Multiple report criteria on command button

[Sammie]

I want to filter a report based on two criteria specified on a form. If I
use either of the strFilter lines below, they return the correct results.
How do I join them to filter by both year and vendorID on the report? The
command button that opens the report contains the following code:

Dim strFilter As String

strFilter = "[vendorID] = " & Chr(34) & Me!VendorID & Chr(34)
'strFilter = "Year([orderdate])=" & Me!cboYear

DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

 

[niuginikiwi]

Try

strFilter = "[vendorID] = " & Chr(34) & Me!VendorID & Chr(34)
strFilter = strFilter & "Year([orderdate])=" & Me!cboYear

 

[fredg]

I want to filter a report based on two criteria specified on a form. If I
use either of the strFilter lines below, they return the correct results.
How do I join them to filter by both year and vendorID on the report? The
command button that opens the report contains the following code:

Dim strFilter As String

strFilter = "[vendorID] = " & Chr(34) & Me!VendorID & Chr(34)
'strFilter = "Year([orderdate])=" & Me!cboYear

DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

From your above syntax it seems [VendorID] is a Text datatype.
If so you can use:

strFilter = "[vendorID] = """ & Me!VendorID & """ AND
Year([OrderDate]) = " & Me.cboYear
DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

 

[Sammie]

Yes, VendorID is a text field. When I tried your suggestion, I get an error
.. . . "database can't find the field '|' referred to in your expression."
Do you know what's wrong?
--
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7

I want to filter a report based on two criteria specified on a form. If I
use either of the strFilter lines below, they return the correct results.
How do I join them to filter by both year and vendorID on the report? The
command button that opens the report contains the following code:

Dim strFilter As String

strFilter = "[vendorID] = " & Chr(34) & Me!VendorID & Chr(34)
'strFilter = "Year([orderdate])=" & Me!cboYear

DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

From your above syntax it seems [VendorID] is a Text datatype.
If so you can use:

strFilter = "[vendorID] = """ & Me!VendorID & """ AND
Year([OrderDate]) = " & Me.cboYear
DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

 

[Sammie]

I tried your suggestion. I get the following error:
"Syntax error (missing operator) in query expression '([vendorID] =
"VendorOK"Year([orderdate])=2009)'."

Any ideas?
--
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7

Try

strFilter = "[vendorID] = " & Chr(34) & Me!VendorID & Chr(34)
strFilter = strFilter & "Year([orderdate])=" & Me!cboYear

I want to filter a report based on two criteria specified on a form. If I
use either of the strFilter lines below, they return the correct results.
How do I join them to filter by both year and vendorID on the report? The
command button that opens the report contains the following code:

Dim strFilter As String

strFilter = "[vendorID] = " & Chr(34) & Me!VendorID & Chr(34)
'strFilter = "Year([orderdate])=" & Me!cboYear

DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

 

[fredg]

Yes, VendorID is a text field. When I tried your suggestion, I get an error
. . . "database can't find the field '|' referred to in your expression."
Do you know what's wrong?

It's always best to copy and paste into a reply message the code that
errors out so that we can see if you have left out a comma or
incorrectly written the expression.
I have no idea what your current actual code actually looks like.

Check that you have correctly spelled each control and field name, and
that you have all the quotes, spaces, and ampersands as I have written
them.
The expression I gave you works correctly for me (substituting my
field and control names).

 

[PieterLinden via AccessMonster.com]

Yes, VendorID is a text field. When I tried your suggestion, I get an error
. . . "database can't find the field '|' referred to in your expression."
Do you know what's wrong?[quoted text clipped - 14 lines]

Year([OrderDate]) = " & Me.cboYear
DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

How about:
' that is : "[vendorID] = doublequote single quote ["
' and then closing is the reverse.
strFilter = "[vendorID] = '"& Me!VendorID & "' AND
Year([OrderDate]) = " & Me.cboYear
DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

 

[Sammie]

Dear Fred,

Sorry, I just copied/pasted your code. Here is what I have that produces
the error:

strFilter = "[vendorID] = """ & Me!VendorID & """ AND "
Year([OrderDate]) = " & Me.cboYear "
DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

When I step into the code, I see the error occurs on the
Year([OrderDate])line.
Error message: . . . "database can't find the field '|' referred to in your
expression."

One thing I noticed is that your Year([OrderDate]) line didn't have a
double-quote (") at the end of it. I pasted without the double-quote and
Access adds the double-quote at the end of that line. Could that be the
problem? If so is there a way to force it not to add the double-quote?

 

[fredg]

Dear Fred,

Sorry, I just copied/pasted your code. Here is what I have that produces
the error:

strFilter = "[vendorID] = """ & Me!VendorID & """ AND "
Year([OrderDate]) = " & Me.cboYear "
DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

When I step into the code, I see the error occurs on the
Year([OrderDate])line.
Error message: . . . "database can't find the field '|' referred to in your
expression."

One thing I noticed is that your Year([OrderDate]) line didn't have a
double-quote (") at the end of it. I pasted without the double-quote and
Access adds the double-quote at the end of that line. Could that be the
problem? If so is there a way to force it not to add the double-quote?

Newsgroup messages are usually formatted to display about 70 or so
characters on one line. You have to be aware of this when copying
from a newsgroup message, as the VBA editor will add an ending line
quote if it detects one missing from the end of a line.

There is the added " after the word AND in your code ...
strFilter = "[vendorID] = """ & Me!VendorID & """ AND "
which is incorrect ...
as is the final quote after the word cboYear.

Copied from my original reply it should be (ALL ON ONE LINE):

strFilter = "[vendorID] = """ & Me!VendorID & """ AND
Year([OrderDate]) = " & Me.cboYear

When you copy the above into your code, remove the line break after
the word AND so that the above code is all on one line (and also make
sure there is a space between the word AND and the word YEAR).
Or ...
Use the line continuation characters (a space and _)

strFilter = "[vendorID] = """ & Me.[vendorID] & """ And " _
& "Year([OrderDate]) = " & Me.cboYear

Now that quote after the word AND is now necessary to end the text.
Note also that there is now a quote in front of the word Year.
There is NO quote after cboYear on the second line as cboYear is a
Number value (not text).

Another method to split long lines is to break the syntax into more
manageable portions.
The below is on 2 lines:

strFilter = "[vendorID] = """ & Me.[vendorID] & """"
strFilter = strFilter & " AND Year([OrderDate]) = " & Me.cboYear

Note that now there are 4 quotes at the end of the first line.

Try again using any one of the 3 above methods (better yet, try each
one so you get the hang of it).

 

[Sammie]

Dear Fred,

It works!

Thanks for that great lesson on use of quotes, end of line issues and text
versus non-text.

--
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7

Dear Fred,

Sorry, I just copied/pasted your code. Here is what I have that produces
the error:

strFilter = "[vendorID] = """ & Me!VendorID & """ AND "
Year([OrderDate]) = " & Me.cboYear "
DoCmd.OpenReport "PurchasesReport", acPreview, , strFilter

When I step into the code, I see the error occurs on the
Year([OrderDate])line.
Error message: . . . "database can't find the field '|' referred to in your
expression."

One thing I noticed is that your Year([OrderDate]) line didn't have a
double-quote (") at the end of it. I pasted without the double-quote and
Access adds the double-quote at the end of that line. Could that be the
problem? If so is there a way to force it not to add the double-quote?

Newsgroup messages are usually formatted to display about 70 or so
characters on one line. You have to be aware of this when copying
from a newsgroup message, as the VBA editor will add an ending line
quote if it detects one missing from the end of a line.

There is the added " after the word AND in your code ...
strFilter = "[vendorID] = """ & Me!VendorID & """ AND "
which is incorrect ...
as is the final quote after the word cboYear.

Copied from my original reply it should be (ALL ON ONE LINE):

strFilter = "[vendorID] = """ & Me!VendorID & """ AND
Year([OrderDate]) = " & Me.cboYear

When you copy the above into your code, remove the line break after
the word AND so that the above code is all on one line (and also make
sure there is a space between the word AND and the word YEAR).
Or ...
Use the line continuation characters (a space and _)

strFilter = "[vendorID] = """ & Me.[vendorID] & """ And " _
& "Year([OrderDate]) = " & Me.cboYear

Now that quote after the word AND is now necessary to end the text.
Note also that there is now a quote in front of the word Year.
There is NO quote after cboYear on the second line as cboYear is a
Number value (not text).

Another method to split long lines is to break the syntax into more
manageable portions.
The below is on 2 lines:

strFilter = "[vendorID] = """ & Me.[vendorID] & """"
strFilter = strFilter & " AND Year([OrderDate]) = " & Me.cboYear

Note that now there are 4 quotes at the end of the first line.

Try again using any one of the 3 above methods (better yet, try each
one so you get the hang of it).