Query Quirk (Access 2000)

Topic

New Reply

Good Morning All:

I’m trying to create a query with the following fields: ProductId,
rptMonth: DateSerial(Year([OrderDate]),Month([OrderDate]),1), and Quantity. It is a Totals query with the group by fields being ProductID and rptMonth. The Quantity field is “sum”. Data types are ProductID – Integer; OrderDate – Date/Time; Quantity – Integer. When running the query I receive the following message: Data Type Mismatch in criteria expression. The kicker is that there is no criteria expression in any of the 3 fields. Any and all suggestions are appreciated. TIA.

Reply | Quote

Replies

A query like that should work without problems. Could you switch to SQL view, copy the SQL statement that is displayed, and paste it into a reply?

Reply | Quote

Thanks for the reply. Here is the SQL:

SELECT [Order Details].ProductID, DateSerial(Year([OrderDate]),Month([OrderDate]),1) AS rptMonth, Sum([Order Details].Quanity) AS Total
FROM [Order Details]
GROUP BY [Order Details].ProductID, DateSerial(Year([OrderDate]),Month([OrderDate]),1)
WITH OWNERACCESS OPTION;

Reply | Quote

A quick test give me the same error if there are dates (your OrderDate) that are empty.
The combination of DateSerial and Total are not working with Null or empty dates.
Is this the case in your table ?

Reply | Quote