Query with Yes/No parameter prompt

[Guest]

I have a field with a Yes/No data type in a table. When I run a query with the Criteria for this field hardcoded to either "Yes" or "No" everything works fine. However I really want the query to prompt the user to input the Yes/No value. I have tried to do this two different ways

a) putting the user prompt in square brackets in the Criteria. This works fine as long as the user enters either -1 or 0 when prompted. This is not very intuitive, I would prefer if the user could enter yes or no instead

b) using Query / Parameters... to enter the prompt and the data type (Yes/No). I can't get this to work at all as every record seems to be returned regardless of whether -1 or 0 is entered when prompted

Does anyone know how to set up a prompt for the user so that he can enter either "yes" or "no" ?

 

[Guest]

I wasn't keeping the prompt in both places but I have just tried doing that now. It still isn't working though - only difference is I get prompted twice...

----- John Spencer (MVP) wrote: -----

If you are using alternative B, are you also keeping the prompt in the criteria?
It needs to be in both places.

 

[John Spencer (MVP)]

OK, can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give us a hint in where we are mis-advising you or where the problem
in communication lies.

 

[Guest]

Hi,

Here is the SQL for my parameter query that is prompting the user for a yes/no answer.

SELECT tblAnimalKingdom.Name, tblAnimalKingdom.Hibernates
FROM tblAnimalKingdom
WHERE (((tblAnimalKingdom.Hibernates)=[Show Animals that hibernate? (-1)yes /(0) no: ]))
ORDER BY tblAnimalKingdom.Name;

The Hibernates field has a yes/no data type. This query only works when the users enters -1 or 0. If "yes" is entered then the following error is reported:

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

 

[John Spencer (MVP)]

Try declaring the parameter and entering it in the where clause. The two places
must be identical so if there is an extra space, the computer will think they
are two different parameters.

The modified SQL statement below should work assuming that Hibernates is a
Yes/No (True/False boolean) field. As a matter of fact you should be able to
use -1,0,Yes,No,True, or False in this code.

Parameters [Show Animals that hibernate? Yes/No:] Bit;
SELECT tblAnimalKingdom.Name, tblAnimalKingdom.Hibernates
FROM tblAnimalKingdom
WHERE (((tblAnimalKingdom.Hibernates)=[Show Animals that hibernate? Yes/No:]))
ORDER BY tblAnimalKingdom.Name;

 

[Guest]

Thank you very much - that works perfectly! When I had tried using the Query/Parameters... dialog box before I accidentially left out a trailing space and that was why I got prompted twice...as you say, the prompt must be absolutely identical in both places.