IIF statement in a SELECT query column alias

[lachesis]

Has anyone ever used an IIF statement in a SELECT query, where an alias name is used to generate an alternative value from the original table data?

eg.
SELECT xid, xname,
IIf(DateValue(Format([xDate],"dd/mm/yyyy&quot)=Null,"",DateValue(Format([xDate],"dd/mm/yyyy&quot)) AS LastDate
FROM xTable;

xDate in table xTable is stored in the general Date format ie. dd/MM/yyyy hh:mm:ss AMPM

I've used something similar with strings, but not with the IIF statement.

thanks

 

[KenReay]

Hi

I have used such constructs before, no problem - are you having a particular problem?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lachesis]

Yes, here's the IIF statement:

SELECT ...,
IIF([xDate]="" Or [xDate]=Null, "12:00 AM", Format([xDate]-DateValue(Format([xDate],"dd/mm/yyyy&quot),"Medium Time&quot) AS LastTime, ...
FROM ...

Irrespective of whether there is a Date in xDate (General date:= 10/10/2003 10:00:00 AM) or not, I get #Error as the output.

Im thinking its the way Access handles Dates.

I'm definitely overlooking something -- can't see the wood for the trees!! LOL

thanks

 

[KenReay]

Hi

The second select is not the same as the first.

I do not understand what you are trying to do Datevalue() returns a date from a string, you appear to be converting a date to a date, plus you then subtract it from itself?

Also overcomplicating the test for null or blank

Yes, here's the IIF statement:

SELECT ...,
IIF(Len(Trim(Nz([xDate],"&quot))=0, "12:00 AM", ...the rest I do not understand what you are trying to achieve




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[lachesis]

I didnt mean to confuse you ;-)

I realised my original statement was overcomplicated and found I didnt even have to test for Null, just Boolean outcomes. The reason I was subtracting was to create results in Time output only. Basically the two IIF statements are the same, except one is Date format excluding the time portion; the other Time format excluding the Date portion (hence use of the "Datevalue" to get Date only)

Here are my final "working" versions:

1> Date - sets LastDate to 01/01/1900 if xDate is blank

SELECT...,
IIf([xDate],DateValue(Format ([xDate],"dd/mm/yyyy&quot),"01/01/1900&quot AS LastDate, ...
FROM ...

1> Time - sets LastTime to 12:00 AM if xDate is blank

SELECT...,
IIf([xDate],Format([xDate]-DateValue([xDate]),"hh:mm AMPM&quot,"12:00 AM&quot AS LastTime, ...
FROM ...

thanks