"Data type mismatch in criteria expression" in VBA ACCESS SQL

Question

I am trying to fetch data from my Access table into Excel sheet. The below mentioned SQL query is throwing an error "Data type mismatch in criteria expression" while the execution reaches Open query.

I googled for all possible fixes. All are saying the number might be passed in quotes. i checked for it still clueless, The query is pretty simple where i am trying select data from a userform text box in DD-MMM-YYYY format (03-OCT-2018) and comapring with date portion of timestamp field and order by my customer id field

SELECT * FROM Master_Intrpay_Cash where DateValue(LAST_UPDATE_TIMESTAMP)>=#" & Trim(startdate.value) & "# and DateValue(LAST_UPDATE_TIMESTAMP)<=#" & Trim(enddate.value) & "# ORDER BY CUSTOMER_ID

Below Shown is the msgbox showing the query being passed. if it helps.

Also the crazy part is the above query was copy pasted from an existing working query, just changed the table name and timestamp field name. Rest everything is exactly the same.

Sergey S. · Accepted Answer · 2018-10-04 03:55:25Z

1

Try without DateValue:

SELECT *
FROM Master_Intrpay_Cash
WHERE LAST_UPDATE_TIMESTAMP >= # " & Trim(startdate.value) & " #
    AND LAST_UPDATE_TIMESTAMP <= # " & Trim(enddate.value) & " #
ORDER BY CUSTOMER_ID

DateValue expects a String as an argument, your column as Date/Time

Also, a preferable format for the date is #mm/dd/yyyy# (US date format), otherwise you may have problems with different locales.

answered Oct 4, 2018 at 3:55

Sergey S.

6,3461 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vimal Over a year ago

Thank Sergey, It worked. but i dont know its crazy. i have same query querying from same structure table with 'Datevalue' mentioned and its working fine. No Idea whats going on here. May be its already 12 past midnight and my system is already scaring me like this. - On the date format m using in "DD-MMM-YYYY" as i am going to use this code in India.

Collectives™ on Stack Overflow

"Data type mismatch in criteria expression" in VBA ACCESS SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related