multiple criteria in select query with 1 like

[boddy]

Hi,

I'm trying to build a select query where it prompts the user for a few parameters. I've been having issues where people would misspell the vendor name and nothing pops up. I changed the vendor to Like [Vendor Name] & "*" but now I can't use the parameter of PO# without everything popping up. I have attached the SQL view. Thank you for any suggestions.

SELECT [Main Payment].[Batch#], [Main Payment].VendorName, [Main Payment].VoucherPrefix, [FY08 PAYMENT detail].VoucherNumber, [Main Payment].VoucherSuffix, [FY08 PAYMENT detail].Vchline1, [FY08 PAYMENT detail].PONo, [FY08 PAYMENT detail].InvoiceDate, [FY08 PAYMENT detail].InvoiceID, [FY08 PAYMENT detail].Amount
FROM [Main Payment] INNER JOIN [FY08 PAYMENT detail] ON [Main Payment].VoucherNumber = [FY08 PAYMENT detail].VoucherNumber
WHERE ((([FY08 PAYMENT detail].PONo)=[Enter PO#])) OR ((([Main Payment].[Batch#])=[Enter Batch #])) OR ((([FY08 PAYMENT detail].VoucherNumber)=[Enter Voucher #])) OR ((([Main Payment].VendorName) Like [Enter Vendor Name] & "*"));

 

[EMP]

Take a look at this thread.
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[boddy]

I see
(2) We may also occasionally see a solution like this:-
-------------------------------------
Field: FieldName

Show: check

Criteria: Like "*" & [Forms]![FormName]![ControlName] & "*"
-------------------------------------
Unfortunately, the Like operator cannot return Null values. So when the text box or combo box is left blank, it fails to return all the records if the field happens to contain Null values. "

As the closest one to my situation, but that doesn't explain a way to be able to leave the vendor name blank and just use the PO# criteria and just bring up the PO# I would like. I figured that since it is an OR statement, I might need another way around.

 

[EMP]

Are you looking for ways to incorporate the ability of leaving some of the parameters blank in a query?

If you are, then Jon K's method is a way to go. His query in the sample database contains examples of using the = operator for exact match and the Like operator for partial match of character strings, in both cases having the ability of leaving the parameter blank.


His Note #2 just brings our attention to the pitfall in a solution that we may sometimes find of using the Like operator in another way.

^