import date value problem

[djemmers]

hi all,

I import the type of driver licence (a b or c) and date of driver license into an access table through an application that I didn't write and don't know how to adjust..
a b c works perfect
BUT date doesn't
when I don't fill in a date it gives 30/12/1899.
this is verry confusing and I would like it to import null or nothing into the access database when date isn't filled in.
I tried all kinds of things with the settings of the table in wich the date goes but nothing seems to work

I think validation rule or standard Value should be able to help me but the things I tried didn't work
Can anyone help me ?

djemmers

 

[KenHigg]

After you do the import, you could run a 'fix-it' type update query that would check to see if the date was 30/12/1899 and if so the update query would set it to null...

Would this work?

 

[djemmers]

well, it would (I thought of that too)

But , new entries come in every day and is not a really ellagant sollution,
I just thought there should be an easy way to fix it in access, something
like "if valye = 30/12/1899 put null" but I don't know where or how to put that in access.

tnx for the reply

djemmers

 

[KenHigg]

Do you discard the file after you import it?

 

[neileg]

Access stores dates as numbers and 30/12/1899 corresponds to zero. You don't need to change this value, you just need to handle it properly.

Create a query based on your table containing the imported data. Add a calculated field that looks like this:
NewDate: IIf([MyDate]=0,Null,[MyDate])

This will give a null value instead of the zero and return the proper date if the value isn't zero.

Use this query any place you would otherwise use your table and you won't have any more problems.

 

[djemmers]

tnx a lot !

djemmers