Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

date type mismatch in criteria expression

Posted on 2014-02-14
7
Medium Priority
?
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 39858406
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 2000 total points
ID: 39858422
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
ID: 39858454
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Sheils
ID: 39858694
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
ID: 39858729
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 52

Expert Comment

by:Gustav Brock
ID: 39858905
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
ID: 39861642
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

610 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