If Statement in Query

D

Debbie

I am trying to write an if statement in a query that will take a text column
that shows a date (from a linked table) and convert the text to a date.
Where the text does not have a date and is blank to convert blank data to
blank data in date conversion column.

[0572 PTA12TD TABLE].DisposalDate, DateValue([DisposalDate]) AS DisposalDates,
Above SQL converts text to date but gives an error on blank data.
 
P

Pat Hartman \(MVP\)

[0572 PTA12TD TABLE].DisposalDate,
IIf(IsNull(DisposalDate),null,DateValue([DisposalDate])) AS DisposalDates,
 
J

John W. Vinson

I am trying to write an if statement in a query that will take a text column
that shows a date (from a linked table) and convert the text to a date.
Where the text does not have a date and is blank to convert blank data to
blank data in date conversion column.

[0572 PTA12TD TABLE].DisposalDate, DateValue([DisposalDate]) AS DisposalDates,
Above SQL converts text to date but gives an error on blank data.
Click to expand...

IIF(IsNull([0572 PTA12TD TABLE].DisposalDate, Null, CDate([DisposalDate]))

should do the trick for you (unless the text is non-Null but also not a valid
date).

John W. Vinson [MVP]
 
Top