Whole Months Function - Whole Months in Microsoft Access, Part 2


	Back to Whole Months 2 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Whole Months Function

Debby Lawrence

8 months ago

Morning
My Table is LeaseT - fields:
    1&2 : StartDate, EndDate (both type Date/Time)
    3: Lease Period (Type Number > Long Integer) ;

Function: Public Function WholeMonths(StartDate As Date, EndDate As Date) As Long

Form - Control Source for Lease Period Field: =WholeMonths([StartDate],[EndDate])

Problem - when I go to a New record in the form the Lease Period Field displays and error i.e.  "#Type!" - I tried changing field type to Number > Integer   (Same Result)
*Note- once the new record is added the field is populated correctly

	Debby Lawrence OP @Reply 8 months ago
Comment on above - once the record is added value on form is correct but the field in the table is not populated

	Sami Shamma @Reply 8 months ago
Debby please show your form and design view with "All" tab all the way to the top for the Field lease period.

	Debby Lawrence OP @Reply 8 months ago

	Debby Lawrence OP @Reply 8 months ago
Morning Sami thank you for the response. I uploaded pics of Form, Field (LeasePeriod) properties as well as the function.

	Debby Lawrence OP @Reply 8 months ago
Hi Sami - extra info I created this Query and it works perfectly Table- LeaseT Fields: StartDate; EndDate; LPer:WholeMonths([StartDate],[EndDate]) Result: 01-11-2024 to 31-10-2025 Result LPer=12;

Kevin Robertson @Reply

8 months ago

The problem is Null values. Write your function like this:

Public Function WholeMonths(StartDate As Variant, EndDate As Variant) As Variant



    WholeMonths = DateDiff("m", StartDate, EndDate)



End Function

Use Variants if you need to handle null values.

From your original post you have a field in your table so want to store this value. This is unnecessary, but if you really need to store the value returned by the function you can't put it in the Control Source of the field. Use the After Update events of your date fields:

Details

Private Sub CalculateLeaseMonths()



    If Not IsNull(StartDate) And Not IsNull(EndDate) Then

        LeaseMonths = WholeMonths(StartDate, EndDate)

        Me.Dirty = False

    End If

    

End Sub



Private Sub EndDate_AfterUpdate()



    CalculateLeaseMonths

    

End Sub



Private Sub StartDate_AfterUpdate()



    CalculateLeaseMonths

    

End Sub

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Whole Months 2.

Next Unseen

New Feature: Comment Live View