Date Calculation and Notification

[Guest]

I have fields called InitialAssessmentDate and ReviewDate. I want to be able
to have a bold coloured label appear on the front page of a client record
informing a user (when they access a clients record) when the client is due a
review - usually three months after the Initial Assessment Date/and/or three
months after the Review Date.

So far I have a couple of bold coloured labels that appear when individual
client records open to advise users of other important stuff such as whether
the client is high risk (for personal safety) or if the client needs a
interpreter or signer: The code used for these two beauties is: (thanks go to
ruralguy)

"Private Sub Form_Current()
Me.HighRiskLabel.Visible = Me.High
Me.InterpreterLabel.Visible = Me.Interpreter
End Sub"

Is it possible to do something similar for the date calculation?

Thanks in advance
Doug

 

[strive4peace]

Hi Doug,

You can do something like this:

'~~~~~~~~~~~~~~~~~
dim mBoo as Boolean
mBoo = true 'default value

if not isnull(me.InitialAssessmentDate_controlname) then
mBoo = IIF( _
Date() > DateAdd( _
"m" _
,3" _
, me.Date_controlname _
) _
, true _
, false _
)
end if

Me.Label_controlname.Visible = mBoo
'~~~~~~~~~~~~~~~~~

The reason I used a variable is so that you can modify it if you want to
check something else...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Doug,

Here is one possible method. I don't know if it is the most efficient, but
it seems to work for me...


Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error GoTo ProcError

If Me.NewRecord Then 'Set everything to normal
Me.HighRiskLabel.Visible = True
Me.InterpreterLabel.Visible = True
Me.InitialAssessmentDateLabel.FontWeight = 400
Me.ReviewDateLabel.FontWeight = 400

Else 'This is an existing record
Me.HighRiskLabel.Visible = Me.High
Me.InterpreterLabel.Visible = Me.Interpreter

'Only InitialAssessmentDate entered
If Not IsNull([InitialAssessmentDate]) And IsNull([ReviewDate]) Then
Me.ReviewDateLabel.FontWeight = 400
If DateAdd("m", 3, InitialAssessmentDate) < Date Then
Me.InitialAssessmentDateLabel.FontWeight = 700
Else
Me.InitialAssessmentDateLabel.FontWeight = 400
End If

'Only ReviewDate entered
ElseIf IsNull([InitialAssessmentDate]) And Not IsNull([ReviewDate])
Then
Me.InitialAssessmentDateLabel.FontWeight = 400
If DateAdd("m", 3, ReviewDate) < Date Then
Me.ReviewDateLabel.FontWeight = 700
Else
Me.ReviewDateLabel.FontWeight = 400
End If

'Both Dates entered, so only review date should be considered
ElseIf Not IsNull([InitialAssessmentDate]) And Not
IsNull([ReviewDate]) Then
Me.InitialAssessmentDateLabel.FontWeight = 400

If DateAdd("m", -3, Date) > [ReviewDate] Then
Me.ReviewDateLabel.FontWeight = 700
Else
Me.ReviewDateLabel.FontWeight = 400
End If

'Neither Date entered
ElseIf IsNull([InitialAssessmentDate]) And IsNull([ReviewDate]) Then
Me.ReviewDateLabel.FontWeight = 400
Me.InitialAssessmentDateLabel.FontWeight = 400
End If

End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Current..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Crystal,

Thanks for this, and the warm fuzzies Hope you have an awesome day too.
Unfortunately I am very new to Access and am having problems getting my head
around the more advanced stuff, and to be honest I don't have a clue where to
put the code you wrote or how to interpret how and where to make changes.

On the very up side though, I am interested in this part of your signature:
'Remote Programming and Training' Can you tell me something about this, and
importantly if I could enrol?

Kindest Regards
Doug

 

[Guest]

Hi Tom,

Thank you very much for the efforts you have made in producing this for me.
I truely am overwhelmed by the kindness shown in this forum. As I mentioned
to Crystal earlier though I'm struggling with most of the advanced methods of
doing things in Access. However, I have used what you have provided, and
although it doesn't achieve it has given me the confidence to at least play
around with it, and understand more about what each part of the code does.
You have done me a great service and I am eternally grateful.

Kind Regards
Doug

 

[Guest]

Hi again Tom,

I just had to come back and say 'You Beauty!!!!!!' I played around as I
said I was going to and using your code as a template I achieved, I think all
of what I wanted. Where as you had the two date field labels going from
400pt to 700pt to advise users of some action to be taken, I needed something
a little more in their face a bold coloured label that appeared when they
viewed a client record.

This is what I did, and it seems to work fine:

Private Sub Form_Current()
On Error GoTo ProcError

If Me.NewRecord Then 'Set everything to normal
Me.HighRiskLabel.Visible = False
Me.InterpreterLabel.Visible = False
Me.ReviewDueLabel.Visible = False


Else 'This is an existing record
Me.HighRiskLabel.Visible = Me.High
Me.InterpreterLabel.Visible = Me.Interpreter
Me.ReviewDueLabel.Visible = (DateDiff("d", Me.InitialAssessmentDate,
Date) > 90)

'Only InitialAssessmentDate entered
If Not IsNull([InitialAssessmentDate]) And IsNull([ReviewDate]) Then
Me.ReviewDueLabel.Visible = False
If DateAdd("m", 3, InitialAssessmentDate) < Date Then
Me.ReviewDueLabel.Visible = True

End If

'Only ReviewDate entered
ElseIf IsNull([InitialAssessmentDate]) And Not IsNull([ReviewDate])
Then
Me.InitialAssessmentDateLabel.FontWeight = 400
If DateAdd("m", 3, ReviewDate) < Date Then
Me.ReviewDueLabel.Visible = True
Else
Me.ReviewDueLabel.Visible = False
End If

'Both Dates entered, so only review date should be considered
ElseIf Not IsNull([InitialAssessmentDate]) And Not
IsNull([ReviewDate]) Then
Me.ReviewDueLabel.Visible = False

If DateAdd("m", -3, Date) > [ReviewDate] Then
Me.ReviewDueLabel.Visible = True
Else
Me.ReviewDueLabel.Visible = False
End If

'Neither Date entered
ElseIf IsNull([InitialAssessmentDate]) And IsNull([ReviewDate]) Then
Me.ReviewDueLabel.Visible = False

End If

End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Current..."
Resume ExitProc
End Sub

I couldn't have done this without your help Tom, so thank you once more!

Kindest Regards
Doug

 

[strive4peace]

Hi Doug,

you're welcome

on the training, send me an email (address in my siggy), and I'll be
happy to send you information.

When I get your email, I will also send my 30-page Word document on
Access Basics (for Programming) -- it doesn't cover VBA, but prepares
you for it because it covers essentials in Access.

If you want to get more VBA-specific information, I send out the first 3
chapters of a book I am writing on Programming with VBA to all who
request it.

In the code I posted, there is an extra " in --> ,3"
.... but I see Tom has you on a good path...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

Hi Tom,

Thanks for jumping in!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Glad I could help. Good luck on your project!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________