Query Design Problem

[hardleydirt]

I have a table that has several fields that are check box's (data type Yes/No - Format true/False). I am trying to run a Query where you are prompted for input. The Criteria is - Between [Start Date] And [End Date] and =[True or False].

When the Query is run I type a date range for the 1st prompt and then True or False for a 2nd prompt and get this error.



"This expression is typed incorrectly, or it is too complex to be evaluated. For Example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. HUM?????

Anyone have any ideas what is wrong?

[ssanfu]

Between [Start Date] And [End Date] and =[True or False].

What is field is True or False? There has to be a field name associated with the [True or False].

Looking at part of the criteria, you would have

WHERE [HireDate] Between [Start Date] And [End Date]

when you execute the query, you get prompts for the start date and end date. [Start Date] is So the criteria becomes

WHERE [HireDate] Between #1/1/2011# And #12/31/2011#

You need the criteria to be something like

WHERE [HireDate] Between [Start Date] And [End Date] AND Check1 = [True or False]

Because there are no fields/controls named [Start Date], [End Date] or [True or False], you get a prompt to enter the criteria values.


Hope this makes sense....

[hardleydirt]

Yea major confusion - not a very good programer - see attached image - The Date Received field is data type Date/Time, the Stand Alone field has data type Yes/No on the table design page the format for the Stand Alone shows True/False (I changed this to Yes/No) but same results. If I only use the date filed it works fine - except it won't sort correctly on the date which is another weird issue (tried ascending sort, sorted the table nothing).

[ssanfu]

Even though "StandAlone" is defined in the table as "Yes/No" (aka Boolean), it is really a number. Access defines a constant "True" as -1 and False as 0 (Zero).

Run your query and when the prompts appear, enter the dates then enter -1 for the [True or False] prompt.

[hardleydirt]

Steve - YOU are correct - It works GREAT - Well the next step in my query has a hang up - I wanted to use two Yes/No prompts on to separate fields the Stand alone one and another one - but it seems it will only popup for one of the fields - going to have to rethink things I guess.

But thanks so much for your answer.

[ssanfu]

I use a form instead of input prompts. You have more control over what happens.
When you click a button or initiate the query, the code will create the "WHERE clause" or the query will look at the form to get the criteria for the query.