Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-02-2016, 02:42 AM   #1
jehanim009
Newly Registered User
 
Join Date: Dec 2015
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
jehanim009 is on a distinguished road
Append Query: Type Conversion Failure

Hi all,

I'm trying to append a query into one of my tables, the query runs fine as a select query but produces an error when used as an append query.

I've pasted the SQL of the Query below:

INSERT INTO TblSPlan ( StyleID, [Section], PlanDate )
SELECT QryGenerateDates.StyleID, QryGenerateDates.Section, QryGenerateDates.DateSeries
FROM QryGenerateDates
WHERE (((Month([DateSeries]))=[PlanMonth]) AND ((Exists (SELECT * FROM TblSPlan WHERE TblSPlan.PlanDate = QryGenerateDates.DateSeries AND TblSPlan.StyleID = QryGenerateDates.StyleID AND TblSPlan.Section = QryGenerateDates.Section))=False));

I'm assuming the problem is with the date field but have no idea how to solve it.

Any help would be much appreciated.

jehanim009 is offline   Reply With Quote
Old 09-02-2016, 02:49 AM   #2
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,682
Thanks: 2
Thanked 2,065 Times in 2,020 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Append Query: Type Conversion Failure

Check if the fieldtype for StyleID, [Section], PlanDate is correct, look at the table.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 09-02-2016, 02:57 AM   #3
jehanim009
Newly Registered User
 
Join Date: Dec 2015
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
jehanim009 is on a distinguished road
Re: Append Query: Type Conversion Failure

The plan date in both the table and query is set to 'medium date', StyleID is set as a 'long integer' and Section as 'short text' in the table but doesn't have anything in the query.

I might be wrong but there's no place in the query to specify the data type for the latter two.

jehanim009 is offline   Reply With Quote
Old 09-02-2016, 06:39 PM   #4
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,682
Thanks: 2
Thanked 2,065 Times in 2,020 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Append Query: Type Conversion Failure

Post your database with some sample data + name of the query, zip it because you haven't post 10 post yet.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 09-03-2016, 12:47 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,998
Thanks: 40
Thanked 3,573 Times in 3,452 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Append Query: Type Conversion Failure

it might be your exists subquery - the table is not aliased so the query engine might be getting confused between the TblSPlan you are inserting into and the TblSPlan in the subquery

try aliasing

....Exists (SELECT * FROM TblSPlan AS T WHERE...

Also if this is to avoid duplicates then you could modify your table to not allow duplicates (multiple index, no duplicates on the three fields)
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-08-2016, 09:39 PM   #6
jehanim009
Newly Registered User
 
Join Date: Dec 2015
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
jehanim009 is on a distinguished road
Re: Append Query: Type Conversion Failure

Hi,

So I've narrowed down the problem to the following:

The append query works only if the table it appends data to is a local table. It doesn't work if the table is linked to the back-end.

I've attached a sample of my DB for your reference AccessTest.zip

I made the location of my back-end trusted but the problem still occurs.

Any help?
jehanim009 is offline   Reply With Quote
Old 09-09-2016, 12:50 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,998
Thanks: 40
Thanked 3,573 Times in 3,452 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Append Query: Type Conversion Failure

It is your section field which is causing the problem. Not sure which of these is the reason why, but I've been able to fix it by fixing both

1. Section is a reserved word - you need to change it to something different - if you put Section in the field caption, users will still see that description. Here is a link to reserved words

https://support.office.com/en-gb/art...7-da237c63eabe

2. Section is also text and a primary key in your CBOSection table. I suspect that this has resulted in the value in your Sec dropdown not being a text datatype - something has been lost in translation when the table is linked.

It may be either or a combination of both that is causing the issue. The solution, in your qryGenerateDates ensure it is converted to a string by using the CStr function - CStr([Forms]![FMasterPlanB]![Sec]). This may or may not work if you don't fix the reserved words issue.

Going forward I recommend adding an autonumber primary key to your CBOSection table - you can still index Section as indexed, no duplicates to ensure it is cannot be entered twice.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
jehanim009 (09-09-2016)
Old 09-09-2016, 01:17 AM   #8
jehanim009
Newly Registered User
 
Join Date: Dec 2015
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
jehanim009 is on a distinguished road
Re: Append Query: Type Conversion Failure

@CJ_London that worked perfectly.

The problem was solved by converting the section field into a string using the CStr function.

Will stay away from the reserved words from now on.

Thanks loads!
jehanim009 is offline   Reply With Quote
Old 09-09-2016, 01:43 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,998
Thanks: 40
Thanked 3,573 Times in 3,452 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Append Query: Type Conversion Failure

Happy to help

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Tags
access 2013 , append delete queries , date / time , type conversion failure

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Type Conversion Failure mkdrep Queries 5 09-03-2014 04:59 AM
Type Conversion Failure h1mself2 Queries 2 10-21-2012 03:44 PM
Update Query for blanks getting type conversion failure. Kash Queries 1 06-18-2012 04:32 AM
Update query - type conversion failure mrshah Queries 4 12-06-2011 04:49 PM
Type Conversion Failure in Update Query Jemmo Queries 5 10-09-2008 06:21 AM




All times are GMT -8. The time now is 07:08 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World