[AccessD] append query error count

My approach to these problems is to start by getting ALL the data, needed
for the immediate requirement or not, into an import table in Access and
then grooming it using code, if necessary.  Grooming it means bringing it
over to a new work table which is an image of the eventual target.  You also
want an autonumber in the import table which you carry over to the work
table so you can tie back to the original source of any data item. You want
the import process to accept anything, so I would use 256 character text
fields for everything.  Then, in the grooming process, you can use functions
like IsDate to find the errors.  If the operation has to be automated, you
need IIFs and InStrs, etc. to handle all the variations in the imput.

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of
lyle.hannum at co.wake.nc.us
Sent: Friday, February 28, 2003 9:23 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] append query error count



Actually it is data that has come from a text file, thru Excel, and into a
temp Access table. It could be anywhere from several hundred to several
thousand records at a time. I think I have accounted for the null fields
thru nz etc. My real problem is that the layout of the original text file
is a moving target (from mainframe). My errors now are from data
occasionaly being placed in the wrong fields and thus when I move from temp
table (formatted as text) to a valid records table (formatted as
appropriate) the type conversions occur. For instance in last weeks run 2
out of 12500 records failed. The point of cleaning the data first is a
valid one. However I have been tasked with making this process automated
(no user interaction). In fact I have written these update querries to fail
on error, but many here want to just append the data and deal with any
failed records later. The temp table has about 30 fields, and I validate 5
critical ones before attempting to update. I may end up having to validate
all of them.

Lyle Hannum




                    Drew Wutka
                    <DWUTKA at marlow.com>            To:
"'accessd at databaseadvisors.com'"
                    Sent by:
<accessd at databaseadvisors.com>
                    accessd-admin at databasead       cc:
                    visors.com                     Subject:     RE:
[AccessD] append query error
                                                    count

                    02/28/03 12:53 AM
                    Please respond to
                    accessd






I don't suppose it is dumping the records into a temporary table is it?  (A
lot of error producing wizards do that, I don't remember if I have ever
seen
a query do that though....)

Drew

-----Original Message-----
From: lyle.hannum at co.wake.nc.us [mailto:lyle.hannum at co.wake.nc.us]
Sent: Thursday, February 27, 2003 2:43 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] append query error count


Hi

When running a append query you may occasionally generate a Access msgbox
similar to:
"Microsoft cant append all the records in the append query. Microsoft set
17 field(s) to null due to a type conversion failure..."
Does anyone know how to capture the number and type of append failures?
TIA

Lyle Hannum

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com