Solved

date type mismatch in criteria expression

Posted on 2014-02-14
7
361 Views
Last Modified: 2014-02-15
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
Comment
Question by:lincstech
7 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
Comment Utility
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
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
Comment Utility
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
 

Author Comment

by:lincstech
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 16

Expert Comment

by:Sheils
Comment Utility
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
 

Author Comment

by:lincstech
Comment Utility
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
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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
 

Author Closing Comment

by:lincstech
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now