CODEWITHSUNDEEP
sqlms-accessms-access-2010
Zach
Zach

Reputation: 25

Data type mismatch in Access

I've been banging my head against the wall with a mismatch I keep getting in access. The query I am trying to make is supposed to find customer IDs placed during a period of July 8th through August 8th of 1996, and to show the customer ID and order date columns. The only two relevant tables are the Customer table containing the CustomerID, and the Orders table containing the CustomerID and the OrderDate. I get a mismatch error with the following:

SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN #7/8/1996# AND #8/8/1996#;

Thanks in advance!

Upvotes: 1

Views: 171

Answers (2)

PaDi
PaDi

Reputation: 265

It might be a cause but I am not sure about it.

The format of the date-time in your system and the storage date-time format may be different. This also raises the error as observed from my experience. Just try changing the format dd/mm/yy format or set the format of date time in the query only.

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

  • Access enclosed a date with # signs to indicate a literal value of date.
  • using a single quote means you are comparing a String/Text.

So with a Date data type thus the Data Type mismatch.

If OrderDate is String/Text then It should therefore be:

SELECT CustomerID FROM Orders WHERE OrderDate BETWEEN '7/8/1996' AND '8/8/1996';

Or: Use CDATE() Function

SELECT CustomerID FROM Orders WHERE CDATE(OrderDate) BETWEEN CDATE('7/8/1996') AND CDATE('8/8/1996');

Upvotes: 1

Related Questions