• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

date type mismatch in criteria expression


I'm trying to work out this expression with a query for Microsoft Access 2013 using vb.net and I'm not sure where I'm going wrong.

What I have is three criteria date, time, and a textbox for a name however, I get this message "Date type mismatch in criteria expression"

SELECT * FROM appoint WHERE f_date='" & fdDate.value & "' AND f_time = '" & fdTime.value & "' AND f_user = '" & fdUsers.Text & "'"

Open in new window

1 Solution
Ivo StoykovCommented:
most probably f_date is datetime field but fdDate.value is a string and where clause returns false...

Try using something line cDate(Format( fdDate.value,"yyyy-MM-dd hh:mm:ss"))


Ivo Stoykov
Date values need to be enclosed by pound. Try

SELECT * FROM appoint WHERE f_date=#" & fdDate.value & "# AND f_time = #" & fdTime.value & "# AND f_user = '" & fdUsers.Text & "'"

Open in new window

lincstechAuthor Commented:
The last comment kind of works but it doesn't return any data. I'm using a data grid to view the database table. just returns a blank row
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Make sure you have data that meets all three criteria. May try with one criteria only first then add the second then the third. Code for first and second below

"SELECT * FROM appoint WHERE f_date=#" & fdDate.value & "#"
"SELECT * FROM appoint WHERE f_date=#" & fdDate.value & "# AND f_time = #" & fdTime.value & "#"
lincstechAuthor Commented:
Can you confirm if there is any specific format requirements for the access database. As it stands I have

Date - Format - Date/Time Sort Date
Time - Format - Date/Time Sort Time
Gustav BrockCIOCommented:
You need properly formatted string expressions for your date and time values:

sDate = fdDate.ToString("yyyy\/MM\/dd")
sTime = fdTime.ToString("HH\:mm\:ss")
sUser = fdUsers.Text

Would be:

SELECT * FROM appoint WHERE f_date=#" & sDate & "# AND f_time = #" & sTime & "# AND f_user = '" & sUser & "'"

lincstechAuthor Commented:
Some slight changes to the code for the record is where it says .value I changed that to .text and works a treat.

Thank you for your help experts :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now