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

Data type mismatch in criteria expression

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
ndornack
Asked:
ndornack
  • 5
  • 4
1 Solution
 
mbizupCommented:
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
 
ndornackAuthor Commented:
I tried this and now the query (qryInOutFinal) doesn't return any data.  What happened now?
InOut.mdb
0
 
peter57rCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ndornackAuthor Commented:
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
 
mbizupCommented:
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
 
ndornackAuthor Commented:
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
 
mbizupCommented:
try what I posted please I think you missed one of the spaces.
0
 
ndornackAuthor Commented:
see attached without the space.  The data in query qryInOutFinal needs to show as in column I of the attached spreadsheet.
InOut.mdb
0
 
mbizupCommented:
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
 
ndornackAuthor Commented:
Works perfectly, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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