Syntax Error in Expression

[mddaniels]

This expression is working in that it returns correct results in the query, however, when I try to filter the query say by 'between 5/1/09 and 6/1/09', a syntax error message happens. VH helped me with the expression initially and as I said it does work in the query, as long as I don't try to filter any other information.

This is how the SQL appears in the column heading

SELECT ID, [Completed Date], [Procedure], [Dictating MD], Technologist, Location, [Ordered Date], [Arrived Date], IIf([Completed Date]>[Ordered Date],(24*Int([Completed Date]-[Ordered Date])+Format([Completed Date]-[Ordered Date],'h')) & Format([Completed Date]-[Ordered Date],':nn:ss'),'Order DateTime After Completed') AS [Ordered to Completed HH:MM]
FROM [Critical Value Quality Report tbl];

 

[PHV]

Use # for date values.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mddaniels]

I'm probably confusing the issue trying to explain. The syntax error is in the sql statement- SELECT ID, [Completed Date], [Procedure], [Dictating MD], Technologist, Location, [Ordered Date], [Arrived Date], IIf([Completed Date]>[Ordered Date],(24*Int([Completed Date]-[Ordered Date])+Format([Completed Date]-[Ordered Date],'h')) & Format([Completed Date]-[Ordered Date],':nn:ss'),'Order DateTime After Completed') AS [Ordered to Completed HH:MM]
FROM [Critical Value Quality Report tbl]

When I put in the criteria field of the completed date column between #5/1/09#And#6/1/09#, a syntax error message appears with the sql statement listed above. The cursor always highlights the :ss portion of the sql, and I've tried removing that part of the expression but the syntax error continues to occur. All of the wording in the sql is exactly as it is listed in the table.

 

[GKChesterton]

So you've isolated the problem at
[tt]Format([Completed Date]-[Ordered Date],':nn:ss')[/tt]?

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[mddaniels]

If that is what the Access software is doing when it highlights the area in the sql statement syntax error, that was my asumption. However, removing that portion did not correct the syntax error.

 

[PHV]

between #5/1/09#And#6/1/09#
Lack of space around And.
I'd use this criteria:
Between #2009-05-01# And #2009-06-01#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886