Passing "ALL" or "*" from Combo Box to Query?

[CCRT]

I'm trying to add "*" to a combo box and then pass it to a query. to select all records. I've used faq702-4538 as best I can but cannot get it to query without error.

Here is the SQL for the Combo Box (This works as it adds the "*" to the combo box)

SELECT qryComponentNumber.ComponentNumber FROM qryComponentNumber UNION SELECT "*" FROM qryComponentNumber
ORDER BY qryComponentNumber.ComponentNumber;


Here is the SQL for the query

"SELECT tblMFGItems.ComponentNumber, tblMFGItems.ManufactureName, tblMFGItems.ManufactureNumber, tblMFGItems.Status, tblMFGItems.RoHS, tblMFGItems.Preferred
FROM tblMFGItems
WHERE (((tblMFGItems.ComponentNumber)=[Forms]![frmCboLookup]![Combo0])) OR ((([Forms]![frmCboLookup]![Combo0])="*"));"

The query throws and error " Data type mismatch in the criteria expression"

Thanks for any help

--Dave

http://home.comcast.net/~ccrt-racing/ccrt.htm

 

[pmkieffe]

Did you check your Data Types of the fields you are using in the table design view?

-pmkieffe

 

[PHV]

Seems that tblMFGItems.ComponentNumber is defined as numeric.
Simply replace "*" with any numeric value not valid for ComponentNumber (eg 0 or -1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CCRT]

Thanks! That worked great. I changed the format from numeric to text. Is there a method to use the word "All" instead of the "*"? I tried to substitute it in the code but it did not work.

http://home.comcast.net/~ccrt-racing/ccrt.htm

 

[PHV]

SQL for the combo:
SELECT ComponentNumber FROM qryComponentNumber
UNION SELECT 'All' FROM qryComponentNumber
ORDER BY 1

SQL for the Query:
SELECT ComponentNumber, ManufactureName, ManufactureNumber, Status, RoHS, Preferred
FROM tblMFGItems
WHERE [Forms]![frmCboLookup]![Combo0] In (ComponentNumber, 'All')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886