Select addresses using two criteria, one 'Like" 2

[TrekBiker]

This is a simplified version of process for selecting customers by Type and Postcode.

It starts with qryCustomersWithAddresses, which contains all customer details.

The user chooses a Customer Type in the combo, whose AfterUpdate runs query A-Customers to select customers of a selected Type.

If Private is the chosen Type there are too many results for printing labels so, just for CustomerType = "Private", I want to extract only Customers with a PostalCode starting with a chosen pair of letter, say CM.

In trying to do this my starting point was a new query based on A-Customer but can't find how to build a criterion on PostalCode that would include IIF([CustomerType] = "Private" then Like([Enter Postalcode] & "*" and something to close the IIF

Any help appreciated

 

[Andrzejek]

Are you trying to write the (VBA) code? Or Select statement?

Your query runs from AfterUpdate in cboType, right?
"PostalCode starting with a chosen pair of letter, say CM" - where those letter are chosen? Another combo?

[tt]Select ...
From ...
Where ...
And [Type] = "Private" And PostalCode Like "CM*"
[/tt]
---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

I would add a combo box to select the Postal Code (I never use a parameter prompt). The after update of the cboType could change the value of the postal code combo box to "*". Then use Like in your criteria.



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks to both of you. I've made a hybrid of your suggestions, now using the separate form to allow the user to select both a Postalcode and a new field that limits Dates to limit results to date minus any chosen number of years. The Type criterion can be handled in a query that's the basis for the one used in the selection form for the other two variables.