"Where" condition in a DoCmd.OpenReport command

[thatlem]

I need to be able to have two field filters defined in the where condition of a DoCmd.OpenReport command. Both of the filters work fine individually, but when I try to put them together I am constantly getting errors. I have not found any references that indicate how to include more than one filter. I have tried using the following: And ; | : & as well as enclosing the entire statement in () or [].

I'm stumped. Any suggestions or references?

 

[gemma-the-husky]

its AND, but you probably arent formatting it properly

can you show us the code you have?


----------
the finished "where" needs to look like this, the spaces are significant, and the quotes and hashes are also significant for the appropriate data type

[myfield] = "somefield" AND [mynumber] = 12 AND [mydate] = #13/4/09#

 

[thatlem]

Below is the entire statement:

DoCmd.OpenReport stDocName, [acViewReport], , "[Audit Month] ='" & stDocNameMonth & "'" And "[Audit Year] ='" & stDocNameYear & "'"

The last section starting with Audit Month is the where statement. Basically I want to insert a month and year filter based on the predefined equation to determine stDocNameMonth and stDocNameYear. Both are numerical entries, month being 1-12 and the year as 2008, 2007, etc.

 

[pbaldy]

You've got some stray quotes, plus if the values are numeric you don't want the single quotes. Try

DoCmd.OpenReport stDocName, [acViewReport], , "[Audit Month] =" & stDocNameMonth & " And [Audit Year] =" & stDocNameYear

 

[thatlem]

Thanks for the reply. Actually, I needed the quotes exactly where they were. My issue turned out to be not including the And within the second set of quotes.

Thanks

 

[pbaldy]

A matter of semantics then. I referred to the quotes in red as stray, since they needed to be removed:


DoCmd.OpenReport stDocName, [acViewReport], , "[Audit Month] ='" & stDocNameMonth & "'" And "[Audit Year] ='" & stDocNameYear & "'"