VBA Help (1 Viewer)

Georgenetwork

Registered User.
Local time
Today, 03:23
Joined
Nov 1, 2019
Messages
21
I am looking for a solution. The have female participant coming for rountine therapy. The participant are visiting 12 times in 3 years.
I want a code to tell me on which visit the participant will turn 18 years old. Here are some idea i tried putting together.
I am only concern about that visit time they will turn 18 and if that time pass, it should do nothing

Some of these participants are minor.
If Visit1 - DOB = 18 Then
Me.AgeStatus = "Turning 18 on visit1"

ElseIf Visit2 - DOB = 18 Then
Me.AgeStatus = "Turning 18 on visit2 "

ElseIf Visit3 - DOB = 18 Then
Me.AgeStatus = "Turning 18 on Visit3"
End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
21,358
Hi. I think you could just compare the years between those dates. What you have right now is calculating the difference in days.
 

isladogs

MVP / VIP
Local time
Today, 10:23
Joined
Jan 14, 2017
Messages
18,186
Whilst you do need to base this on the years rather than dqys, calculating Year(Visit)-Year(DOB) is not sufficient as it depends whether the visit date is before/after their birthday in that year.
You need to use q function to calculate the person's age when the visit occurs.
A forum or Google search will bring plenty of hits, some better than others.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:23
Joined
Feb 28, 2001
Messages
27,001
OK, here are PARTs of the issue.

IF both DOB and Visitn are DATE variables or DATE fields, then their values are computed in DAYS (not years). So your formula would work if the visit occurred on the 18th day of the patient's life. BUT...

You are also using the equals-sign so you would only catch equality in days, because if the visit was not on the person's birthday, the results won't match at all.

You might consider looking at the DATEDIFF function to see if the person's age is 18 - but even there, you have to remember that if the person is ALREADY 18 before that first visit (and has BEEN 18 since the visit prior to Visit1), but will turn 19 before the 3rd visit, you need to filter out that case as well. DATEDIFF always returns an integer number of selected intervals, so won't round off (as far as I recall) if the patient starts the year at 17 and turns 18 between visits. However, be sure to use "yyyy" (years) rather than "y" (day of year) as the interval specifier.

Also, if you are using variable names of Visit1, Visit2, and Visit3, that might be OK - but if those are FIELD names, your database is not normalized.
 

Georgenetwork

Registered User.
Local time
Today, 03:23
Joined
Nov 1, 2019
Messages
21
Whilst you do need to base this on the years rather than dqys, calculating Year(Visit)-Year(DOB) is not sufficient as it depends whether the visit date is before/after their birthday in that year.
You need to use q function to calculate the person's age when the visit occurs.
A forum or Google search will bring plenty of hits, some better than others.

Thanks, I calculated by year but it's not helping. Participant birthday is December 10, 2001 and visitdate is June 10, 2019 and its say 18, but the person wont sign a legal document because they are not yet 18
 

Georgenetwork

Registered User.
Local time
Today, 03:23
Joined
Nov 1, 2019
Messages
21
OK, here are PARTs of the issue.

IF both DOB and Visitn are DATE variables or DATE fields, then their values are computed in DAYS (not years). So your formula would work if the visit occurred on the 18th day of the patient's life. BUT...

You are also using the equals-sign so you would only catch equality in days, because if the visit was not on the person's birthday, the results won't match at all.

You might consider looking at the DATEDIFF function to see if the person's age is 18 - but even there, you have to remember that if the person is ALREADY 18 before that first visit (and has BEEN 18 since the visit prior to Visit1), but will turn 19 before the 3rd visit, you need to filter out that case as well. DATEDIFF always returns an integer number of selected intervals, so won't round off (as far as I recall) if the patient starts the year at 17 and turns 18 between visits. However, be sure to use "yyyy" (years) rather than "y" (day of year) as the interval specifier.

Also, if you are using variable names of Visit1, Visit2, and Visit3, that might be OK - but if those are FIELD names, your database is not normalized.

They are field, not variable. Can you help with the date function based on the logic above?
I calculated by year but it's not helping. Participant birthday is December 10, 2001 and visitdate is June 10, 2019 and its say 18, but the person wont sign a legal document because they are not yet 18
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
21,358
They are field, not variable. Can you help with the date function based on the logic above?
I calculated by year but it's not helping. Participant birthday is December 10, 2001 and visitdate is June 10, 2019 and its say 18, but the person wont sign a legal document because they are not yet 18
Try this:
Code:
DateDiff("yyyy",[DOB],[VisitDate])+(Format([DOB],"mmdd")>=Format([VisitDate],"mmdd"))
 

Cronk

Registered User.
Local time
Today, 21:23
Joined
Jul 4, 2013
Messages
2,770
The OP was
"I want a code to tell me on which visit the participant will turn 18 years old."


It's unlikely that the visit will be on the actual birthday date.


I think the question should be, for a visit date, is the person 18 or more and has the form been signed.


Code:
if DateDiff("yyyy",[DOB],[VisitDate])+(Format([DOB],"mmdd")>=Format([VisitDate],"mmdd"))>= 18 AND NOT dLookup("FormedSigned"], "tblPatients","PatientID=" & [PatientID]) then
   msgbox "Form has to be signed this visit"
endif
 

Georgenetwork

Registered User.
Local time
Today, 03:23
Joined
Nov 1, 2019
Messages
21
You are right! I just want to be alerted that the participant is now 18 so he or she can now Signed. I dont have "formSigned" field in my DB.
If this person turn 18, I want to be reminded in the Status field which will state "now 18".
Remember, there are several visitdates

The OP was



It's unlikely that the visit will be on the actual birthday date.


I think the question should be, for a visit date, is the person 18 or more and has the form been signed.


Code:
if DateDiff("yyyy",[DOB],[VisitDate])+(Format([DOB],"mmdd")>=Format([VisitDate],"mmdd"))>= 18 AND NOT dLookup("FormedSigned"], "tblPatients","PatientID=" & [PatientID]) then
   msgbox "Form has to be signed this visit"
endif
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:23
Joined
Feb 28, 2001
Messages
27,001
I want to be reminded in the Status field which will state "now 18".
Remember, there are several visitdates

This is a marginally different statement than you gave us before. From the description here, you don't want to know on which visit the person is eligible. You want to know on the current visit that the person IS or IS NOT eligible.

If all you want is to know if ON TODAY'S visit the person is legally allowed to sign, that is just something like, if you have a checkbox on the form called EligibleToSign,

Code:
Me.EligibleToSign = DateDiff( "yyyy", [DOB], Now() ) >= 18

That will leave the checkbox unchecked until on or after the person's 18th birthday, after which it will always be checked (each time).
 

Georgenetwork

Registered User.
Local time
Today, 03:23
Joined
Nov 1, 2019
Messages
21
You are still right, but here is the in catch. The report that will printed on each visit date will have the following
Name
Date of Birth
Gender
VisitType - Visit1 or visit2 or visit 3 etc
Status -which should indicate that the person is now 18 or null if they are not yet 18

So i think the code need to be on one of the fields in the report


This is a marginally different statement than you gave us before. From the description here, you don't want to know on which visit the person is eligible. You want to know on the current visit that the person IS or IS NOT eligible.

If all you want is to know if ON TODAY'S visit the person is legally allowed to sign, that is just something like, if you have a checkbox on the form called EligibleToSign,

Code:
Me.EligibleToSign = DateDiff( "yyyy", [DOB], Now() ) >= 18

That will leave the checkbox unchecked until on or after the person's 18th birthday, after which it will always be checked (each time).
 

Georgenetwork

Registered User.
Local time
Today, 03:23
Joined
Nov 1, 2019
Messages
21
You are still right, but here is the in catch. The report that will printed on each visit date will have the following
Name
Date of Birth
Gender
VisitType - Visit1 or visit2 or visit 3 etc
Status -which should indicate that the person is now 18 or null if they are not yet 18

So i think the code need to be on one of the fields in the report



The OP was



It's unlikely that the visit will be on the actual birthday date.


I think the question should be, for a visit date, is the person 18 or more and has the form been signed.


Code:
if DateDiff("yyyy",[DOB],[VisitDate])+(Format([DOB],"mmdd")>=Format([VisitDate],"mmdd"))>= 18 AND NOT dLookup("FormedSigned"], "tblPatients","PatientID=" & [PatientID]) then
   msgbox "Form has to be signed this visit"
endif
 

Cronk

Registered User.
Local time
Today, 21:23
Joined
Jul 4, 2013
Messages
2,770
In your report in the Detail (or wherever you have the text boxes", in the format event, have the following code

Code:
private sub detail_format(cancel as integer, formatcount as integer)
 
   if DateDiff("yyyy",[DOB],[VisitDate])+(Format([DOB],"mmdd")>=Format([VisitDate],"mmdd"))>= 18  then
       '   msgbox "Form has to be signed this visit"
      me.txtStatus="Person is now 18"
   endif
end sub
 

Georgenetwork

Registered User.
Local time
Today, 03:23
Joined
Nov 1, 2019
Messages
21
Thanks for the effort, but the system is now recognizing everyone who is 18 or above instead of recognizing those who just turn 18. For example if you are born January 1, 1980 it says person is now 18

In your report in the Detail (or wherever you have the text boxes", in the format event, have the following code

Code:
private sub detail_format(cancel as integer, formatcount as integer)
 
   if DateDiff("yyyy",[DOB],[VisitDate])+(Format([DOB],"mmdd")>=Format([VisitDate],"mmdd"))>= 18  then
       '   msgbox "Form has to be signed this visit"
      me.txtStatus="Person is now 18"
   endif
end sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:23
Joined
Sep 21, 2011
Messages
14,050
Thanks for the effort, but the system is now recognizing everyone who is 18 or above instead of recognizing those who just turn 18. For example if you are born January 1, 1980 it says person is now 18

So get rid of the > ? :confused:
 

Cronk

Registered User.
Local time
Today, 21:23
Joined
Jul 4, 2013
Messages
2,770
@Gasman
I now believe the OP wants to have the message pop up only once and with 3 visits in a year, the message could pop 3 times depending on when the birthday occurs in the year.


We don't know if there is a field for each patient to indicate whether the form has been signed. I assumed as such and had included the code in my first post in this thread. We now know that the functionality is required in a report so it's just a matter on including that field in the report's source data.


I'd still leave the comparison as >= 18 because there is the possibility that a person turns 18 after the last visit each year (unless every last visit is always on December 31).

Something like
Code:
private sub detail_format(cancel as integer, formatcount as integer)
   if DateDiff("yyyy",[DOB],[VisitDate])+(Format([DOB],"mmdd")>=Format([VisitDate],"mmdd"))>= 18  AND not me.FormedSigned  then
        me.txtStatus="Person is now 18" 

    endif


 end sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:23
Joined
Sep 21, 2011
Messages
14,050
Hi Cronk,
I agree that the requirements are vague. I was thiinking of the frequency of the visits as well. Then again, what guarantee is there that the form will be signed on the first visit.?
 

Cronk

Registered User.
Local time
Today, 21:23
Joined
Jul 4, 2013
Messages
2,770
Code:
Then again, what guarantee is there that the form will be signed on the first visit.?
... on or after 18th birthday.


I had in mind a yes/no field in the patient table (or maybe a DateFormSubmitted) which would be ticked when done. If Form is not supplied, if the field is not updated, then the reminder continues each successive visit.
 

Users who are viewing this thread

Top Bottom