[Access 2k3] pb query with dateserial()

[Jean-Didier]

Hello

I need your help please.
I've a table with [dateretract] as text because the csv file is like that.. (format : 2009-12-01) YYYY-MM-DD.

I have used this code in a query to correct it :

DateSerial([COLOR=#0000ff]mid[/COLOR]([Dateretract],[COLOR=#cc66cc]1[/COLOR],[COLOR=#cc66cc]4[/COLOR]), [COLOR=#0000ff]mid[/COLOR]([Dateretract],[COLOR=#cc66cc]6[/COLOR],[COLOR=#cc66cc]2[/COLOR]), [COLOR=#0000ff]mid[/COLOR]([TDateretract],[COLOR=#cc66cc]9[/COLOR],[COLOR=#cc66cc]2[/COLOR]))

This work fine, but in some records in table, this [dateretract] is empty and i get an #error.

Please can you help me to resolve it to remove #error ? I need this [Dateretract] to be empty if a error occures

Sorry for my very bad english... I'm french

Thank you

 

[pbaldy]

Your English is better than my (non-existent) French (though my daughter is conversant). You can use an IIf():

IIf(IsNull(Dateretract),"",DateSerial(mid([Dateretract],1,4), mid([Dateretract],6,2), mid([TDateretract],9,2)))

Putting whatever you want in the middle argument to use when the field is Null.

 

[Jean-Didier]

Thank you very much !! It's work fine

This case is now solved

 

[pbaldy]

Happy to help, and welcome to the site by the way!