automatically update date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to automatically update field from a set date (hire date) to a date
that will be the 1st day of the month, three months in advance : example
Hire date - 1/14/2007 new date - 4/1/2007. Any suggestions? Thanks!
 
the "new date" is a calculated value, and the rule of thumb is: *don't*
store calculated values in tables; store the raw data (hire date) and
calculate whatever value(s) you need (new date) on the fly. you can
calculate and display a "new date" in a query, form, or report using the
DateAdd() function. suggest you read up on the function in Access Help so
you'll understand how it works.

hth
 
Such function is available in Excel and it returns the 1st day of the month
number of months henceforth from start date (or the last day of the month)
the functions are:
EOMONTH(startDate,NumMonths) or
BOMONTH(startDate,NumMonths).

Once you understand what the function does you can re-write it for yourself
in VBA.
It is not complicated.

JD
 
To get the first day of the month n months from today, use
DateSerial(Year(Date()), Month(Date()) + n, 1)

To get the last day of the month n months from today, use
DateSerial(Year(Date()), Month(Date()) + n + 1, 0)

For an arbitrary date, that would be DateSerial(Year(startDate),
Month(startDate) + n, 1) and DateSerial(Year(startDate), Month(startDate) +
n + 1, 0) respectively.
 
Back
Top