Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-12-2019, 11:56 AM   #1
Robert2019
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Robert2019 is on a distinguished road
Type conversion error in append query that worked minutes ago

I have a query that appends data to an existing table. This query is run from a form. It sets query criteria from one field in the form. It's been working great for weeks. It worked yesterday.

I created another append query to append data that's calculated differently and to be appended to the same table as the first. Again it uses some criteria submitted from the form and subform. After much searching of the web I finally figured out how to properly call for a criteria value from my subform. The append query worked.

Then I thought I would get smart and after running the query, I added anew instruction to refresh the form. (I have a refresh button, but I thought if refresh could just happen after running the append query, it would be much nicer.)

Now the append query gets this error: ms access set 0 fields to null due to a type conversion failure

I removed the refresh action. But it's broken. Both append queries are broken. The whole point of the database is to run these queries which split costs for resources across multiple campuses at varying cost shares.

It makes no sense that the queries appended fine one moment and get conversion type errors now. Nothing in the original table was changed, (that I'm aware I changed). Even running the queries outside of the form and without the calls to form data, the conversion error comes up.

Robert2019 is offline   Reply With Quote
Old 07-12-2019, 12:03 PM   #2
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 720
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Type conversion error in append query that worked minutes ago

Quote:
ms access set 0 fields to null due to a type conversion failure
That's only part of a much longer message, yes? It covers about 4 different reasons with a number for each. You have posted the part that doesn't apply (0 records). Can you post the rest?

If you run it anyway, you may or may not be able to see what records were not affected. I'm going to go out on a limb and say those that don't work have null values in one or more fields, thus your query fails, but the rest of the message would help.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)

Last edited by Micron; 07-12-2019 at 12:05 PM. Reason: correction
Micron is offline   Reply With Quote
Old 07-12-2019, 12:14 PM   #3
Robert2019
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Robert2019 is on a distinguished road
Re: Type conversion error in append query that worked minutes ago

Sorry about truncating the error message. I thought it was standard from there out:

"Microsoft Office Access set 0 fields to null due to a type conversion failure, and it didn't add 8 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations."

The query with or without the append setting finds 8 records, which is the correct number to find. If I turn off the append I get eight records, and all fields look correct. They are (appear to be) number fields as they should.

Edit: The table I'm appending to has an autonumber field and I am not trying to put any of my queried fields into that field nor do they have a label of ID.

Robert2019 is offline   Reply With Quote
Old 07-12-2019, 12:37 PM   #4
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 720
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Type conversion error in append query that worked minutes ago

Quote:
it didn't add 8 records to the table due to key violations
This is telling you that one or more fields of the query contain values that cannot be appended because these values already exist in a field(s) where those values are only allowed to be there 1x These fields could be Primary Key fields, or indexed with unique values set to Yes (meaning each value has to be unique in the indexed field). You need to do something like

a) determine which records contain the same values in the indexed field(s) and don't try to write those (if using a select query first, tweak it to not include them) or
b) turn warnings on and off and run the append anyway (not what I would do)
c) use Execute method of CurrentDb object without dbFailOnError parameter (what I would do).

b and c assume that you might be routinely appending to this table from another source, for example, and just need to add what can be added and forget the rest. It's a common approach IMHO, and one that I would just let Access do the work for.

Last edited by Micron; 07-12-2019 at 12:38 PM. Reason: clarification
Micron is offline   Reply With Quote
Old 07-12-2019, 01:09 PM   #5
Robert2019
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Robert2019 is on a distinguished road
Re: Type conversion error in append query that worked minutes ago

Okay, thanks Micron. There are three indexed fields. The primary (no duplicates0, the invID, and the resourceID. The last two permit duplicates as there can be ten campuses paying a share of each resource per invoice.

Fortunately, I sent a copy of this database to the potential client yesterday and that version still works. I will rebuild this morning's "improvements" and see if I can identify a breaking point. The original copy has other odd behaviors going on where subforms are completely locked I can't find the reason they became locked. Instead I'll just revert and start saving a backup daily.
Robert2019 is offline   Reply With Quote
Old 07-12-2019, 01:44 PM   #6
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 720
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Type conversion error in append query that worked minutes ago

One approach that might work for you is to convert a copy of the action query/queries to saved Select query, then use find duplicates wizard where you link target table to new query and look for dupes on indexed (no dupes) and PK fields. You would at least see what Access is attempting to append that it cannot.
Good luck
Micron is offline   Reply With Quote
Old 07-12-2019, 02:28 PM   #7
Robert2019
Newly Registered User
 
Join Date: Jul 2019
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Robert2019 is on a distinguished road
Re: Type conversion error in append query that worked minutes ago

The database is still small enough, testing size, that I ran as a select query and filtered the table and can't find the duplicates. But, as I'm rebuilding it in yesterday's copy, I'm thinking that the problem may be multiple tables have a primary key labeled--ID. Even though when I query them they've got some altered label, Access might be picking up that they are PK "ID" and those numbers probably duplicate the destination tables PK "ID." So I've renamed all my primary keys to be TableNameID.

Append Query one, is working fine in the restored version. Append Query two, the new one, also seems to be working fine. Plus I added the Refresh function after the query completes and it looks better with one fewer click.

Robert2019 is offline   Reply With Quote
Old 07-12-2019, 02:59 PM   #8
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 720
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Type conversion error in append query that worked minutes ago

Quote:
they've got some altered label,
Your fields might have captions, which could confuse you. In table design, see what the field name is and look at the property sheet for the caption property (or review them and switch back to datasheet view if you can remember what is what). I agree, ID makes a poor name for a field and can cause design mistakes when so many have the same name.
Good luck!

Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Data Type Conversion Error 3421: Parameter Query Sonnydl Modules & VBA 13 07-03-2018 09:59 PM
Append Query: Type Conversion Failure jehanim009 Queries 8 09-09-2016 01:43 AM
Append query: data conversion error not triggered by commas rsf Queries 2 06-27-2016 05:53 AM
Type conversion error on select/update/make table query PISI Guy Queries 7 12-13-2011 01:12 PM
"Type conversion failure" error when running update query ktmchugh Queries 2 04-07-2009 07:38 PM




All times are GMT -8. The time now is 08:58 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