Access SQL help

By suzanne.parker ·
I?ve been learning SQL expressions on the fly for months for Access 2007 and trying to find a decent course to learn it so I don?t? have to bother you folks but for now I still have to.
Right now the person that I?ve been doing this for wants one of the fields to have more than a simple DateDiff calculation. Below I?ve written two versions of what he wants but I don?t know how to go from the IIF statements to the DateDiff statement. I?ve tried a huge number of iterations to attempt to do this:
=IIF([ToDate]>0,IIF([FromDate]<0 THEN =DateDiff(?d?,[ToDate],Now()))

Or in English, it?s:
IF the ToDate is not null and the FromDate is null then calculate the number of days between the ToDate and Today.

I hope I?m making myself clear. Thanks for any help you can send my way!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

For a select

by Tony Hopkinson In reply to Access SQL help

That would be something like

Select DateDiff(day,GetDate(), [ToDate])
from SomeTable Where ToDate is not null and FromDate is Null.

If you want to deal with the case where FromDate is not null as well then something like

When FromDate is null Then DateDiff(day,GetDate(), [ToDate]),
DateDiff(day,[ToDate], [FromDate)
from SomeTable Where ToDate is not null and FromDate is Null.

Off the top of me head anyway.


Collapse -

GetDate not recognized

by suzanne.parker In reply to For a select

Hi Tony,
I've been messing with this since you replied and still no luck. When I try GetDate(), it's not recognized so I tried Now() but am getting an error. I'll keep trying but wanted to say thanks for replying!

Related Discussions

Related Forums