Type Conversion errors on update query

[robert_neville]

My database uses code and queries to import data. The complexity relates to the free form nature of the source data, which I can not change without code. One of my update queries delivers several type conversion errors when running it. My challenge becomes dealing with all the type conversion errors.

In the source table, most fields are set to text, yet the update query inserts into date and yes/no data type fields. This situation happens when importing from a text file with free-form data. Some type conversion occurs here.

So I employed the Cdate function with these text-to-date fields. The record without dates causes the update to fail; in other words the date field does not accept null values. This situation was confirmed when the query returned 114 type conversion errors out 116; the lastupdate field only has data in two records. How do you deal with these null values?

Plus, these dates may not be in the precise short date format, ##/##/####. The date data could simulate ##-##-## or ##/##/##. Let me know your suggestions on dealing with these scenarios.

The updates to the yes/no fields return valuation rule violations. The update query attempts to place Yes or No text into a Yes/No field type in the destination table. Do I need to change the data to -1 or 0 for Yes & No? This type of a conversion remains vague to me.

I wish Access would give me more information where these violations occur; like the exact record and corresponding data failure; placed in a log file. I keep banging my head against the wall deciphering these errors while learning about update / append queries. My only resort is posting in the forum for assistance. Please lead me in the right direction.

 

[Mile-O]

How do you deal with these null values?

Most would gravitate towards the Nz() function.

 

[robert_neville]

Thank you for everybody’s suggestions. Here’s a new improve approach.

IIf(Nz([New].[ProjDate]),Null,CDate([New].[ProjDate]))

It works for the LastUpdate field, yet return 4 type conversion errors on the StartDate Field. Arghh!

Ok (saying to myself), count to 10 and breathe. Let investigate the records where these errors occurred, which was easier said than done. After trying out several different tactics, I arrived at the query design window and create a simple query with two fields, ProjName and ProjDate (aka StartDate). The query returned all records, 116.

Next, I placed “Is Null” in the criteria field. The query returns 4 records. Hmm, looks mighty suspicious since the type conversion error number was 4 as well.

Next, I place “” in the criteria field. The query returned 41 records. Then I confirmed that the four “Is Null” records were not part of the 41 records. Looks like a case for Mystery Incorporated.

Hesitantly, I deleted the column’s content; uses find and replace to remove spaces from these fields. But the mystery remains unsolved.

In summary, the said field’s purpose is to append date values to the main table. The field can be NULL. Let me know if you have more ideas.