Date add

  • Thread starter Thread starter chris
  • Start date Start date
C

chris

I have a form where I enter a date, which is stored in a
table. Is there any way of entering this date in the form
and having access add 8 weeks and then storing that
calculated value in the date field of the table.

Thanks
 
If the other date field must always be exactly 8 weeks from the first date,
do not store it in the table. Instead, type the expression into the Field
row of a query, and you never have to worry about it being wrong. The
expression would be something like this:
PaymentDate: DateAdd("d", 56, [InvoiceDate])

If the other date should default to 8 ways away, but you want the chance to
override it an enter something different, then use the AfterUpdate event
procedure of the first field to assign a value to the 2nd:
Private Sub InvoiceDate_AfterUpdate
Me.PaymentDate = DateAdd("d", 56, Me.InvoiceDate)
End Sub

Further explanation of how and when to use these 2 approaches in article:
Calculated fields
at:
http://members.iinet.net.au/~allenbrowne/casu-14.html
 
Chris,

You can do it in many ways. To begin with, adding 8 weeks to a given date is
as simple as <date> + 56 (8 weeks = 56 days).
Now you can use some simple automation to calculate the new date within the
same text box on the form, so you enter a date and it changes to the
calculated date immediately and visibly, or if you don't want it shown on
the form, you can use a second, hidden textbox to put the calculated date,
and link that to the table instead of the one you make the entry into. In
either case, you would use the Before Update event of the date entry textbox
(assumed name: txtDateEntry) to run either a macro or a piece of code to
calculate the new date.
Scenario 1: In the same textbox
Macro:
Action SetValue
Arguments Item: [txtDateEntry], Expression: [txtDateEntry] + 56
VB Code:
Me!txtDateEntry = Me!txtDateEntry + 56

Scenario 2: In another, hidden(?) textbox (assumed name: txtDateStored)
Macro:
Action SetValue
Arguments Item: [txtDateStored], Expression: [txtDateEntry] + 56
VB Code:
Me!txtDateStored = Me!txtDateEntry + 56

HTH,
Nikos
 
Back
Top