Calculate the number days from previous record

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
Click to expand...

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.
Click to expand...

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]
 
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.

John Vinson said:
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)
Click to expand...

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.
Click to expand...

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]
Click to expand...
 
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.
Click to expand...

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]
 
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.



John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
I changed the field named [Date] to [Last Visit Date]
Click to expand...

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]
 
The [Last Visit Date] in my last post was a typo.

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

Doug

John Vinson said:
I changed the field named [Date] to [Last Visit Date]
Click to expand...

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]
Click to expand...
 
John Vinson said:
I changed the field named [Date] to [Last Visit Date]
Click to expand...

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]
Click to expand...
 
Back
Top