Solved

date type mismatch in criteria expression

Posted on 2014-02-14
7
365 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 500 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
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.

 
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 50

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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

749 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