?
Solved

Query- Type conversion errors

Posted on 2011-02-25
26
Medium Priority
?
305 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 600 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 93

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 1400 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 93

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 93

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 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