How find first date of month of last year after entering the first date of a month of this year

SteveL13
SteveL13 used Ask the Experts™
on
I want to populate a firm field with the first date of month of last year after entering the first date of a month of this year.  SO if I enter 10/1/2017 in the 1st field I want 10/1/2016 to populate the 2nd field.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
Distinguished Expert 2017
Commented:
Hi

You can use a combo of DateSerial, Year, Month and Day functions:
Public Sub test()
    Dim dt As Date
    Dim dtLastYear As Date
    
    dt = Now
    dtLastYear = DateSerial(Year(dt) - 1, Month(dt), Day(dt))
    Debug.Print dt
    Debug.Print dtLastYear
End Sub

Open in new window

Remote Training and Programming
Top Expert 2015
Commented:
there is no need to store this, it can be calulated anytime.  Fabrice's function can be used (change Day(dt) to 1 for first day, or you can also put an equation into a Control Source or calculated field (without =) in a query:
   Control Source:
         =DateSerial(Year( [DateFieldname] ) - 1, Month( [DateFieldname] ), 1)

Open in new window

WHERE DateFieldname is the name of your date field.

DateSerial takes three arguments: year, month number, day number. Year is a function to get the year from a date. Month is a function to get the month number from a date.

when you refer to the first date of a month ... realize that can be calculated from any date by using DateSerial and simply using 1 for the Day parameter (argument)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial