Date function query

[Nunzie23]

HI! Thanks in advance to anyone who can offer some help with this.

In a very simple database I have a field (referral date) and another one (1st appointment) which I need a calculation for. Basically I want to create an additional field that tells me in months and days how long a person was waiting between the date they were referred and the date of the 1st appointment.

If they actually haven't received a 1st appointment yet and the 1st appointment field is blank I would like it to calculate the waiting time so far with the the current date.

I think it's something like [1st appointment] - [referral date] but i'm not sure how to add the if function if the 1st appointment field is blank. And do I put this in the query itself? or in the report, etc.

Thanks again for the help.

 

[alansidman]

Nunzie;
Let's try a new approach. I suspect that Access is having difficulty with the " " to represent a null field.

Try this:

=iif(NZ([Date of 1st Appt],Date()),DateDiff("d",Date(),[Date of 1st Appt]),DateDiff("d",[Date of Referral],[Date of 1st appt]))

In this scenario, we added the NZ function that says if the Date of 1st Appt is null, then use the current date.

Post back with your results.

Alan

 

[xenou]

For the number of days between two dates, you can also subtract:

=NZ([Date of 1st appt],Date())-[Date of Referral])

 

[alansidman]

Xenou;
Excellent addition. I got so wrapped in the =IIF statement that I missed the forest for the trees.

Alan

 

[xenou]

It may be that I have a blind spot for DateDiff as for some reason I almost never use it ... missing forest for trees, I guess. I also seemed to overlook Domain Aggregate functions for a very long time until the last year or so.