Max date between two dates

[Drivememad]

Hi
Can anyone please help me with the loookup function or VBA for my problem?

Query 1 is a list of all patient results (multiple)
Query 2 is a list of all inpatients at a given point in time.

I want to report on the max result date where it is between Date1 and Date2 in the inpatient list.

Thanks in advance
Julie

[VALIS]

Hi
Can anyone please help me with the loookup function or VBA for my problem?

Query 1 is a list of all patient results (multiple)
Query 2 is a list of all inpatients at a given point in time.

I want to report on the max result date where it is between Date1 and Date2 in the inpatient list.

Thanks in advance
Julie

Julie

There needs to be a common field - PatientID?

Your report needs the following as it's control source

SELECT [QryPatients].PatientID, Max(QryPatientResults.TO) AS MaxOfTO1
FROM [QryPatientsResults] RIGHT JOIN QryPatient ON [QryPatientResults].PatientID = [QryPatients].PatientID
WHERE (((QryPatientResults.DateVisit)>Date1 And (QryPatientResults.DateVisit)<Date2))
GROUP BY [QryPatients].PatientID;

Where
QryPatients = Query 1
QryPatientsResults= Query 2

This should give a report with each patients latest "visit" with the two dates.

[Drivememad]

Many Thanks

Sorted my query based on your SQL.
It was simple really....derrr, mental blockage

Once again thanks