Solved

Data Type Mismatch

Posted on 2004-03-31
8
453 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
ID: 10728300
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10728332
Change this
 nz([PDIDEntered],"")
to this
 nz([PDIDEntered],0)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 10728342
If PDIDEntered is number type
This should be
 nz([PDIDEntered],0)
or you will get a type mismatch.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 10728345
Also check for the other fields that you use
the NZ function.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 10728350
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
ID: 10728364
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
ID: 10734354
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
ID: 10844591
This seemed to work itself out after a few days.  Must have been a data issue.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

772 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