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]

Look at using the datediff function. Here is a link to how it is used.

http://www.techonthenet.com/access/functions/date/datediff.php

Alan

 

[Nunzie23]

Ok...so I see how to use the date difference function to give me the number of days if I have a date in the two fields(date of referral field and date of appt field), but I'm not sure how I get it to give me the number of days if the appt date field is blank and I want it to use the current date.

I'm sorry if the answer is there and I just don't understand it as I'm not very familiar with these formulas and doing these functions...this is my first database.

 

[alansidman]

Ok. Try this

=iif([1st Appointment]="",datediff("d",date(),[referral date]),datediff("d",[1st Appointment],[Referral date]))

Alan

 

[Nunzie23]

Ok.........so I put this in the query design....

Expr1: if([Date of 1st Appt]="",DateDiff("d",Date(),[Date of Referral]),DateDiff("d",[Date of1st Appt],[Date of Referral]))

I get an error message that says:

Undefined function 'if' in expression.

What am I doing wrong?

I'm soooo sorry I'm not getting this and if I'm making something very simple more difficult than it needs to be.

 

[alansidman]

Need to make the statement with two i's. =IIF as in Immediate If

Alan

 

[Nunzie23]

It gives me a negative number where there are both dates (-34) but if there is no date in the 1st appt field it is blank. Not calculating by the current date.

 

[alansidman]

Swap the order of the date fields in the DateDiff function.


Alan

 

[Nunzie23]

Thanks Alan, works perfectly for giving me the number of days waiting provided there is a date in both the date of referral and date of 1st appt field. But it doesn't give me a days waiting if there is no date in 1st appt.

Just to see if I've done something else wrong below is the current expression:

Expr1: IIf([Date of 1st Appt]="",DateDiff("d",Date(),[Date of 1st Appt]),DateDiff("d",[Date of Referral],[Date of 1st appt]))

So basically I want it to give me for those with blank 1st appts how long they have been waiting from the date of referral until today(current date).

 

[Nunzie23]

And Alan, I really appreciate all your help. I know this is probably annoying to you as someone who grasps all this and can get their head around it all. I'm probably struggling with something that is quite basic.