Has anyone got some examples of how to use stlink criteria when using it with DoCmd.OpenReport

I used to have a good cribsheet of the syntax for numeric, string, date data etc. but it's vanished
I have found some examples when you are calling the report using the contents of a text box on a form
but this is straightforward using the underlying query to filter, primarily in this case [Field1] = 2
AND [Field2] = 1

I would generally be able to find this on the web, however, my Broadband went down last week, and
altho I live about 200 yards from the exchange, and the Broadband engineer came out last Thursday to
confirm what I had already told multiple citizens of Bombay, viz... "my line is shagged" BT are
seemingly unable to get an engineer to reset the widget/gadget/IF box in the exchange until Friday
this week.

And to all those contemplating buying a 3G mobile broadband dongle to cover the "BT are crap" waiting
interval... been there done that - and its useless, buy booze instead, you will get much more joy.

J


--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

divventknaa wrote:


The general rule is for **values**, either typed directly or
concatenated from a variable/control need to be enclosed in:
number nothing
text either single or double quoted (' or ")
date/time # signs (and be either USA style (m/d/yyyy)
or ISO style (yyyy-m-d)

E.g.
.... = "[number field]=" & numbervariable

.... = "[text field]=""" & stringvariable & """ "
or
.... = "[text field]='" & stringvariable & "' "
or
.... = "[text field]=""" & Replace(stringvariable, """""",
"""") & """ "
or
.... = "[text field]='" & Replace(stringvariable, "'", "''")
& "' "

.... = "[date field]=" & Format(datevariable,"\#yyyy-m-d\#")

the reason dates are so complicated is because Access uses
your user's Windows regional settings to convert a date
value to a string and the result of that conversion may not
be the date that was intended.

--
Marsh

Marshall, thsnks for info.

On the dates thing I saw one solution by putting a CHR$ statement either side which stops the date
translation anomaly.. Not had cause to use it though.

I will save your dit as a Word file and make several copies this time. As I only dip into access now
and again, if I haven't got a handy routine saved the grey hair and age takes over and I forget
things !

J


--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

I did something somewhat similar a few years ago, using Access97.

I had an (unbound) form that I used to collect the criteria, then built an
SQL statement in VBA ... which I used as a recordsource for a datasheet
subform.

This strategy allowed me to "preview" the information that was going to be
on my report ... which used the exact same SQL string as it's FilterName.
(It's kind of an "undocumented feature". <grin>) Syntax is included in the
code below.

The data collection form had the following Controls:

lstFieldNames -- a listbox control containing the field names from the
"tblContacts" table
Row Source Type: Field List
Row Source: tblContacts
===
optCriteriaType -- an Option Group filled with 6 of the possible comparisons
(ie "Equal To", "Less Than", Greater Than", etc.) You'll be able to figure
the list and order of these out if you refer to the "Select Case
Me.optCriteriaType" portion of the code below.
===
txtCompareValue -- an unbound textbox in which you would type the value that
you wanted to return a matching table record. The matching record(s), if
found, get displayed in the subform. (The "like" and "contains" are pretty
neat.)

===

sbfContacts -- the subform that displays the appropriate records from
tblContacts depending on the specified criteria.

===
cmdOpenReport -- A command button that opens a report and displays the same
matching records as the subform.
(WYSIWYG -- "What You See Is What You Get")

=========== Begin Code sample =======

Option Compare Database
Option Explicit
Dim MySQL As String
Dim whr As String

Public Sub GetSQL()

'--------------- Step One ------------------
'-------- Build the SELECT portion ---------
MySQL = ""
MySQL = MySQL & "SELECT tblContacts.* "
MySQL = MySQL & "FROM tblContacts"

'--------------- Step Two ------------------
'- Determine data type of the compare value -
'-------- and format it accordingly. --------
Dim CV
CV = (Me.txtCompareValue)

If IsNumeric(CV) Then
CV = CLng(CV)
ElseIf IsDate(CV) Then
CV = "#" & CDate(CV) & "#"
Else
CV = Chr(34) & CV & Chr(34)
End If
'--------------- Step Three ------------------
'-- Insert the selected comparison operator ---
'-- and ready the string for assembly into ----
'------------- the WHERE statement ------------
whr = ""
Select Case Me.optCriteriaType
Case 1 'Equal To
whr = whr & " = "
whr = whr & CV
Case 2 'Greater Than
whr = whr & " > "
whr = whr & CV
Case 3 'Less Than
whr = whr & " < "
whr = whr & CV
Case 4 'Like _____
'WHERE (((tblContacts.ContactName) Like "D" & '*'));
whr = whr & " Like "
whr = whr & CV
whr = whr & " & '*'"
Case 5 'Contains ____
'WHERE (((tblContacts.ContactName) Like '*' & "on" & '*'));
whr = whr & " Like "
whr = whr & "'*' & "
whr = whr & CV
whr = whr & " & '*'"
Case Else
whr = ""
End Select
'--------------- Step Four ------------------
'--- IF the user has entered any Criteria ----
'--- construct a WHERE clause and insert -----
'-------- it into the SQL statement ----------
If Len(whr) > 0 Then
MySQL = MySQL & " WHERE (((tblContacts."
MySQL = MySQL & Me.lstFieldNames & ")"
MySQL = MySQL & whr & " ))"
End If
'--------------- Step Five ------------------
'----- "Close out" the SQL statement ---------
'-- There may be those that argue that this --
'-- step is unnessecary, but it's just good --
'------- programming practice, IMHO ----------
MySQL = MySQL & " ;"
'Debug.Print MySQL

'--------------- Step Six ---------------------
'- Use this SQL statement as the RecordSource -
'- for the subform (and a report if you like) -

Me.sbfContacts.Form.RecordSource = MySQL


End Sub

Private Sub lstFieldNames_AfterUpdate()

Me.optCriteriaType = 6
Me.txtCompareValue.Visible = False
GetSQL

End Sub

Private Sub optCriteriaType_AfterUpdate()

If Me.optCriteriaType <> 6 Then
Me.txtCompareValue.Visible = True
Me.txtCompareValue = ""
Me.txtCompareValue.SetFocus
Else
Me.txtCompareValue.Visible = False
GetSQL
End If

End Sub

Private Sub txtCompareValue_AfterUpdate()
GetSQL
End Sub

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String

stDocName = "rptContacts"
'Here is the "undocumented feature". You can use any SQL string as the
"filtername".
'Syntax: DoCmd.OpenReport reportname[, view][, filtername][,
wherecondition]
DoCmd.OpenReport stDocName, acPreview, MySQL

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

===== End of Code Sample ==============

HTH,
Don



"divventknaa" <imat (AT) hotmail (DOT) com> wrote

divventknaa wrote:
If it really was a date and not a string, the Chr$ was the
same as using "#" so it would suffer the same Windows
settings conversion issues. Either will work dine on a
system set to use USA or ISO style dates, but, since you can
not control what users might do on their systems, you should
use the Format function to guarantee it will work on all
systems regardless of the Windows settings.

--
Marsh