Filtering a report's underlying table from a form

[John S. Ford, MD]

I have a form with several command buttons each of which will open the same
report but with a different filter. What kind of code can I put in each
button's OnClick event that will pass the appropriate WHERE string to the
report's Filter property?

I'm using Access 97.

John

 

[Douglas J. Steele]

Dim strCriteria As String

strCriteria = "[Field1] = 5 AND [Field2] = 'abc'"

DoCmd.OpenReport "NameOfReport", acViewPreview, , strCriteria

The criteria is a Where clause (without the word WHERE at the front of it).
You can build the criteria using variables, or by referring to controls on
the form too, if you like. In the example about, I'm assuming Field1 is
numeric and Field2 is text. To set strCriteria using controls on the form,
you'd use:

strCriteria = "[Field1] = " & Me.Control1 & _
" AND [Field2] ='" & Me.Control2 & "'"

Note that I've still included the single quotes around the second value.
Note, too, that that won't work if there's a chance that the value contains
an apostrophe. If it does, replace Me.Control2 with Replace(Me.Control2,
"'", "''"). In case it's not evident, the two parameters there are " ' " and
" ' ' ".

 

[John S. Ford, MD]

Doug,

I doubt you're still following this thread, but if you are, thanks! That
worked perfectly.

John

Dim strCriteria As String

strCriteria = "[Field1] = 5 AND [Field2] = 'abc'"

DoCmd.OpenReport "NameOfReport", acViewPreview, , strCriteria

The criteria is a Where clause (without the word WHERE at the front of
it). You can build the criteria using variables, or by referring to
controls on the form too, if you like. In the example about, I'm assuming
Field1 is numeric and Field2 is text. To set strCriteria using controls on
the form, you'd use:

strCriteria = "[Field1] = " & Me.Control1 & _
" AND [Field2] ='" & Me.Control2 & "'"

Note that I've still included the single quotes around the second value.
Note, too, that that won't work if there's a chance that the value
contains an apostrophe. If it does, replace Me.Control2 with
Replace(Me.Control2, "'", "''"). In case it's not evident, the two
parameters there are " ' " and " ' ' ".

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form with several command buttons each of which will open the
same report but with a different filter. What kind of code can I put in
each button's OnClick event that will pass the appropriate WHERE string to
the report's Filter property?

I'm using Access 97.

John