multiple criteria search - please help

[M0E-lnx]

I need to create a report based on a query using multiple criteria. Here is my example

I have a table with several records of purchase orders.
[PO Number] | [DATE] | [Vendor] | [Job]

I can run a searh using the job or vendor fields using the following line

DoCmd.OpenReport stDocName, acViewPreview, , "PoJob = " & Me.cbSrchJob, acWindowNormal

But what if I wanted to narrow down my search even more and tell it to show me all the hits where POJob = "ThisJob" and PoVendor = "This vendor"

Anyone know how to do that? Please help

 

[pbaldy]

"PoJob = " & Me.cbSrchJob & " AND PoVendor = " & Me.VendorControl

 

[M0E-lnx]

Is there a limit as to how many AND instances you can have in the statement?
And what about wild cards?
For example, I want to be able to clear out the VendorControl and have it bypass that option

 

[pbaldy]

From Help:

The maximum length of the wherecondition argument is 32,768 characters

other than that, I'm not aware of a limit on the number of AND or OR instances (which is not to say there isn't one). I think wildcards will work there, though offhand I can't remember if I've ever tried it.

If the fields are optional, I'd build a string variable and then use that in the OpenReport. IOW, you only add a field to the variable if the control is populated.

 

[M0E-lnx]

I'm getting a "Type Mismatch" error when I try to use 2 conditions on the where statement.

Can you give me an example?
here is the code I'm using now

DoCmd.OpenReport stDocName, acViewPreview, , "PoJob = " & Me.cbSrchJob And "PoVendor = " & Me.cbVendrName, acWindowNormal

 

[M0E-lnx]

I'm starting to think this is not the right way of doing this...

Can anyone drop in and help?

 

[georgedwilkinson]

You need to be wary of the data types in the "where" clause.

It LOOKS like your vendor is text, which means you need to "delimit" the condition for it:

"PoJob = " & Me.cbSrchJob And "PoVendor = '" & Me.cbVendrName &"'",

 

[M0E-lnx]

Thanks for the reply my fellow Houstonian.
Unfortunately, that did not work
Actually, these are numerical values (the comboboxes are bound to the ID (keys) but only display the text, so does the report.
Both combos are setup exactly the same way

what gets me is that it works fine when only one condition is thrown at it.
I purposfully left the operator off the line and got an error that says invalid syntax (which I knew), but I can see that it's parsing the correct values to the query.

 

[pbaldy]

Here's what I posted (adjusted for the name), and then what you used:

"PoJob = " & Me.cbSrchJob & " AND PoVendor = " & Me.cbVendrName
"PoJob = " & Me.cbSrchJob And "PoVendor = " & Me.cbVendrName

See the difference?

 

[M0E-lnx]

Definitely!.... I can't believe it flew by me... It works now
Thank you very much.

Now... while we're on the subject.., how do we make these only take effect if populated?