How to use 'AND' when printing a report with multiple parameters?

Does there need to be parenthesis anywhere for the 'AND' to work?

Both SpecialistSearch and SupplierSearch are unbound comboboxes. These do work individually for the report.

When I add 'AND', the report fails.

DoCmd.OpenReport "Order Report", acPreview, , "[Specialist]=" & Chr(34) & Me!SpecialistSearch & Chr(34) And "[Supplier]=" & Chr(34) & Me!SupplierSearch & Chr(34)

I receive an error.

Type Mismatch

But doesn't show me what is wrong.

Solution

The most correct way, imo, is to use parameters.

DoCmd.SetParameter "pSpecialist", Me!SpecialistSearch.Value
DoCmd.SetParameter "pSupplier", Me!SupplierSearch.Value
DoCmd.OpenReport "Order Report", acPreview, , "[Specialist] = pSpecialist And [Supplier]=pSupplier"

Alternately, you could construct a valid SQL statement, which means in quotes AND with a leading space in quotes.

DoCmd.OpenReport "Order Report", acPreview, , "[Specialist]=" & Chr(34) & Me!SpecialistSearch & Chr(34) " And [Supplier]=" & Chr(34) & Me!SupplierSearch & Chr(34)

This second option opens you up to SQL injection, which might seem minor because for Access databases, we usually fully trust the user, but it can also lead to annoying bugs, for example, if your supplier is Jack "The Giant" Chairs, that'd cause the SQL statement to break and the application to malfunction.