Access Update Query Issue with blank date fields

[gto70ski]

I am trying to update from one table with a short text field that has dates, blanks and "N/A" values to a table with a date type field .

Using the following in the update query:

Update To: DateValue(IIf([Table1DateText]![DateText]="N/A",#12/31/2100#,[Table1DateText]![DateText])).

Works fine for dates and "N/A" but gives the usual "Access cannot update all the records in the update query" popup for the blank date values.

Which is not clean but ok since the non updated fields are blanks anyway.

Looking for suggestions.

Thanks!

 

[stumac]

Your problem is probably with the DateValue function DateVaule(Null) will return an error, try removing it.

IIf([Table1DateText]![DateText]="N/A",#12/31/2100#,[Table1DateText]![DateText])

 

[gto70ski]

Awesome!

Yes, that took care of it!

Thanks!!