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.

 

[welshgasman]

Not a lot to go on here?

 

[Micron]

I have tried formatting all tables as text

That probably made it worse. Data type conversion failures indicate that a conversion function is failing, and that could be just about anywhere. If it was simpler, the error message would probably be like "data type mismatch". I think your only hope is that someone could look at your db. Last thought at the moment: trying to run a conversion function against fields that are null will usually fail. So yes, not much to go on. If you could describe what is being done and to what, it might help.

 

[WaterWorks]

Not a lot to go on here?

Sorry, I'm in the position of not knowing what exactly I am talking about, but also dealing with data that requires some discretion. Its a government biology database that pulls from several lab record sources.

 

[WaterWorks]

If you could describe what is being done and to what, it might help.

Sure, it is a database that holds biological data for the state. I receive records from various labs as well as the field and this data is inputted into Access through Excel spreadsheets. One of the labs that originally sent their own spreadsheet changed for one that doesn't send spreadsheets and I generated a template for the required data entry.

Maybe I'll try starting there, and get a better idea of whether or not my cell formatting is consistent with the old lab's.

This might be preaching to the choir, or maybe this solution does exist, but I which Access had some debugging tool...

 

[Micron]

Well that's the 20,000 foot view so we're getting closer. Can you explain the process that gets the xl data into your db? I'll bet that needs improvement, which might fix your problem. I would do that and forget about fixing your sheets wrt data types because if you use the import or export method and try to use that data in Access, you could still have a problem. That method typically determines data types based on the first 8 (10?) rows and if there is a different type further down in a column you can run into this problem.

 

[jackd]

One of the labs that originally sent their own spreadsheet changed for one that doesn't send spreadsheets and I generated a template for the required data entry.

Why can't that lab send you the data in the format you need? Seems they changed the format for some reason??
You process the data from several labs, why should this one have a separate procedure??

As others have suggested, if you want more specific advice, you'll have to provide some detail.
Table structures, any queries or sql to modify data etc.

 

[WaterWorks]

Why can't that lab send you the data in the format you need? Seems they changed the format for some reason??
You process the data from several labs, why should this one have a separate procedure??

They all have separate procedure. I'm not really in a position to ask for accommodations unfortunately.

Well that's the 20,000 foot view so we're getting closer. Can you explain the process that gets the xl data into your db? I'll bet that needs improvement, which might fix your problem. I would do that and forget about fixing your sheets wrt data types because if you use the import or export method and try to use that data in Access, you could still have a problem. That method typically determines data types based on the first 8 (10?) rows and if there is a different type further down in a column you can run into this problem.

Understood! Each sample collected has a number that is used across different tables within Access, the primary key I believe. Then, any parameters that are collected in the field or read in the lab are associated with this number. Different labs measure different parameters, and provide their results typically through a scanned copy of physical receipts.

 

[WaterWorks]

I appreciate your guys' patience as I try to explain/understand this

 

[Micron]

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