Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
SQL conditional AND clause?
I'm trying to make an access database to keep a track of my DVDs (I've got quite a few!) I designed an SQL query to find films by name which worked fine, but i want to expand it now so I can also search by genre. I've come up with the following:
SELECT Films.[Disc#], Films.FilmName, Films.Genre, Films.MediaType
FROM Films
WHERE Films.FilmName Like "*" & Forms![Main Display Form]!name & "*" And Films.Genre like Forms![Main Display Form]!genre
ORDER BY Films.FilmName;
This works ok, but if you omit either a name or a genre on [main display form] it returns no rows. What I want is to use an IF statement to check and see if ![main display form]!name or ![main display form]!genre is null, and if so to ignore that section of the WHERE statement (if I've not entered a name then to search by genre only, and I've not entered a genre to search by name only)
I tried substituting the AND statement with an OR statement but that didn't produce the results I wanted!
Any help getting this working would be much appreciated!
3 Answers
- expertazizLv 51 decade agoFavorite Answer
you have not written where you are displaying the query result. I assume you are using listbox to display the result on the same form then try with following:
if not isnull(Me.name) and not isnull(Me.genre) then
Me.List1.RowSource ="SELECT Films.[Disc#], Films.FilmName, Films.Genre, Films.MediaType
FROM Films
WHERE Films.FilmName Like "*" & Forms![Main Display Form]!name & "*" And Films.Genre
like Forms![Main Display Form]!genre
ORDER BY Films.FilmName;"
Elseif Isnull(Me.genre) Then
Me.List1.RowSource = "SELECT Films.[Disc#], Films.FilmName, Films.Genre, Films.MediaType
FROM Films
WHERE Films.FilmName Like "*" & Forms![Main Display Form]!name & "*"
ORDER BY Films.FilmName;"
Elseif Isnull(Me.name) Then
Me.List1.RowSource = "SELECT Films.[Disc#], Films.FilmName, Films.Genre, Films.MediaType
FROM Films
WHERE Films.Genre like Forms![Main Display Form]!genre Order By Films.Genre;"
endif
- Serge MLv 61 decade ago
Try this replacement:
Forms![Main Display Form]!genre ==>
nz(Forms![Main Display Form]!genre,'*')
- NigelLv 51 decade ago
You could look at using 'like' in the query, sorry not sure how it fits into your query exactly, but if you have a look at http://forums.devarticles.com/microsoft-access-dev... it may give you some ideas.
