Growing "Conversion Type Failure" error

[WaterWorks]

Hi all,
Sorry if this question is has been asked repeatedly. I picked up Excel and Access after my coworker abruptly left and while I have done a decent job adapting, there is one error I can't manage to stamp out and it seems to grow every time I update the data base. The database is made up of different tables consisting of water quality data. I have tried formatting all tables as text and other tricks but the error count keeps growing. Its currently a little over 300 errors.

 

[WaterWorks]

That does not answer my question. How are you getting the data from whatever is submitted to you (excel sheets??) into Access tables?

Data is submitted to me as either a PDF or a scanned physical copy of the lab submission sheet. I then enter the results into an Excel sheet that is copy and pasted into the lab table in Access. I am going to check that the column formats in the Excel template sheets match the Access columns.

 

[Micron]

an Excel sheet that is copy and pasted into the lab table in Access.

I'd say that is your main problem. Best to check the field types vs the sheet cells. Even then AFAIK, if you change sheet cell formats where there is already data, there is no guarantee that the data is properly formatted. You sometimes have to delete and re-enter it if you changed the format.

I'd be asking them if they can send it to you as a csv file (comma separated values) or something that vba can work with. While it's called csv, the separators don't have to be commas. You could then dispense with the manual data input.

If you stick with Excel, you need to change the way you get the data into Access.

 

[jackd]

I agree with Micron, avoid the manual data entry. Automate where possible/practical.
Perhaps you could provide your table structures and some sample (made up) data along with the queries/whatever you have in your Access database. We don't want your confidential/proprietary info but we need something to work with to help you.
Post a zip format of a copy of your database with only a few records in each table.

 

[Micron]

@jackd, you're confusing this forum with AccessForums.net? Can't post files here.

 

[jackd]

Micron, Waterworks
Yes, confused the 2 forums. Thanks for the heads up.
You can always try wormhole to send files (see Wormhole - Simple, private file sharing)