Solved

Data Type Mismatch

Posted on 2004-03-31
8
449 Views
Last Modified: 2009-07-29
SELECT qryPDIDDoesNotEqualPDIDEntered.EntryID, CDate([Time Received]) AS TimeReceived, qryPDIDDoesNotEqualPDIDEntered.[Assigned Account Executive], qryPDIDDoesNotEqualPDIDEntered.[Application Account Executive], qryPDIDDoesNotEqualPDIDEntered.[Loan Officer], qryPDIDDoesNotEqualPDIDEntered.[Lender Phone]
FROM qryPDIDDoesNotEqualPDIDEntered
ORDER BY CDate([Time Received]) DESC;



Is there a reason why the above SQL would generate a "Data Type Mismatch" error?


Here is the SQL for qryPDIDDoesNotEqualPDIDEntrered:


SELECT entryLenderView.EntryID, Format([TimeReceived],'m/d/yyyy') AS [Time Received], tblDirector.fullName AS [Assigned Account Executive], qryPDIDEnteredPDName.AEName AS [Application Account Executive], entryLenderView.loName AS [Loan Officer], fnFormatPhone([workNumber]) AS [Lender Phone]
FROM tblDirector INNER JOIN (qryPDIDEnteredPDName INNER JOIN entryLenderView ON qryPDIDEnteredPDName.EntryID = entryLenderView.EntryID) ON tblDirector.directorID = entryLenderView.PDID
WHERE (((nz([entryLenderView].[PDID],""))<>[entryLenderView].[PDIDEntered] And (nz([entryLenderView].[PDID],""))<>"" And (nz([entryLenderView].[PDID],""))<>"0"))
ORDER BY Format([TimeReceived],'m/d/yyyy') DESC;



UUUUGH....a bunch of nested queries......
0
Comment
Question by:knowlton
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Author Comment

by:knowlton
Comment Utility
Here is qryPDIDEnteredPDName:

SELECT [tblEntries].[EntryID], nz([PDIDEntered],"") AS ApplicationPD, [tblDirector].[fullname] AS AEName
FROM tblDirector INNER JOIN tblEntries ON [tblDirector].[directorID]=[tblEntries].[PDIDEntered]
WHERE (((nz([PDIDEntered],""))<>"" And (nz([PDIDEntered],""))<>"0"));
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Change this
 nz([PDIDEntered],"")
to this
 nz([PDIDEntered],0)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
If PDIDEntered is number type
This should be
 nz([PDIDEntered],0)
or you will get a type mismatch.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 200 total points
Comment Utility
Also check for the other fields that you use
the NZ function.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
Wow...that looks painful.

Data type mismatch usually means you are assuming a string is a number, or vice versa.   Since the first query is just a basic select statement, it will probably fall in one of the other two queries.  Try running each of the queries lower in the 'nest' by themselves and see if any generate errors.  That should at least help to narrow down the problem.  Failing that, please post the data types for each field in each query.  Here's a couple of things you can look for yourself:

1) NZ([Field],"") -- Make sure [Field] is a text/memo field.  Otherwise, change the NZ function to return a numeric value instead: NZ([Field],0)
2) Make sure [TimeReceived] is a Date/Time field.  If it's text, then it falls to the user to make sure what they enter is a valid date, and we all know how well that will work out.
3) Make sure calls to external functions (like fnFormatPhone([WorkNumber])) are receiving the proper argument types.  If the function is expecting a number, sending a string gives it a fit.
3) Mae sure comparisons between two fields (in the JOIN..ON clause and in the WHERE clause) are the same data type on each side of the comparison.  VB can auto-convert a number to a string (sometimes..other times it's flaky), but string to number almost always causes grief.
0
 
LVL 50

Assisted Solution

by:Steve Bink
Steve Bink earned 100 total points
Comment Utility
One final note not related to your bug.  Using Format on a date field could turn it into a string.  Not a big deal, normally, except you have an ORDER BY clause on that field, and date ordering will not be the same as string ordering.  Example:

Date                              String
1/1/04                           1/1/04
1/2/04                           1/2/04
2/1/04                           10/1/04
10/1/04                          2/1/04
0
 
LVL 39

Accepted Solution

by:
stevbe earned 200 total points
Comment Utility
what is the underlying datatype of TimeReceived in the table?

do the lower level queries work by themselves?

your orderby clause was already mentioned but you could ... ORDER BY [TimeReceived] DESC;

try pulling out the Order By ... does that work?

if not try adding fields one at a time to see which causes the error

Steve
0
 
LVL 5

Author Comment

by:knowlton
Comment Utility
This seemed to work itself out after a few days.  Must have been a data issue.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

16 Experts available now in Live!

Get 1:1 Help Now