If Statement in Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Debbie - 19 Jul 2007 18:30 GMT

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.

Reply to this Message

Pat Hartman (MVP) - 19 Jul 2007 19:08 GMT

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

>I am trying to write an if statement in a query that will take a text
>column
[quoted text clipped - 5 lines]
> DisposalDates,
> Above SQL converts text to date but gives an error on blank data.

Reply to this Message

John W. Vinson - 20 Jul 2007 00:51 GMT

>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.
[quoted text clipped - 3 lines]
>[0572 PTA12TD TABLE].DisposalDate, DateValue([DisposalDate]) AS DisposalDates,
>Above SQL converts text to date but gives an error on blank data.

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]

Reply to this Message