data type mismatch in criteria expression

[gutmj]

Hi All,

I have a simple query which throws an error (as in subject) on run.

SELECT tblItemMaster.SKU, tblItemMaster.MaterialDescription, MonthName(Month([LastRevision])) AS [Month], Year([LastRevision]) AS [Year], tblItemMaster.LastRevision
FROM tblItemMaster
GROUP BY tblItemMaster.SKU, tblItemMaster.MaterialDescription, MonthName(Month([LastRevision])), Year([LastRevision]), tblItemMaster.LastRevision;

Any thoughts? Thanks

 

[vbaInet]

Month and Year are poor choices for field names because those names are reserved for Access/VBA.

If the data type of [LastRevision] is not Date/Time it will throw this kind of error.

In the meantime, use this:

SELECT SKU, MaterialDescription, MonthName(Month([LastRevision])) AS [Month], Year([LastRevision]) AS [Year], LastRevision
FROM tblItemMaster;

 

[gutmj]

Thanks for that. I will check if the data type can be changed to "date/time".

 

[MSAccessRookie]

Thanks for that. I will check if the data type can be changed to "date/time".

If the Data looks like a date (mm/dd/yyyy), but the Data Type is Text, then you could use cDate() to change the type from Text to Date.

 

[vbaInet]

... but much better to set the data type in the underlying table.

 

[MSAccessRookie]

... but much better to set the data type in the underlying table.

I completely agree with you here, that changing the data type would be the ideal solution to the problem. My experience, on the other hand, has been that I do not always have control over the Table Structures, and as a result, cannot change the format from Text to Date. The OP should change the data type if that is possible, and convert it if it is not.

 

[vbaInet]

My experience, on the other hand, has been that I do not always have control over the Table Structures, and as a result, cannot change the format from Text to Date.

It must get a bit frustrating when you're not allowed to do that

 

[MSAccessRookie]

Believe me that it is. Most of the time I was successful in getting the user to agree, but when a project has been in existence for a long time, Tables and Queries are used over and over, and a simple (and just) change like this one could have an effect on a lot of the Forms, Reports, and other code.

 

[vbaInet]

At least you get paid for fixing their errors