Calculate the number days from previous record

[Guest]

I hope I explain this well.

I am putting together a small scheduling database for a house call doctor
medical company. I need to calculate the difference in days based on
information from previous records.

I have 3 fields:
1. Date (date the patient is scheduled)
2. Visit Type (who saw the patient - NP or MD)
3. Number of Days Since Last MD Visit (date diff I need to calculate)

I need to calculate the number of days that have elapsed since the last MD
visit [Visit Type = MD] based the value in the [Date] field to the current
date.

Example of Records
Date Visit Type Number of Days Since
Last MD Visit
1/15/2005 MD 0
2/15/2005 NP 30
3/15/2005 NP 60
4/15/2005 NP 90
5/15/2005 MD 0
6/15/2005 NP 30

The doctors need to be able to look at a patients record and make sure that
an MD is scheduled to see the patient at least once every 90 days since the
last MD visit.

Thanks in advance for any help.

Doug

 

[John Vinson]

I hope I explain this well.

I am putting together a small scheduling database for a house call doctor
medical company. I need to calculate the difference in days based on
information from previous records.

I have 3 fields:
1. Date (date the patient is scheduled)
2. Visit Type (who saw the patient - NP or MD)
3. Number of Days Since Last MD Visit (date diff I need to calculate)

One warning - Date is a reserved word and a bad choice for fieldname;
Access *will* confuse it with the Date() function. I'd suggest
changing this fieldname to VisitDate.

I need to calculate the number of days that have elapsed since the last MD
visit [Visit Type = MD] based the value in the [Date] field to the current
date.

Try:

DateDiff("d", nz(DMax("[date]", "[Visits]", "[PatientID] = " &
[PatientID] & " AND [VisitType] = '" & [VisitType] & "' AND [date] <
#" & [date] & "#"), [date]), [date])

This will look up the most recent visit to this patient (assuming you
have a PatientID field and that the table is named Visits); if there
is none, NZ() will default to the current date record giving you the 0
in your first example.


John W. Vinson[MVP]

 

[Guest]

Thanks for all your help. I have one last question.

All of your code worked except for the NZ(). The NZ() as you mentioned is
supposed to put a "0" if there is none. Instead of a "0" the result is #ERROR.

Any suggestions?

Thanks John.

I hope I explain this well.

I am putting together a small scheduling database for a house call doctor
medical company. I need to calculate the difference in days based on
information from previous records.

I have 3 fields:
1. Date (date the patient is scheduled)
2. Visit Type (who saw the patient - NP or MD)
3. Number of Days Since Last MD Visit (date diff I need to calculate)

One warning - Date is a reserved word and a bad choice for fieldname;
Access *will* confuse it with the Date() function. I'd suggest
changing this fieldname to VisitDate.

I need to calculate the number of days that have elapsed since the last MD
visit [Visit Type = MD] based the value in the [Date] field to the current
date.

Try:

DateDiff("d", nz(DMax("[date]", "[Visits]", "[PatientID] = " &
[PatientID] & " AND [VisitType] = '" & [VisitType] & "' AND [date] <
#" & [date] & "#"), [date]), [date])

This will look up the most recent visit to this patient (assuming you
have a PatientID field and that the table is named Visits); if there
is none, NZ() will default to the current date record giving you the 0
in your first example.


John W. Vinson[MVP]

 

[John Vinson]

All of your code worked except for the NZ(). The NZ() as you mentioned is
supposed to put a "0" if there is none. Instead of a "0" the result is #ERROR.

Actually I was using NZ() with its optional second argument, to return
[date] if the DMax() returns NULL. Did you use that exact expression?
It ahould work...

John W. Vinson[MVP]

 

[Guest]

Here is the code that I used:

=DateDiff("d",NZ(DMax("[LastVisitDate]","[PatientSchedule]","[PatientID] = "
& [PatientID] & " AND [VisitType] = 'MD' AND [LastVisitDate] < #" &
[LastVisitDate] & "#"),[LastVisitDate]),[LastVisitDate])

I changed the field named [Date] to [Last Visit Date] and the value for
[VisitType] to "MD". I need to calculate the number of days since the last MD
visit.

If the first visit is an MD visit, I get #ERROR otherwise everything else
works great.

All of your code worked except for the NZ(). The NZ() as you mentioned is
supposed to put a "0" if there is none. Instead of a "0" the result is #ERROR.

Actually I was using NZ() with its optional second argument, to return
[date] if the DMax() returns NULL. Did you use that exact expression?
It ahould work...

John W. Vinson[MVP]

 

[John Vinson]

I changed the field named [Date] to [Last Visit Date]

Well, you have [LastVisitDate] in the expression - which is NOT the
same as [Last Visit Date]. Was that a typo? Blanks are significant!

Actually on the *very first* visit, there won't be anything in
LastVisitDate at all. Maybe move the check for NULL out of the
function:

=IIf(IsNull([LastVisitDate], 0,
DateDiff("d",DMax("[LastVisitDate]","[PatientSchedule]","[PatientID] =
" & [PatientID] & " AND [VisitType] = 'MD' AND [LastVisitDate] < #" &
[LastVisitDate] & "#"),[LastVisitDate]))

John W. Vinson[MVP]

 

[Guest]

The [Last Visit Date] in my last post was a typo.

All the code worked great. Thanks for all your help!

Doug

I changed the field named [Date] to [Last Visit Date]

Well, you have [LastVisitDate] in the expression - which is NOT the
same as [Last Visit Date]. Was that a typo? Blanks are significant!

Actually on the *very first* visit, there won't be anything in
LastVisitDate at all. Maybe move the check for NULL out of the
function:

=IIf(IsNull([LastVisitDate], 0,
DateDiff("d",DMax("[LastVisitDate]","[PatientSchedule]","[PatientID] =
" & [PatientID] & " AND [VisitType] = 'MD' AND [LastVisitDate] < #" &
[LastVisitDate] & "#"),[LastVisitDate]))

John W. Vinson[MVP]

 

[Guest]

I changed the field named [Date] to [Last Visit Date]

Well, you have [LastVisitDate] in the expression - which is NOT the
same as [Last Visit Date]. Was that a typo? Blanks are significant!

Actually on the *very first* visit, there won't be anything in
LastVisitDate at all. Maybe move the check for NULL out of the
function:

=IIf(IsNull([LastVisitDate], 0,
DateDiff("d",DMax("[LastVisitDate]","[PatientSchedule]","[PatientID] =
" & [PatientID] & " AND [VisitType] = 'MD' AND [LastVisitDate] < #" &
[LastVisitDate] & "#"),[LastVisitDate]))

John W. Vinson[MVP]