• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

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

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.
2 Solutions
Fabrice LambertFabrice LambertCommented:

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

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now