Add 14 days to Dispatch Date

[John]

Hello,

I have a form in my database that we use to check in medical personnel on a
disaster response. One of the fields we capture is dispatch date, this is
when the person starts travel to the disaster site. The person is obligated
to remain for 14 days which will be their end date. As we have hundreds of
personnel coming and going at different days and times, I need to be able to
have a field that takes the dispatch date and automatically adds 14 days to
it.

This will allow me to add the field to a report that will give me dispatch
and end dates for everyone under our command.

Thanks for the help

 

[NevilleT]

Hi John

You can use dateadd. If you have a form with a text box called
txtDespatchDate you create another called something like txtEndDate. You can
use the BeforeUpdate event for txtDespatchDate to include the following.
Me!txtEndDate = dateadd("d",14,Me!txtDespatchDate)
Link it to your table field End Date

 

[John W. Vinson]

Hello,

I have a form in my database that we use to check in medical personnel on a
disaster response. One of the fields we capture is dispatch date, this is
when the person starts travel to the disaster site. The person is obligated
to remain for 14 days which will be their end date. As we have hundreds of
personnel coming and going at different days and times, I need to be able to
have a field that takes the dispatch date and automatically adds 14 days to
it.

This will allow me to add the field to a report that will give me dispatch
and end dates for everyone under our command.

Thanks for the help

Do you want to actually *store* this date in the end date field (so that it
can be edited, e.g. if the end date needs to be extended to 28 days instead of
14, or a bigger disaster elsewhere requires that the person leave before the
14 days?

Or do you want a dynamically calculated field that is 14 days later?

If the latter, put a textbox on the form or report with a Control Source

=DateAdd("d", 14, [DispatchDate])

If the former... post with some more info.

 

[John]

Typically personnel will leave on the 14th, but like in Katrina, people
extended a week at a time or 2 or 3. So yes the date may need to be modified.

Hello,

I have a form in my database that we use to check in medical personnel on a
disaster response. One of the fields we capture is dispatch date, this is
when the person starts travel to the disaster site. The person is obligated
to remain for 14 days which will be their end date. As we have hundreds of
personnel coming and going at different days and times, I need to be able to
have a field that takes the dispatch date and automatically adds 14 days to
it.

This will allow me to add the field to a report that will give me dispatch
and end dates for everyone under our command.

Thanks for the help

Do you want to actually *store* this date in the end date field (so that it
can be edited, e.g. if the end date needs to be extended to 28 days instead of
14, or a bigger disaster elsewhere requires that the person leave before the
14 days?

Or do you want a dynamically calculated field that is 14 days later?

If the latter, put a textbox on the form or report with a Control Source

=DateAdd("d", 14, [DispatchDate])

If the former... post with some more info.

 

[John W. Vinson]

Typically personnel will leave on the 14th, but like in Katrina, people
extended a week at a time or 2 or 3. So yes the date may need to be modified.

Assuming a textbox named txtStart and another named txtEnd, you could use code
in the AfterUpdate event of txtStart:

Private Sub txtStart_AfterUpdate(Cancel as Integer)
If IsNull(Me!txtEnd) Then
Me!txtEnd = DateAdd("d", 14, Me!txtStart)
Else
MsgBox "End date already filled in, check it!", vbOKOnly
End If
End Sub

 

[John]

Thanks for the help!

Assuming a textbox named txtStart and another named txtEnd, you could use code
in the AfterUpdate event of txtStart:

Private Sub txtStart_AfterUpdate(Cancel as Integer)
If IsNull(Me!txtEnd) Then
Me!txtEnd = DateAdd("d", 14, Me!txtStart)
Else
MsgBox "End date already filled in, check it!", vbOKOnly
End If
End Sub

 

[John]

Hello John, As I am new to code, I really cant seem to get the second
suggestion to work. The two field names I am using are Dispatch Date and End
Date (with the space in the names). Now when I copy the code, I tried to add
it to the end but isnt working.

As I am trying to understand what you wrote, when I check a members in and
enter a dispatch date it will check the end date to see if its empty, If it
is it will add 14 days, If it has a date it will do what?

 

[John]

And just one last question, I added the field End Date to my table, I put it
on the form and entered the line =DateAdd("d", 14, [Dispatch Date]) and can
see the right date in the field when I view the form. But when I view the
table the End date column is empty, why is the date not going into the table?

 

[John W. Vinson]

And just one last question, I added the field End Date to my table, I put it
on the form and entered the line =DateAdd("d", 14, [Dispatch Date]) and can
see the right date in the field when I view the form. But when I view the
table the End date column is empty, why is the date not going into the table?

The Control Source of a textbox can be *EITHER* a fieldname, in which case it
will display the value in that field and let you update it; *OR* it can be an
expression, in which case it will display that expression on the form and
ignore the table entirely.

Change the control source of your textbox to [End date]. Actually you might do
well to avoid using spaces (or any other special characters) in fieldnames;
you can get around the problems with them but it just adds to the hassle.

My code

Private Sub txtStart_AfterUpdate(Cancel as Integer)
If IsNull(Me!txtEnd) Then
Me!txtEnd = DateAdd("d", 14, Me!txtStart)
Else
MsgBox "End date already filled in, check it!", vbOKOnly
End If
End Sub

assumes that you have a textbox named txtStart with a control source of [Start
Date], and another textbox named txtEnd bound to [End date]. If the user types
a date into Start date the code will store the date 14 days thereafter into
txtEnd if txtEnd is blank; if there already is data in txtEnd, it pops up a
warning message to the user using the MsgBox function and does nothing else.

 

[John]

Sorry but I am not getting this:
I went to the Dispatch Date field

And just one last question, I added the field End Date to my table, I put it
on the form and entered the line =DateAdd("d", 14, [Dispatch Date]) and can
see the right date in the field when I view the form. But when I view the
table the End date column is empty, why is the date not going into the table?

Thanks for the help!

Sorry but I am not getting this:
I went to the Dispatch Date field
After update
Code builder and added your stuff to the bottom... The two fields are
Dispatch Date and End Date

Below is everything on the code page

Private Sub Button75_Click()
On Error GoTo Err_Button75_Click


DoCmd.GoToRecord , , A_FIRST

Exit_Button75_Click:
Exit Sub

Err_Button75_Click:
MsgBox Error$
Resume Exit_Button75_Click

End Sub

Private Sub Button76_Click()
On Error GoTo Err_Button76_Click


DoCmd.GoToRecord , , A_LAST

Exit_Button76_Click:
Exit Sub

Err_Button76_Click:
MsgBox Error$
Resume Exit_Button76_Click

End Sub

Private Sub Button77_Click()
On Error GoTo Err_Button77_Click


DoCmd.GoToRecord , , A_NEXT

Exit_Button77_Click:
Exit Sub

Err_Button77_Click:
MsgBox Error$
Resume Exit_Button77_Click

End Sub

Private Sub Button83_Click()
On Error GoTo Err_Button83_Click


DoCmd.GoToRecord , , A_PREVIOUS

Exit_Button83_Click:
Exit Sub

Err_Button83_Click:
MsgBox Error$
Resume Exit_Button83_Click

End Sub

Private Sub Button85_Click()
On Error GoTo Err_Button85_Click


DoCmd.Close

Exit_Button85_Click:
Exit Sub

Err_Button85_Click:
MsgBox Error$
Resume Exit_Button85_Click

End Sub

Private Sub Button88_Click()
On Error GoTo Err_Button88_Click


DoCmd.DoMenuItem A_FORMBAR, A_FILE, A_SAVERECORD, , A_MENU_VER20

Exit_Button88_Click:
Exit Sub

Err_Button88_Click:
MsgBox Error$
Resume Exit_Button88_Click

End Sub

Private Sub Button90_Click()
On Error GoTo Err_Button90_Click


DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, 10, , A_MENU_VER20

Exit_Button90_Click:
Exit Sub

Err_Button90_Click:
MsgBox Error$
Resume Exit_Button90_Click

End Sub

Private Sub Find_Resource_Order__Click()
On Error GoTo Err_Find_Resource_Order__Click


DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, 10, , A_MENU_VER20

Exit_Find_Resource_Order__Click:
Exit Sub

Err_Find_Resource_Order__Click:
MsgBox Error$
Resume Exit_Find_Resource_Order__Click

End Sub

Private Sub Resource_Confirmatio_Click()
On Error GoTo Err_Resource_Confirmatio_Click


DoCmd.Close

Exit_Resource_Confirmatio_Click:
Exit Sub

Err_Resource_Confirmatio_Click:
MsgBox Error$
Resume Exit_Resource_Confirmatio_Click

End Sub

Private Sub Command195_Click()
On Error GoTo Err_Command195_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command195_Click:
Exit Sub

Err_Command195_Click:
MsgBox Err.Description
Resume Exit_Command195_Click

End Sub

Private Sub Find_Resource_Click()

End Sub
Private Sub Command223_Click()
On Error GoTo Err_Command223_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command223_Click:
Exit Sub

Err_Command223_Click:
MsgBox Err.Description
Resume Exit_Command223_Click

End Sub
Private Sub Command224_Click()
On Error GoTo Err_Command224_Click


DoCmd.GoToRecord , , acFirst

Exit_Command224_Click:
Exit Sub

Err_Command224_Click:
MsgBox Err.Description
Resume Exit_Command224_Click

End Sub
Private Sub Command225_Click()
On Error GoTo Err_Command225_Click


DoCmd.GoToRecord , , acPrevious

Exit_Command225_Click:
Exit Sub

Err_Command225_Click:
MsgBox Err.Description
Resume Exit_Command225_Click

End Sub
Private Sub Command226_Click()
On Error GoTo Err_Command226_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command226_Click:
Exit Sub

Err_Command226_Click:
MsgBox Err.Description
Resume Exit_Command226_Click

End Sub
Private Sub Command227_Click()
On Error GoTo Err_Command227_Click


DoCmd.GoToRecord , , acNext

Exit_Command227_Click:
Exit Sub

Err_Command227_Click:
MsgBox Err.Description
Resume Exit_Command227_Click

End Sub
Private Sub Command228_Click()
On Error GoTo Err_Command228_Click


DoCmd.GoToRecord , , acLast

Exit_Command228_Click:
Exit Sub

Err_Command228_Click:
MsgBox Err.Description
Resume Exit_Command228_Click

End Sub
Private Sub Command229_Click()
On Error GoTo Err_Command229_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command229_Click:
Exit Sub

Err_Command229_Click:
MsgBox Err.Description
Resume Exit_Command229_Click

End Sub

Private Sub Dispatch_Date_Update(Cancel As Integer)End Sub

Private Sub DISPATCH_DATE_AfterUpdate()

End Sub

 

[John W. Vinson]

Sorry but I am not getting this:
I went to the Dispatch Date field
After update
Code builder and added your stuff to the bottom... The two fields are
Dispatch Date and End Date

Below is everything on the code page

First off... posting a hundred lines of irrelevant code is NOT helpful.

Secondly, it appears that you just blindly copied and pasted the code without
thinking about what it contains:

Private Sub Dispatch_Date_Update(Cancel As Integer)End Sub

The > > characters are newsgroup "reply" indicators, they are not valid VBA
code. You should edit them out.

Thirdly, you've fallen victim to an annoying quirk of VBA. If a control name
has blanks in it, Access translates those blanks to underscores in the names
of subs and functions - but the underscores do NOT work in referencing the
control on the form. Instead you must use square brackets around the name.

Finally, you say "does not work" but did not indicate in what WAY it didn't
work (I'd expect you'ld get a compile error... right?)

Change the AfterUpdate event code to

Private Sub Dispatch_Date_AfterUpdate(Cancel As Integer)
If IsNull(Me![End Date]) Then
Me![end date] = DateAdd("d", 14, Me![dispatch date])
Else
MsgBox "End date already filled in, check it!", vbOKOnly
End If
End Sub

by deleting the code you added previously, and selecting the control on the
form in design view. View its properties, click the ... icon by it, and choose
"Code Builder". Access will give you the Sub and End Sub lines, just edit the
code in between.