Append Query: Type Conversion Failure (1 Viewer)

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.

Check if the fieldtype for StyleID, [Section], PlanDate is correct, look at the table.

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.

Post your database with some sample data + name of the query, zip it because you haven't post 10 post yet.

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)

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 View attachment AccessTest.zip

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

Any help?

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/ar...-symbols-e33eb3a9-8baa-4335-9f57-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 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!

Happy to help