Solved

Query- Type conversion errors

Posted on 2011-02-25
26
303 Views
Last Modified: 2012-05-11
I have two tables as described below.

One called RecentDataPull which is imported with all text fields. If I import with a date/time or change to a different format, I get errors.

Another table with emails that is formated as date/time fields when the email was sent. Needs to be date/time so I can do calculations.

However when trying to do an update query, i get a conversion error. Anyone know anyway around this? I have tried datevalue and not sure its working properly.

Please help!
0
Comment
Question by:kwarden13
[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
  • 13
  • 6
  • 4
  • +2
26 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 34980668
is there any data in the date field that is throwing the error?
0
 

Author Comment

by:kwarden13
ID: 34980746
Here is a sampe of what I am talking about with the update query.
 Database10.mdb
0
 

Author Comment

by:kwarden13
ID: 34980763
Also, If you run the update multiple times, you get a different number of errors each time.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
ID: 34981067
You need to do the right type conversions, as well as handle possible null values:


UPDATE tblRecentDataPull INNER JOIN tblCastMemberEmails ON (tblRecentDataPull.Year = tblCastMemberEmails.Calendar_Year) AND (tblRecentDataPull.[Item ID] = tblCastMemberEmails.Item_ID) AND (tblRecentDataPull.[Learner ID] = tblCastMemberEmails.Learner_ID) SET tblCastMemberEmails.[30_Day_Email] = IIf((CDate(Nz([tblRecentDataPull]![30 Day Email],"1 Jan 1900"))>[tblCastMemberEmails]![30_Day_Email]) Or ([tblCastMemberEmails]![30_Day_Email] Is Null),CDate(Nz([tblRecentDataPull]![30 Day Email],"1 Jan 1900")),[tblCastMemberEmails]![30_Day_Email]);

Open in new window

0
 

Author Comment

by:kwarden13
ID: 34981101
what does the 1st of January mean in the query?
0
 

Author Comment

by:kwarden13
ID: 34981167
Also, it still gives me a conversion error?!?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34981192
I ran that query in the sample database you provided, and it executed without errors.

That "1 Jan 1900" is simply a dummy date used in the event that you have a Null in [tblRecentDataPull]![30 Day Email], which in at least one record in the sample file, you did.
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
ID: 34981200
One is data type Date/Time the other is data type text.
You did n ot do a vonversion on the data filed to convert it to the type that the query requires, I didn't check what type it was but the imports from one of the tables is not being converted to sit in the query, hence the errors.  Real pain in the butt this problem, I get it all the time and often forget to convert the data first within the query whilst it is running.  You can simply tell by data which table is not being imported.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 350 total points
ID: 34981263
try this

UPDATE tblRecentDataPull INNER JOIN tblCastMemberEmails ON (tblRecentDataPull.[Learner ID] = tblCastMemberEmails.Learner_ID) AND (tblRecentDataPull.[Item ID] = tblCastMemberEmails.Item_ID) AND (tblRecentDataPull.Year = tblCastMemberEmails.Calendar_Year) SET tblCastMemberEmails.[30_Day_Email] = IIF(([tblRecentDataPull]![30 Day Email] <> null And cdate([tblRecentDataPull]![30 Day Email])>[tblCastMemberEmails]![30_Day_Email]) or ([tblCastMemberEmails]![30_Day_Email] Is Null),cdate([tblRecentDataPull]![30 Day Email]),[tblCastMemberEmails]![30_Day_Email]);
0
 

Author Comment

by:kwarden13
ID: 34981265
For the Jan 1, 1990 date, why can't I have it be blank? I don't want to have to use "Dummy Dates"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34981324

kwarden13,
try the query i posted at http:#a34981263
0
 

Author Comment

by:kwarden13
ID: 34981691
I am trying it now. Thank you Capricorn1
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34981699
>>For the Jan 1, 1990 date, why can't I have it be blank?

Because CDate errors when you try to convert Null to a Date.
0
 

Author Comment

by:kwarden13
ID: 34982235
Capricorn1 this is still giving me a type conversion error. Please help
0
 

Author Comment

by:kwarden13
ID: 34982265
out of 4400 fields that were updated, 400 didnt update. Could it be because its a blank field?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34982284
what type conversion error??

test this one, run query1
Database10Rev.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34982302
<Could it be because its a blank field?> yes,
if you don't want to see that message

tools > options >edit/find > uncheck all options in the Confirm frame
0
 

Author Comment

by:kwarden13
ID: 34982311
does it mess anything up if its a blank field?

Does the query still work properly?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34982315
Please post a few examples of rows that should have updated, but did not.
0
 

Author Comment

by:kwarden13
ID: 34982324
So I ran query 1 and it says 14 fields didnt update because of a conversion error.
0
 

Author Comment

by:kwarden13
ID: 34982339
Capicorn1 posted the db
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34982342
does it mess anything up if its a blank field? NO

Does the query still work properly? YES
0
 

Author Comment

by:kwarden13
ID: 34982345
matthewspatrick- I am not sure which rows. it just says 14 records had a conversion type failure
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34982399
open the table tblCastMemberEmails and verify the field [30_Day_Email] if the field got updated....
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
ID: 34982416
Have you defined which table the errors are from?  Or are they from both tables?
0
 

Author Comment

by:kwarden13
ID: 34982767
it seems to work. i am going to go with it for now. Thanks a lot everyone
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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