Whats wrong with this where clause?

[Culland]

I have a SQL query setup with the following in the where clause:

(((people.mailout)=CBOOL([Wants Mailout (True or False) or Leave Blank for All])) Or ([Wants Mailout (True or False) or Leave Blank for All] Is Null))

It works if you enter True or False in the parameter, but if you leave it blank it returns an error message about the query being in error or too complex.

I have been using:

(((people.mailout) Like [Wants Mailout (True or False) or Leave Blank for All]) Or ([Wants Mailout (True or False) or Leave Blank for All] Is Null))

for basic text type fields which works fine except when I get to a field thats true/false, which is why I changed Like to = and then wrapped it in Cbool to convert it to a boolean. I am guessing that the problem is that the Null value is being converted to 'false' by the cbool? Not sure what else could be wrong there.

Thanks for any insight.

Cul

 

[Jon K]

You can declare the data type of the parameter instead of using CBool(). See query in database.
.

 

[Culland]

Thanks Jon, that did the trick. I cant seem to find any docs on the syntax for that line. How would I set more then one paramter up at the top?

Different PARAMETERS clause for each one, or comma seperated list?

Thanks again.

Cul

 

[Jon K]

It's a comma separated list. See the example in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=72727

If you don't know its syntax, you can do it in query Design View. Just choose menu Query, Parameters... And in the dialog box that pops up, type each parameter in the first column and select the corresponding data type in the second column.
.

 

[Culland]

Great, thanks again!!

Cul