Solved

Data type mismatch in criteria expression

Posted on 2013-05-23
10
663 Views
Last Modified: 2013-05-23
I am using Access 2003 and getting an error in a query:  (see attached file)

Data type mismatch in criteria expression

for the query "qryInOutFinal"

What is wrong?
InOut.mdb
0
Comment
Question by:ndornack
[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
  • 5
  • 4
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39191285
Your employee number is TEXT, so it needs to be delimited with single quotes:

SELECT qryInOut00.ID, qryInOut00.EmpNum, tblPunches.PunchDate, qryInOut00.PunchDT, DMin("PunchDT","qryInOut00","[EmpNum] =' " & [qryInOut00].[EmpNum] & "' And [PunchDT] > #" & Format([qryInOut00].[PunchDT],"mm\/dd\/yyyy hh:nn") & "#") AS TNext
FROM qryInOut00 INNER JOIN tblPunches ON qryInOut00.ID = tblPunches.ID
WHERE (((DCount("*","qryInOut00","[EmpNum] =' " & [qryInOut00].[EmpNum] & "' And [PunchDT]  <= #" & Format([qryInOut00].[PunchDT],"mm\/dd\/yyyy hh:nn") & "#") Mod 2)=1)); 

Open in new window

0
 

Author Comment

by:ndornack
ID: 39191509
I tried this and now the query (qryInOutFinal) doesn't return any data.  What happened now?
InOut.mdb
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39191728
THere's an extra space which must be removed...

WHERE (((DCount("*","qryInOut00","[EmpNum] =' " & ......
should be..
WHERE (((DCount("*","qryInOut00","[EmpNum] ='" & ....

No space between the single ' and the "
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:ndornack
ID: 39191738
I am trying to capture the punch out time of the next record.  Attached is a spreadsheet with column I showing what I am looking for.

Also attached is the latest .mdb file for performing this query in "qryInOutFinal"
clockexample.xls
InOut.mdb
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39191786
Did you try Pete's suggestion of removing the extra space?  That was a typo in the query I posted earlier:

SELECT qryInOut00.ID, qryInOut00.EmpNum, tblPunches.PunchDate, qryInOut00.PunchDT, DMin("PunchDT","qryInOut00","[EmpNum] ='" & [qryInOut00].[EmpNum] & "' And [PunchDT] > #" & Format([qryInOut00].[PunchDT],"mm\/dd\/yyyy hh:nn") & "#") AS TNext
FROM qryInOut00 INNER JOIN tblPunches ON qryInOut00.ID = tblPunches.ID
WHERE (((DCount("*","qryInOut00","[EmpNum] ='" & [qryInOut00].[EmpNum] & "' And [PunchDT]  <= #" & Format([qryInOut00].[PunchDT],"mm\/dd\/yyyy hh:nn") & "#") Mod 2)=1));

Open in new window

0
 

Author Comment

by:ndornack
ID: 39191857
Yes, I removed the extra space.  That s/b reflected in the .mdb I posted above.  I need to have the data return like you see in column I of the spreadsheet.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39191909
try what I posted please I think you missed one of the spaces.
0
 

Author Comment

by:ndornack
ID: 39191966
see attached without the space.  The data in query qryInOutFinal needs to show as in column I of the attached spreadsheet.
InOut.mdb
0
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 39192233
Did you try the SQL, exactly as I posted it at http:#a39191786 ?

There was an additional space that I had corrected there.

This has the query revised, using the SQL from my last comment, which removes an additional space in the query.  If that is not what you are looking for, please explain in a little more detail how the results are incorrect, and what you should be seeing:
InOut--1-.mdb
0
 

Author Closing Comment

by:ndornack
ID: 39192251
Works perfectly, thanks.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.

687 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