Solved

Data Type Mismatch

Posted on 2004-03-31
8
456 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:Tom Knowlton
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Author Comment

by:Tom 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
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)

 
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:Tom Knowlton
ID: 10844591
This seemed to work itself out after a few days.  Must have been a data issue.
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

710 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