Solved

Data type mismatch in criteria expression

Posted on 2013-05-23
10
629 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
  • 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

861 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

30 Experts available now in Live!

Get 1:1 Help Now