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

date type mismatch in criteria expression

Hello,

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

0
lincstech
Asked:
lincstech
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"))

HTH

Ivo Stoykov
0
 
SheilsCommented:
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

0
 
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
SheilsCommented:
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 & "#"
0
 
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
0
 
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:
    2014/02/14
    14:56:16
    Joe

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

/gustav
0
 
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 :)
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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