If Statement in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
[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
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

#error - Blank 3
Change text format to date format 2
How to convert a .msg File? 2
Data Mismatch in Excel 2010 4
Time Trouble - Data Type, Query, Comparison 2
Update Query Questions 7
How to validate time format from a text field? 2
IF statement in Access ? 2

Back
Top