Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Data type mismatch in criteria expression

Posted on 2013-05-23
10
Medium Priority
?
675 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 1000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 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