Solved

Data type mismatch in criteria expression

Posted on 2013-05-23
10
662 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

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!

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…

732 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