Software

Ask something
Question Locked

Access SQL help

0 helpful
3 replies

by suzanne.parker

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!

3 replies

by tony hopkinson

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

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

Off the top of me head anyway.

HtHs

by suzanne.parker

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!

This discussion is closed to new replies.