conditional SQL statement?

Question

I'm making a database to keep a track of my dvds in access.  I've got a working SQL query which looks up film names and returns the details of any dvds it finds, the query is linked to a form which displays the returned data, the code looks like this:

Select * from films
where flims.filmname like [forms]![searchform]![filmtitle];

The problem I'm having is that I also want to be optionally able to search by genre as well.  As I want to make genre searching optional I cant just use an and clause, because the genre details box might be left empty in which case the query returns no rows.  Is there any way to include an If statement in an SQL query, or another way to only include fields into which the user has entered data in the query?

TheMadProfessor · Accepted Answer

You can conditionally execute one of 3 different queries, depending on user selection from a drop-down list, as one suggestion:

0 selections: execute query with no criteria by genre

1 selection: execute query with genre = <selected value>

2+ selections: execute query with genre IN <comma separated list of selections>

vorpal22 · Answer

Could you build your SQL query as a string and then execute it like that? Check if the details box is empty, and if it isn't, add an AND clause to the query. If it isn't empty, simply omit it.

It would be helpful if we had more details: what are you using for your programming? What is your form made with?

Rasputin · Answer

Start the where clause with WHERE 1 = 1
then you can follow that with AND clauses if needed for what the user enters on the form

Anonymous · Answer

hi, Please see the "source" link for the syntax highlighted version. i assume your table has a different index field, i visit assume it is called `UniqueID` First, create a sparkling column for this: regulate table `orders` upload COLUMN `new_id`; Now, right that's the technique: DELIMITER // CREATE technique setIterate() start up declare accomplished INT DEFAULT 0; declare var_OrderID, var_OldOrderID CHAR(128); declare var_Iteration, var_UniqueID INT DEFAULT a million; declare cursah CURSOR FOR go with `OrderID`, `UniqueID` FROM `orders` ORDER via `OrderID`, `UniqueID`; declare proceed HANDLER FOR not discovered SET accomplished = a million; OPEN cursah; REPEAT FETCH cursah INTO var_OrderID, var_UniqueID; IF not accomplished THEN IF var_OrderID = var_OldOrderID THEN SET var_Iteration = var_Iteration + a million; ELSE SET var_OldOrderID = var_OrderID; SET var_Iteration = a million; end IF; replace `orders` SET `new_id` = var_Iteration the place `UniqueID` = var_UniqueID; end IF; till accomplished end REPEAT; close cursah; end// call setIterate; wish this enables!

Trending News

conditional SQL statement?

4 Answers