CODEWITHSUNDEEP
excelms-access
Vimal
Vimal

Reputation: 57

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

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. Actual query value and Access table structure

Upvotes: 0

Views: 3068

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

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.

Upvotes: 1

Related Questions