Help With Error on Stored Query

[DavidWE]

I am getting an error on a stored query that accepts parameters from a form.

PARAMETERS [Forms]![Scan].[txtScanDate] DateTime, [Forms]![Scan].[txtScanNo] Integer;
SELECT [BoxScans].[Barcode],  [BoxScans].[ScanDate],  [BoxScans].[ScanNo], Sum( [BoxScans].[Quantity]) AS Total
FROM BoxScans
WHERE ([BoxScans].[ScanDate]=[Forms]![Scan].[txtScanDate] And [BoxScans].[ScanNo]=[Forms]![Scan].[txtScanNo])
GROUP BY  [BoxScans].[ScanDate],  [BoxScans].[ScanNo],  [BoxScans].[Barcode];

I am currently testing the query by entering the parameters manually. I enter a date for [Forms]![Scan].[txtScanDate] and then the value "2" for [Forms]![Scan].[txtScanNo].

Executing the query causes the following error:
"The 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 query will work without the parameters and the WHERE clause. Does anyone know what is causing the error?

Thanks

 

[boblarson]

I deleted my other response because I goofed on it.

But how about this:


WHERE ([BoxScans].[ScanDate]=Format([Forms]![Scan].[txtScanDate], "\#mm\/dd\/yyyy\#") And

 

[DavidWE]

Thanks, Bob

I just tried:
WHERE ([BoxScans].[ScanDate]=Format([Forms]![Scan].[txtScanDate], "\#mm\/dd\/yyyy\#")

That causes the error "Syntax error(missing operation) in expression."

The query now looks like:

PARAMETERS [Forms]![Scan].[txtScanDate] DateTime;
SELECT [BoxScans].[Barcode],  [BoxScans].[ScanDate],  [BoxScans].[ScanNo], Sum( [BoxScans].[Quantity]) AS Total
FROM BoxScans
WHERE ([BoxScans].[ScanDate]=Format([Forms]![Scan].[txtScanDate], "\#mm\/dd\/yyyy\#")
GROUP BY  [BoxScans].[ScanDate],  [BoxScans].[ScanNo],  [BoxScans].[Barcode];

I'll add the second parameter when I get past that error.

 

[boblarson]

Thanks, Bob

I just tried:
WHERE ([BoxScans].[ScanDate]=Format([Forms]![Scan].[txtScanDate], "\#mm\/dd\/yyyy\#")

That causes the error "Syntax error(missing operation) in expression."

The query now looks like:

PARAMETERS [Forms]![Scan].[txtScanDate] DateTime;
SELECT [BoxScans].[Barcode],  [BoxScans].[ScanDate],  [BoxScans].[ScanNo], Sum( [BoxScans].[Quantity]) AS Total
FROM BoxScans
WHERE ([BoxScans].[ScanDate]=Format([Forms]![Scan].[txtScanDate], "\#mm\/dd\/yyyy\#")
GROUP BY  [BoxScans].[ScanDate],  [BoxScans].[ScanNo],  [BoxScans].[Barcode];

I'll add the second parameter when I get past that error.

Of course it would give you an error. I only posted the first part but you would need to get rid of the left paren after the word WHERE if you are just using the part I gave you. If you put it all together the way you had it, you shouldn't get the syntax error.

 

[DavidWE]

Of course it would give you an error...

I didn't notice the extra parenthesis. Fixing that got rid of the syntax error.

I just now realized that the ScanDate field was formatted as text instead of date in my table. Once I changed that my original query works without the added Format...\#mm\/dd\/yyyy\#")

Thanks for your help, Bob. Your reply made me realize what I had overlooked.