using "OR" in a ms access 2007 query prompt

Posted on 2012-03-14
Medium Priority
Last Modified: 2012-03-14
I have a query that I need the prompt on a field called "charge_code" to allow multiple entries. Without a prompt the field criteria might look like "LA1234 or LA9876". My plight is sometimes there may be 2 entries, sometimes 3 or more and sometimes it just might be one entry.
Question by:howcheat
LVL 77

Assisted Solution

peter57r earned 1000 total points
ID: 37721034
For your criteria use....

WHERE instr( [Enter list with commas],Chargecode)>0

This assumes that one field value is not wholly contained in another value.
(won't deal properly with Smith, smithson)
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1000 total points
ID: 37721064
to deal with not finding the correct info, add "," in both sides of the parameters

WHERE instr("," &  [Enter list with commas] & ",", "," & [Chargecode] & ",")>0
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37721089
Why not make this easy on your users (and yourself as the developer)  and use a multiselect listbox?



Author Closing Comment

ID: 37721290
listbox doesn't seem feasible for 1.4 million records.

peter57r and capricorn1: that's what I'm looking for!

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question