SQL WHERE Multiple Columns

[bumble]

Hi People

This database stuff is new to me and it's driving me nuts! I hope someone can help me with this problem? I'm posting to this forum because looking at the posts, it looks like there are some knowledgeable people around here.

Hello by the way - I'm stressed and forgetting my manners!

I have a table called tbl_dir_details. Within that table I have 3 fields (well actually many more but they don't come into this equation). Those 3 fields are called Category, Category2 and Category3. I want to retrieve all records where ? is in any of the fields (? being a value passed via a URL parameter).

I have tried the following without luck:

SELECT *
FROM tbl_dir_details
WHERE Category = ? OR Category2 = ? OR Category3 = ?

I get the following error:
No value given for one or more required parameters

Now this works if I lookup one field at a time. It's like the value of ? is not being passed from one query to the next in the WHERE statement. Your going to cringe when I tell you that I have currently created 3 record sets to fudge this at the moment.

I hope i'm explaining that correctly?? So, does anyone here know what I’m going on about and secondly, know the correct syntax that I need to use?

Many thanks

Lee

[Stewart Ross]

Hi Lee, and welcome to Bytes!

The error message is a bit confusing. The syntax you used with the question marks is the same as that used with Activex Data Objects (ADO) recordsets to refer to variable parameters passed to the recordset - so it expects to find a parameter declared somewhere to match the wildcard character in the SQL. It is not the same parameter when it is repeated, it is different parameters from a list matched by position.

If, on the other hand, your comparison fields are strings set to the value '?' you need to enclose the value in single quotes, like this:

category = '?' OR category2 = '?' ...

If you do have parameters passed via ADO, rather than string values, the problem is as mentioned that it is expecting a different parameter on each occasion, with positional matching to each parameter defined.

I would suggest in this case that you use VB variables in place of the parameter and refer to the variable's value instead.

This is like this:

Dim somevar as string
...
somevar = yourparameter
...
where category = somevar or category2 = somevar...

You don't give enough information about how you are retrieving your parameter passed to the routine for me to advise further on how you set the variable at this stage.

-Stewart