Locating the middle Month between between two dates spread over more than a year

[Robert Moore]

Hi,

This relates to a wedding enquiry database.

On a Form i have two Date Fields using short date, one for a date of booking and the other for


the date of the wedding which rarely gets changed.

I'm looking to be able to show in a third Data Field, as being the date which is half-way between
the two dates.

It is required to give the user a reminder that the client should get a phone call at this point to see if everything
is still on course, etc

I've looked at the DateDiff, but that's no good for what I need.

Regards

Rob

[ArviLaanemets]

=Int((BookingDate+WeddingDate)/2)
The control with formula must be formatted as date.

The formula above Returns either a date exactly halfway between BookingDate and WeddingDate, or rounds it down to to nearest date in case the sum of dates is not an even number.

[Welshgasman]

Practice in the Immediate window.

tt=Date
tt1 = #07/19/2026#
? tt, tt1
09/02/2025    19/07/2026 
? tt1-tt
 525 
? tt + (tt1-tt)/2
29/10/2025 12:00:00

Dates are actually numbers when stored, with a day being 1.
Your could also use DateAdd()

Not sure why you think DateDiff is no good for you?

? datediff("d",date,tt1)
 525 
  


? dateAdd("d",datediff("d",date,tt1)/2,tt)
29/10/2025

Simple math really?

[jojowhite]

as the other suggested:

BetweenDate=[BookingDate] + (([WeddingDate] - [BookingDate])\2)

[Robert Moore]

Thank to all who have provided me with options.

I've now got what I need

Thanks again

[Robert Moore]

Hi again,

I need some more assistance with my Wedding Database.

I now have a text box on the Form giving me the middle month between the date of booking a wedding, and
the actual wedding date. Just what I wanted. The text box field is local to this Form.

A field from the Table relating to this form needs to receive this data.

The Control Source of the Table field is the name of the field - ie: Contact_Month.

The only way that I can get the data to appear on the Table Field, is to copy and paste from
text box containing the Forms field of the 'Middle date Calculation'.

Thanks in advance

Robert

[jojowhite]

is the field Contact_Month on the same table as the Wedding_date and Booking_date?
if it is, then just add the field to your Form.

then on both Wedding_Date and Booking_Date add code to their AfterUpdate, example:

Private Sub Wedding_Date_AfterUpdate()
CalcBetweenDate
End Sub

Private Sub Booking_Date_AfterUpdate()
CalcBetweenDate
End Sub

Private Sub CalcBetweenDate()
If IsDate(Me!Wedding_Date) And IsDate(Me!Booking_Date) Then
    Me!Contact_Month = Me.Booking_Date + ((Me.Wedding_Date - Me.Booking_Date)\2)
End If
End Sub

also add code to Form's Current event:

Private Sub Form_Current()
If Not IsDate(Me!Contact_Month) Then
   CalcBetweenDate
   Me.Dirty = False
End If
End Sub

[Welshgasman]

You bind that textbox to that field. Tables do not have control sources. Forms and Reports do.

However, as that is a calculated field, it is probably best NOT to store it, unless you recalc any time either of the other two dates change?

[Minty]

Agree with WGM - don't store it as it can always simply be calculated, and will therefore always be correct if any of it's source data points are changed.
This is a general DB rule, as it it stops you trying to capture all the points where the underlying data might change, and then trying to update your stored calculation.

There are some exceptions to the rule, but this isn't one of them!

[Robert Moore]

Thank you all once again for your help.

A quick question for jojwhite. can i apply a refresh to either / or Wedding_Date and Booking_Date fields on the Form ?

Thankyou once again !

[Minty]

If you update either of those values in code or anywhere other than the form, your stored field with the middle date will be incorrect.
VBA updates even in the form will NOT trigger the after update event. That only occurs in the form when someone physically changes the data via the keyboard.

That is another reason for just calculating it for display when you need it, and not storing it.

[jojowhite]

if you already have the Booking and Wedding dates on your table
and want to update the Contact_Month, use an update query
to update the field:

Update YourTableName Set Contact_Month =  Me.Booking_Date + ((Me.Wedding_Date - Me.Booking_Date)\2)

[Robert Moore]

Thank you all once again for your expert advice