CODEWITHSUNDEEP
ms-access
rryanp
rryanp

Reputation: 1027

Query not running with "*" criteria

I have a query, and I want the criteria to look to a combo box. I populated the combo box with the following Row Source to add an "All" option:

SELECT id, team FROM tblTeams ORDER BY team UNION SELECT "*", "<ALL>" FROM tblTeams;

The query runs fine if I choose a team (the criteria is against the id, and the bound column of the combo box is 1). But if I choose <ALL>, I get a warning: "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."

The field is an integer, so I was thinking the * was causing problems. I tried changing it to ">0", but that gave me the same error. Any suggestions for making this work so I don't have to use VBA to manipulate the query (I'm fine to do that, but I feel like it's cleaner if you don't--let me know if that's an errant thought)?

Upvotes: 1

Views: 414

Answers (2)

Fionnuala
Fionnuala

Reputation: 91366

It seems to me that you wish to refer to your form as a criteria line in your query.

Try using Like. This works for me:

SELECT Table1.ID, Table1.Team
FROM Table1
WHERE Table1.ID Like [Forms]![Table1]![MyCombo]

This does not need any code to work. If a number is selected, that number will be returned, if All is selected, all non-null records will be returned.

Upvotes: 1

Fink
Fink

Reputation: 3436

If your 'id' field from the tblTeams is numeric, trying to use a string such as '*' or '>0' will generate an error when they are joined together by the UNION.

Depending on your needs I would suggest using a query like the one below, where you use a -1 as your 'id', or some other sort of unique numeric 'id'.

Assuming your 'id' fields are all positive numbers. You can then test for a negative number to determine if all the records should be returned or not.

SELECT id, team FROM tblTeams 
UNION 
SELECT -1 as id, "<ALL>" as team FROM tblTeams 
ORDER BY team

Alternatively, you can cast your 'id' field into a string, so it's a string just like '*' or '>0'. The important thing is that the fields be of the same datatype.

SELECT Cstr(id), team FROM tblTeams
UNION
SELECT "*" as id, "<All>" as team from tblTeams
ORDER BY team

Upvotes: 2

Related Questions