Two date fields - auto fill tick box if 6 weeks or more apart

  • Thread starter Thread starter callumsalfield
  • Start date Start date
C

callumsalfield

I have two date fields, recieved and date of event, if these are 6
weeks or more apart I want the "recieved on time" tick box below them
to automatically be ticked, how do i do this? Cheers
 
I wouldn't do that.

In a query, I would just calculate the elapsed days using the DateDiff
function.

Field: OnTime: DateDiff("d",ReceivedDate,EventDate) > 41

That should return True or False. The advantage of this is that you don't
have to worry about the OnTime value being corrected if some one changes the
EventDate or ReceivedDate.
 
Thankyou for you advice I will bear that in mind but as these dates
once inputted will never be changed and my empoyers prefer to look at
individual records in the form view it would be an advantage if you
could let me know how to do this (if there is a way)

Cheers
 
There is a way. Use VBA code in the form in the after update event of the
two controls. Since the code needs to run in two places I would write a
small sub in the form's module and then call that sub from the afterupate
event of the two controls. Sample

Private Sub SetOnTime()

If IsDate(Me.ReceivedDate) and IsDate(Me.EventDate) Then
If DateDiff("d",Me.ReceivedDate,Me.EventDate) > 41 Then
Me.OnTime = True
Else
Me.Ontime = False
End If
Else
Me.OnTime = False
End if
End Sub
 
Create a query with all the fields you need to include the Recived and
date of event.

Create addtionaly Column

Over6Weeks: [DateRecived]-[DateofEvent]

Add that Field (Hidden) to you form and use a Setvalue Macro to change
the Check mark to True if the [Over6Weeks] field is >42. Call the Macro
in your from OnLoad.
 
Back
Top