Solved

Data Type Mismatch

Posted on 2004-03-31
8
452 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 119

Expert Comment

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

Expert Comment

by:Rey Obrero
ID: 10728342
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
ID: 10728345
Also check for the other fields that you use
the NZ function.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Report that will show table changes 7 48
data analyst 3 50
MS Access XML API HTTP POST Call Object Send Not Working Correctly 15 38
Search Form not Querying 2 12
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

27 Experts available now in Live!

Get 1:1 Help Now