Calculating a Date

  • Thread starter Thread starter Chip
  • Start date Start date
C

Chip

I have a 3 different fields in a form each for a date that I want to show
with 5 days added to it, another field (textbox) with 6 months added to in
and another field (textbox) with 1 year added to it. there will be 4
different fields one with the date and the three with the date that is
calculated from the original date. Can this be done in ACCESS? I am new to
the ACCESS and just starting to learn VB so be gentle... THANKS IN
ADVANCE!!!
 
Hope I understand. You want to enter a date (lets name
the field "txtDate". Once you enter the date, you want
three fields to be automatically calculated from it:

1) txtDay = Day([txtDate]) + 5
2) txtMonth = Month([txtDate] + 6
3) txtYear = Year([txtDate]) + 1

However, you will have an issue with this because it will
cause impossible dates (e.g. December + 6 = 18), although
I can't tell if that is what you want or not. If you want
the actual date that you would achieve by adding 1 year, 6
months, and 5 days to a given date, then do the following:

1) Have these fields on your form:
txtDate (this is the one you type the date in)
txtDay
txtMonth
txtYear
(make sure these are the actual names of the fields)

2) In the event properties tab for txtDate, set After
Update to [Event Procedure] and click on the ... next to
it.

3) Paste this VB:

Private Sub txtDate_AfterUpdate()

'*****START HERE*****
Dim intDate As Date 'creates a temporary place holder
for the date you input

intDate = Me.txtDate 'sets the place holder equal to
the date you input
intDate = DateAdd("d", 5, [intDate]) 'adds 5 days to
that date
intDate = DateAdd("m", 6, [intDate]) 'adds 6 months
to that date
intDate = DateAdd("yyyy", 1, [intDate]) 'adds 1 year
to that date

Me.txtDay = Day([intDate]) 'extracts the day from the
resulting date
Me.txtMonth = Month([intDate]) 'extracts the month
from the resulting date
Me.txtYear = Year([intDate]) 'extracts the year from
the resulting date
'*****FINISH HERE*****

End Sub


By the way, just picked up a cool tip from Malek's Access
tips. In a database field (or in Excel), hold the [Ctrl]
key and tap the [:] key to instantly enter today's date.
Do the same but also hold the [Shift] key to enter the
current time.
 
I just read your post again, and now I understand....
Similar to what I gave you, but much simpler:

JUST use the DateAdd function for the AfterUpdate event.
You will ONLY need these three lines of code:

txtDate1 = DateAdd("d", 5, [txtDate])
txtDate2 = DateAdd("m", 6, [txtDate])
txtDate3 = DateAdd("yyyy", 1, [txtDate])

txtDateX are the fields on your form. The DateAdd
function is simple to use and you can learn more about it
using the help function in Access and typing the
keyword "DateAdd".
 
Back
Top