SQL Select Statements : how??

[zozzie]

Hi,

I'm designing a search engine for my database and I'm having some troubles with the SQL-select statement.

The database contains a table tblCD with the following fields:
MainGroupID (pk)
DescriptionCD
ProgramGroup

The SQL-statement is built up by two parameters: a listbox and a hitword (keyword).

The keyword is used to search in two columns of the databasetable tblCD: tblCD.DescriptionCD and tblCD.ProgramGroup

The purpose of the search function is to select all records that have the corresponding selected value of the listbox and where the keyword occurs in one of the two columns in the database.

My SQL statement is the following:

SQLSearch = "SELECT * FROM tblCD "
SQLSearch = SQLSearch & "where tblCD.MainGroupID=" & Me.ListBox1.SelectedValue
SQLSearch = SQLSearch & " AND tblCD.DescriptionCD like '%" & HitWord & "%'"
SQLSearch = SQLSearch & " OR tblCD.ProgramGroup like '%" & HitWord & "%'"

Everything works fine, except when the user selects a value in the listbox and gives in a keyword then the result isn't correct because it seems that there is only looked to the value in tblCD.ProgramGroup and it doesn't matter what the selected value is in the listbox.

Is this because of the OR??

I'll give an example: suppose tblCD contains the following records:
MainGroupID (pk):1
DescriptionCD: Climbing Everest
ProgramGroup : Climbing

MainGroupID (pk):2
DescriptionCD: Climbing Eiger
ProgramGroup : Climbing

So when the user selects 1 in the listbox and gives in the keyword "climbing" then the result should be:
Climbing Everest, Climbing

Instead of this the result is:
Climbing Everest, climbing
Climbing Eiger, climbing

Can anybody help me please???

Thanks,

Tom

[Cander]

Use the same rule as in Math about parenthesis.

you need to group things in parenthesis that need to evaluate seperatly

2 examples

(situation1 AND situation 2) OR situation3

situation1 AND (situation 2 OR situation3)

[zozzie]

Thanks,

problem resolved thanks to your suggestion.

Tom