Like ASP, idiot with sql 1

[weblead]

Hi,

I hope onprint isn't going to give me a link to a SQL forum for this..... and I did check the faqs, honest.

I have a database of details about training courses. I wish to return results to a recordset from my sql database filtered by year, division, region, and language. However the training courses can be in two languages.

I have five DB fields StartYear, Division, Region, Language1, and Language2. I want visitors to use 4 dropdown lists to make their selection with one being Language which will pick out the matching entries in any courses that have the selected language in either language field.

This is my sql:

"SELECT * FROM tbl_TrainingEvents WHERE StartYear = '"& varYear &"' AND Division = '"& varDiv &"' AND Region = '"& varRegion &"' AND Language1 = '"& varLang &"' OR Language2 = '"& varLang &"' ORDER BY StartMonth,StartDay"

This almost worked !!

Ok I now realise that using the OR means the results show every result that has the Language2 'varLang' in it regardless of year, division, and region.

Have I started out on the wrong track, and need strategic help, or can this be fixed so it works?

If its strategic advice then there are six possible languages, three possible divisions, and five possible regions (in case this helps).

As always thanks in advance for any help you can give me to help me run before I can walk....

 

[Chopstik]

If you're going to use the OR statement in conjunction with a lot of AND's, then you probably want to narrow it down somewhat with some parentheses. Try this and see if it helps:

"SELECT *  FROM tbl_TrainingEvents  WHERE StartYear = '"& varYear &"' AND Division = '"& varDiv &"' AND Region = '"& varRegion &"' AND [COLOR=red]([/color]Language1 = '"& varLang &"' OR Language2 = '"& varLang &"'[COLOR=red])[/color]  ORDER BY StartMonth,StartDay"

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[weblead]

FABULOUS. It works, and no starting from scratch!

A star for simplicity.

Many many thanks.

 

[Chopstik]

Hey, you know the motto... KISS (Keep It Simple, Stupid)... I live by it.

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou