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)

 

[WaterWorks]

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.

I worked with Access and Excel yesterday and found the issue is with the usage of "<" and ">" for lab results. If a result, like suspended solids, is below detectable threshold it needs a "<". If a result is above threshold it takes a ">". These characters are causing the bulk of my "Type Conversion Failures", even when the column in Excel is formatted as text. Do any suggestions come to mind?

I also spoke with someone at one of the labs and he said they will not send me .CSV files since they don't have the time or extra manpower for the task.

As for showing sample data, I'll have to look into the data structure soon. I don't have consistent days working in Excel/Access...

 

[welshgasman]

So you would need to bring those fields as text and then manipulate them as you wish.
Perhaps a new field for the < or >, perhaps = for the others. Up to you.

Test for the < and > and remove that to the new field and store.

 

[Micron]

As for showing sample data, I'll have to look into the data structure soon. I don't have consistent days working in Excel/Access...

It would really help if we could see both files with just enough data to replicate the issue. I'm not understanding how math operators are causing this.

 

[Micron]

If a result, like suspended solids, is below detectable threshold it needs a "<". If a result is above threshold it takes a ">".

My experience with lab results is limited to reading results, so this may be wrong, but don't you mean "if a result is below or above an acceptable range"? If the parameter test result is below the detectable range, there can be no numerical result because it was undetected (unless maybe the system uses a zero to indicate no result) so what is it less than? Again, probably need to see your data. If you're saying that your data is like "< 0.05 ppm" then yes, that is text and you cannot put it in a numeric field. So if the field is text, you must be using the field's values in some sort of fashion that can accept only number data types, thus the errors. A better approach might be to have the lower limit in one field, the result in another, and the upper limit in another field. Then you can have a field that is text and provides just about anything as a value by way of an expression, including conditional formatting.

We're at 2 pages in this thread now and still don't know much. In absence of files, or at least data, I'm going to have to unsubscribe soon.

 

[WaterWorks]

My experience with lab results is limited to reading results, so this may be wrong, but don't you mean "if a result is below or above an acceptable range"? If the parameter test result is below the detectable range, there can be no numerical result because it was undetected (unless maybe the system uses a zero to indicate no result) so what is it less than? Again, probably need to see your data. If you're saying that your data is like "< 0.05 ppm" then yes, that is text and you cannot put it in a numeric field. So if the field is text, you must be using the field's values in some sort of fashion that can accept only number data types, thus the errors. A better approach might be to have the lower limit in one field, the result in another, and the upper limit in another field. Then you can have a field that is text and provides just about anything as a value by way of an expression, including conditional formatting.

We're at 2 pages in this thread now and still don't know much. In absence of files, or at least data, I'm going to have to unsubscribe soon.

I understand, and really appreciate the support. I was able to figure out the issue before attempting to create data.

So you would need to bring those fields as text and then manipulate them as you wish.
Perhaps a new field for the < or >, perhaps = for the others. Up to you.

Test for the < and > and remove that to the new field and store.

It turns out the issue isn't from the ">" or "<" sign but from the measurable values being different. So the old under range value was "<0.01" for total phosphorus and the new under range value is "<0.077". Same for E. Coli where the old value is ">2419.6" while the value that the new lab is giving is ">2419". This was causing my error.

I appreciate the advice and next time I have a question I'll be sure to include/create sample data!