Solved

Query- Type conversion errors

Posted on 2011-02-25
26
300 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
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
 
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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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
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.

 

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 119

Expert Comment

by:Rey Obrero
ID: 34982284
what type conversion error??

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

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

930 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

11 Experts available now in Live!

Get 1:1 Help Now