Solved

Query- Type conversion errors

Posted on 2011-02-25
26
299 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
  • 13
  • 6
  • 4
  • +2
26 Comments
 
LVL 18

Expert Comment

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

Author Comment

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

Author Comment

by:kwarden13
Comment Utility
Also, If you run the update multiple times, you get a different number of errors each time.
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 150 total points
Comment Utility
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
Comment Utility
what does the 1st of January mean in the query?
0
 

Author Comment

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

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 350 total points
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility

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

Author Comment

by:kwarden13
Comment Utility
I am trying it now. Thank you Capricorn1
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

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

Author Comment

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

Expert Comment

by:Rey Obrero
Comment Utility
what type conversion error??

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

Expert Comment

by:Rey Obrero
Comment Utility
<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
Comment Utility
does it mess anything up if its a blank field?

Does the query still work properly?
0
 
LVL 92

Expert Comment

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

Author Comment

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

Author Comment

by:kwarden13
Comment Utility
Capicorn1 posted the db
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
does it mess anything up if its a blank field? NO

Does the query still work properly? YES
0
 

Author Comment

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

Expert Comment

by:Rey Obrero
Comment Utility
open the table tblCastMemberEmails and verify the field [30_Day_Email] if the field got updated....
0
 
LVL 2

Expert Comment

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

Author Comment

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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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 …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

771 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

14 Experts available now in Live!

Get 1:1 Help Now