Microsoft ODBC Microsoft access driver data type mismatch in criteria expression

[brncao]

Hey guys,

I'm trying to query a specific date in Excel to pull data from Access. I have a date cell (the parameter) that will refresh the table based on user input [Date].

I get the following error "Microsoft ODBC Microsoft access driver data type mismatch in criteria expression." However, it works when [Date] is a text. I'd rather not convert date to string because it will mess everything else up and adds unnecessary complexity.

So here's the setup:
Access
Table "Bank"

Field: "AS OF DATE"​

Data Type: Date/Time​

​

Query qryBank

SELECT [AS OF DATE], AMOUNT, TEXT
FROM Bank

Excel
Query from MS Access Database

Load qryBank​

Criteria Field: AS OF DATE​

Value: [Parameter1]​

To test it, I enter 1/6/2022 and result shows 2022-01-06 00:00:00 in the table

The table is loaded into Excel. The results does show 1/6/2022. It is a date and not a text.

Parameters "Parameter1"
Get the value from the following cell: =Parameters!$A$2
Parameters!$A$2 is set to 1/6/2022 whose serial number is 44567 just to show you that it is indeed a date and not a text.

Error
"Microsoft ODBC Microsoft access driver data type mismatch in criteria expression"

Please advise on what needs to be done.

Thank you,

 

[Micron]

If 44567 is left aligned, it is the numeric value for the date, but it is text. If it is right aligned, it is the numeric equivalent of the date. This assumes you never applied any alignment on the cell. Access will attempt to handle a data type based on certain rules when it isn't explicitly data typed, but I can't tell you about Excel due to lack of experience. My guess is that the driver is balking because the sheet value is not a date data type, but Access is OK with it, which is why you get a result. If you are 100% sure the sheet value is a date data type, then I don't know what the issue is. Also, Access dates must be in US format (dd/mm/yy) so there's that too. Can't tell from your post if that's Jan 06 or June 01, but that would not be the reason for the error message.

 

[brncao]

I fixed it by removing the AS OF DATE field in Microsoft Query and adding it back. That fixed it. Weird...

 

[Micron]

That is the very definition of weird.