×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Select addresses using two criteria, one 'Like"
2

Select addresses using two criteria, one 'Like"

Select addresses using two criteria, one 'Like"

(OP)
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

RE: Select addresses using two criteria, one 'Like"

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?

Select ...
From ...
Where ...
And [Type] = "Private" And PostalCode Like "CM*"

---- Andy

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

RE: Select addresses using two criteria, one 'Like"

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

RE: Select addresses using two criteria, one 'Like"

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close