Update text to date field

[SteveR]

I have a table which is imported from the data warehouse
which has a date field. If the date is 12/31/9999 I do
not want to see it but want to see an "X" instead. I get
an error because of the data type incompatabilities. I
realize that I can change the datatype of the local table
to text and there are no problems but the table will be
updated (from the datawarehouse) daily and the user will
not have the ability to change the datatype from
date/time to text.
Any Ideas?

Thank you very much for the help.

 

[Rick Brandt]

I have a table which is imported from the data warehouse
which has a date field. If the date is 12/31/9999 I do
not want to see it but want to see an "X" instead. I get
an error because of the data type incompatabilities. I
realize that I can change the datatype of the local table
to text and there are no problems but the table will be
updated (from the datawarehouse) daily and the user will
not have the ability to change the datatype from
date/time to text.
Any Ideas?

Generally any time imported data needs any "scrubbing" you are better off
importing into a holding table with all fields needing scrubbing as text
and then use append queries from the holding table to do all conversions
into your final tables. In your case the append query could easily use an
Immediate-If function to replace the 12/31/9999 with a Null value.

You can even create a temp database and holding table within it to prevent
the temporary space usage from causing bloat in your production MDB file.

 

[Van T. Dinh]

12/31/9999 is probably a dummy date from data warehouse.

Try an Update Query to get rid of these dummy date and set the Field value
to Null. Something like:

UPDATE YourTable
SET DateField = Null
WHERE DateField =#12/31/9999#

 

[John Spencer (MVP)]

Which version of Access? Where do you want to see the "X" - in a report or on a
form or in a query?

In a query, you could use a calculated text field.

ShowDate:IIF(DateField=#12/31/9999#,"X",Format(DateField,"MM/DD/YYYY"))