?
Solved

Data Type Mismatch

Posted on 2004-03-31
8
Medium Priority
?
460 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 120

Assisted Solution

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

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 51

Assisted Solution

by:Steve Bink
Steve Bink earned 400 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 800 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

719 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