CODEWITHSUNDEEP
sqlms-access
gotigers
gotigers

Reputation: 11

Selecting multiple options in MS Access

I am writing a code in SQL for Access. The query asks three questions. I have three categories -- I'll just use the categories 'country', 'city', 'street' for now. I am trying to figure out how to make it so that you only have to enter one answer even though it asks you 3. But if you answer two, it will give you the like terms. For example, if I answered Georgia and Atlanta, Atlanta Georgia would show up. Or if I entered Canal in 'street' and Louisiana, every street named Canal in Louisiana would show up.

Currently, if I typed out Canal and Louisiana, the query would show me everything listed under Louisiana and every street titled Canal (even the ones not in Louisiana).

SELECT * 
FROM File 
WHERE (((File.State)=[Enter the state])) 
    OR (((File.City)=[Enter the city])) 
    OR (((File.Street)=[Enter the street]));

Upvotes: 1

Views: 113

Answers (2)

braX
braX

Reputation: 11735

SELECT * FROM File WHERE
(((File.State)='[Enter the state]'))
    OR (((File.City)='[Enter the city]'))
    OR (((File.Street)='[Enter the street]'));

You just needed some quotes around them because they are strings.

Upvotes: 0

Don't Panic
Don't Panic

Reputation: 41810

I think you should be able to do it by using AND rather than OR to connect the criteria for the different columns, but not using the criteria for a column if its parameter wasn't given.

SELECT * 
FROM File 
WHERE   ( ([Enter the state] = '') OR (File.State=[Enter the state]) ) 
    AND ( ([Enter the city] = '') OR (File.City=[Enter the city]) )
    AND ( ([Enter the street] = '') OR (File.Street=[Enter the street]) );

I'm kind of rusty with Access, so I'm not sure if the parameter will be null or '' if nothing is entered, so it might need to be adjusted a little for that.

Upvotes: 1

Related Questions