dlookup help

[Guest]

I have the following fields atientid,TravelDate,ReturnDate. the Patientid
is an autonumber , the other two are date/time .For most patients , there
will be more than one record (one visit). what i want is , when the user
enters the traveldate for a new visit of a patient, to look up the table
(PatientsMaintable) , and see the previous visit whether return date has been
entered or not , if not to just prompt the user and give him the patient id.

Any one knows how this can be done?

 

[Guest]

You did not specify the name of the control on your form where the Patientid
is, so I used txtPatient as an example. Change it to the actual name of your
control.

In this case, you really don't want the DLookup. It finds the first
occurance of TravelDate for the Patientid. The DMax will find the most
recent date. Like the DLookup, if no TravelDate exists for the Patientid, it
will return Null. Use a Variant variable to receive the results of the DMax;
otherwise, you would get an Invalid Use of Null error.

Dim varTravelDate As Variant

varTravelDate = DMax("[TravenDate]", "PatientsMaintable",
"Patientid = " & Me.txtPatient)
If IsNull(varTravelDate) Then
MsgBox "No Previous Visits"
Else
MsgBox "Last Visit Was " & varTravelDate
End If

 

[Guest]

Thank alot . it workd , the name is same "Patientid"
thanks again.