I need to calculate the average waiting time between two dates, create_dttm and min of event_dt.

create_dttm is the date the referral was created and only occurs once where event_dt can have multiple entries so I need to find the date difference between the referral date and the first (min) event date entry.

I am trying to use the datediff function but I am getting expression errors, can anyone help?

Thanks!

I am trying to use the datediff function but I am getting expression errors, can anyone help?

Post what you're trying to use and we can help you formulate it properly. It is simply:

DateDiff("d",[FirstField], [SecondField])

I can't post it as its on a company server, its an access front end sql database.

I don't need the difference between 2 date fields.

I need to find out the difference between referral date which occurs first (create_dttm) and the first entry for contact date (event_dt).

So a paitent could have..

referral on 1/1/09 then 4 contacts relating to that referral on 2/2/09, 3/3/09, 4/4/09 and 5/5/09. I need to find the difference between 1/1/09 (referral) and 2/2/09 (the first contact date).

Is there a way of doing this?

I have been trying to put a "Min" criteria into the query but I get errors.

Does this make sense?

Cheers

effectively you need two queries

say we are talking patients

then
1) just find the min of contact date, for each patient (i assume)
2) use this query to compare this date with the referral date for eacxh patient.

so you should have the referral date stored at the patient level - but the contact dates stored in a different table

I can't post it as its on a company server, its an access front end sql database.
No, I didn't mean to post the database. I meant to post what you've tried to do with the DateDiff. What actual code did you try?