quick query syntax questions

[cowboy]

PARAMETERS [Forms]![Main Form]![cmbArea] Text ( 255 );
SELECT InspectionsDI.ID,InspectionsDI.Area
FROM InspectionsDI
WHERE InspectionsDI.Area=Forms![Main Form]!cmbArea;

This code works correctly but I also want to allow all the records to show if Forms![Main Form]!cmbArea = "ALL"



I am not sure how to do this, I tried If statement but my syntax must have been wrong, thanks for help.

[llkhoutx]

To retrieve all records use (using your notation)

SELECT InspectionsDI.ID,InspectionsDI.Area
FROM InspectionsDI 

or

SELECT InspectionsDI.ID,InspectionsDI.Area
FROM InspectionsDI
WHERE InspectionsDI.Area Like "*" 

or

If  isnull([Forms]![Main Form]![cmbArea]) = true then
SELECT InspectionsDI.ID,InspectionsDI.Area
 FROM InspectionsDI
else
SELECT InspectionsDI.ID,InspectionsDI.Area
 FROM InspectionsDI
end if 

Note that "InspectionsDI." prefix to field names isn't required in your simple SQL query.

[cowboy]

None of those options worked, any other ideas?

[NTC]

ummmm - the first example must work because all it is doing is dropping the WHERE clause and thus all should return.....if that doesn't work then there is some sort of misunderstanding in your description....

are you passing that sql statement via vba with an Run Query or are your using a defined query?

[cowboy]

Check out the original post, you didnt just drop the Where clause from the first there is a parameter sent in when the query is called. If I drop the Where clause the query becomes pointless. I just need to have another possibility.

[NTC]

build your query using the query design grid......and then when it is working the way you want - - change it to sqlview to see the syntax....

[cowboy]

by query design grid do you mean design view? I dont see anywhere that lets me set conditions using design view, how would I do that?