Date Problem

[Andre Adams]

Hey guys,

I'm creating a query that needs to be run off of Trade Date. However, all
of my data is put into the database under Settlement date (Trade Date + 3
days). How can I tell the database to add 3 days to all of the items that
populate, thus creating another field called Trade Date?

 

[Klatuu]

Do you mean a control on a form that would be 3 days greater than the
settlement date stored in the table? If so, create a text box on your form
and use the Control Source property to calculate that:

=DateAdd("d", 3, txtSettlementDate)

Certianly you don't mean add a field to your table to store a calculated
value. I am really sure you don't want to violate database normalization
rules by storing a calculated value.

 

[Andre Adams]

Absolutely not. I would not want to store this in a table. Thanks for the
formula. Will the 3 days include weekends and holidays as well? I would
need it to just show business days.

 

[Andre Adams]

Thanks Steve, worked like a charm. The only thing is, I need it to factor in
business days (without weekends or holidays as the market is closed). How do
I tell Access that?

Put the following in a blank field in your query:

[TradeDate]ateAdd("D",-3,[SettlementDate])

Steve

Hey guys,

I'm creating a query that needs to be run off of Trade Date. However, all
of my data is put into the database under Settlement date (Trade Date + 3
days). How can I tell the database to add 3 days to all of the items
that
populate, thus creating another field called Trade Date?

 

[Klatuu]

Okay, no problem. You will need a holiday table. Here is a function that
returns a date in the future or past that excludes Saturdays, Sundays, and
any date in the holiday table. Put it in a standard module and change the
control source of your code:

=AddWorkDays(txtSettlemetDate, 3)

Note, if you have a holiday table, change the table and field name in this
code. If not, you can create one using any names you like as long as they
match. My table has an additional field to store the name of the holiday,
but that isn't important for this function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The table
should
' : Contain one row for each holiday. As written, the table is
named
' : tblHolidays and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

 

[Klatuu]

I disagree with Steve's suggestion if you are using an mdb as your back end
database. Jet is a file server database engine and no logic or processing
happens at the back end database level. That means when you use functions in
queries with Jet, it degrades performance. Sometimes it is necessary, but
when you can, keep the logic at the form or report level.

If you are using SQL server or some other client server database engine,
then putting the logic in the back end database is preferrable.
--
Dave Hargis, Microsoft Access MVP

Thanks Steve, worked like a charm. The only thing is, I need it to factor in
business days (without weekends or holidays as the market is closed). How do
I tell Access that?

Put the following in a blank field in your query:

[TradeDate]ateAdd("D",-3,[SettlementDate])

Steve

Hey guys,

I'm creating a query that needs to be run off of Trade Date. However, all
of my data is put into the database under Settlement date (Trade Date + 3
days). How can I tell the database to add 3 days to all of the items
that
populate, thus creating another field called Trade Date?

 

[Andre Adams]

Thank you very much Klatuu. I have no idea how to use this VBA code. I
don't know where to put it or what each line means in relation to my data.
Can you break this down just a little bit more for me? The furthest I got
was adding the holiday table and then everything just went blank for me.
I've never studied this part of access that indepthly before. Please help,
I'm 80% done with this project and I would like to finish this as soon as I
can.

Okay, no problem. You will need a holiday table. Here is a function that
returns a date in the future or past that excludes Saturdays, Sundays, and
any date in the holiday table. Put it in a standard module and change the
control source of your code:

=AddWorkDays(txtSettlemetDate, 3)

Note, if you have a holiday table, change the table and field name in this
code. If not, you can create one using any names you like as long as they
match. My table has an additional field to store the name of the holiday,
but that isn't important for this function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The table
should
' : Contain one row for each holiday. As written, the table is
named
' : tblHolidays and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Absolutely not. I would not want to store this in a table. Thanks for the
formula. Will the 3 days include weekends and holidays as well? I would
need it to just show business days.

 

[Klatuu]

You bet.
First, copy and paste the code into a standard module. That would be:
Choose the modules pane in the database window
Click new to create a new module - The VB editor (VBE) will open
Paste the code into the blank module.
Save it and give it a name, but the name cannot be the same as the function
name.

Now, to use it in your query, open the query builder. You will need to
create a calculated field. That is a field name that doesn't exist in the
table. Then you call the function using the date you want to add the 3 days
to. So in the field name row it would look something like:

TradeDate: AddWorkDays([SettlementDate], 3)

So your query will then have a field name TradeDate that will be 3 working
days after the SettlementDate field in the table.
--
Dave Hargis, Microsoft Access MVP

Thank you very much Klatuu. I have no idea how to use this VBA code. I
don't know where to put it or what each line means in relation to my data.
Can you break this down just a little bit more for me? The furthest I got
was adding the holiday table and then everything just went blank for me.
I've never studied this part of access that indepthly before. Please help,
I'm 80% done with this project and I would like to finish this as soon as I
can.

Okay, no problem. You will need a holiday table. Here is a function that
returns a date in the future or past that excludes Saturdays, Sundays, and
any date in the holiday table. Put it in a standard module and change the
control source of your code:

=AddWorkDays(txtSettlemetDate, 3)

Note, if you have a holiday table, change the table and field name in this
code. If not, you can create one using any names you like as long as they
match. My table has an additional field to store the name of the holiday,
but that isn't important for this function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The table
should
' : Contain one row for each holiday. As written, the table is
named
' : tblHolidays and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP

Absolutely not. I would not want to store this in a table. Thanks for the
formula. Will the 3 days include weekends and holidays as well? I would
need it to just show business days.

:

Do you mean a control on a form that would be 3 days greater than the
settlement date stored in the table? If so, create a text box on your form
and use the Control Source property to calculate that:

=DateAdd("d", 3, txtSettlementDate)

Certianly you don't mean add a field to your table to store a calculated
value. I am really sure you don't want to violate database normalization
rules by storing a calculated value.
--
Dave Hargis, Microsoft Access MVP


:

Hey guys,

I'm creating a query that needs to be run off of Trade Date. However, all
of my data is put into the database under Settlement date (Trade Date + 3
days). How can I tell the database to add 3 days to all of the items that
populate, thus creating another field called Trade Date?

 

[Andre Adams]

Hmph. I never used the module tab before. I did everything you told me to
do and everything seemed great until it told me that the VB Editor had a
syntax error in it. What do I do now?

You bet.
First, copy and paste the code into a standard module. That would be:
Choose the modules pane in the database window
Click new to create a new module - The VB editor (VBE) will open
Paste the code into the blank module.
Save it and give it a name, but the name cannot be the same as the function
name.

Now, to use it in your query, open the query builder. You will need to
create a calculated field. That is a field name that doesn't exist in the
table. Then you call the function using the date you want to add the 3 days
to. So in the field name row it would look something like:

TradeDate: AddWorkDays([SettlementDate], 3)

So your query will then have a field name TradeDate that will be 3 working
days after the SettlementDate field in the table.
--
Dave Hargis, Microsoft Access MVP

Thank you very much Klatuu. I have no idea how to use this VBA code. I
don't know where to put it or what each line means in relation to my data.
Can you break this down just a little bit more for me? The furthest I got
was adding the holiday table and then everything just went blank for me.
I've never studied this part of access that indepthly before. Please help,
I'm 80% done with this project and I would like to finish this as soon as I
can.

Okay, no problem. You will need a holiday table. Here is a function that
returns a date in the future or past that excludes Saturdays, Sundays, and
any date in the holiday table. Put it in a standard module and change the
control source of your code:

=AddWorkDays(txtSettlemetDate, 3)

Note, if you have a holiday table, change the table and field name in this
code. If not, you can create one using any names you like as long as they
match. My table has an additional field to store the name of the holiday,
but that isn't important for this function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The table
should
' : Contain one row for each holiday. As written, the table is
named
' : tblHolidays and the field used is a Date field named Holidate
' : No time vaule should be in the Holidate field. Time values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Absolutely not. I would not want to store this in a table. Thanks for the
formula. Will the 3 days include weekends and holidays as well? I would
need it to just show business days.

:

Do you mean a control on a form that would be 3 days greater than the
settlement date stored in the table? If so, create a text box on your form
and use the Control Source property to calculate that:

=DateAdd("d", 3, txtSettlementDate)

Certianly you don't mean add a field to your table to store a calculated
value. I am really sure you don't want to violate database normalization
rules by storing a calculated value.
--
Dave Hargis, Microsoft Access MVP


:

Hey guys,

I'm creating a query that needs to be run off of Trade Date. However, all
of my data is put into the database under Settlement date (Trade Date + 3
days). How can I tell the database to add 3 days to all of the items that
populate, thus creating another field called Trade Date?

 

[Douglas J. Steele]

Unfortunately, you fell victim of wordwrap in what Dave posted (some of his
lines of code appeared as two lines in his post).

Here's a version that shouldn't have that problem:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number
' of days after or before a given date.
' Arguments : OriginalDate The date you want to add or subtract
' from to determine a worday
' : DaysToAdd Either a positive or negative number of
' working days
' : Saturdays and Sundays are not counted
' Holidays can also be omitted using a holiday table.
' The table should contain one row for each holiday.
' As written, the table is named tblHolidays and the field
' used is a Date field named Holidate
' No time vaule should be in the Holidate field.
' Time values may produce incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays( _
OriginalDate As Date, _
DaysToAdd As Long _
) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #" & _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:

On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in function AddWorkDays"
GoTo AddWorkDays_Exit

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hmph. I never used the module tab before. I did everything you told me
to
do and everything seemed great until it told me that the VB Editor had a
syntax error in it. What do I do now?

You bet.
First, copy and paste the code into a standard module. That would be:
Choose the modules pane in the database window
Click new to create a new module - The VB editor (VBE) will open
Paste the code into the blank module.
Save it and give it a name, but the name cannot be the same as the
function
name.

Now, to use it in your query, open the query builder. You will need to
create a calculated field. That is a field name that doesn't exist in
the
table. Then you call the function using the date you want to add the 3
days
to. So in the field name row it would look something like:

TradeDate: AddWorkDays([SettlementDate], 3)

So your query will then have a field name TradeDate that will be 3
working
days after the SettlementDate field in the table.
--
Dave Hargis, Microsoft Access MVP

Thank you very much Klatuu. I have no idea how to use this VBA code.
I
don't know where to put it or what each line means in relation to my
data.
Can you break this down just a little bit more for me? The furthest I
got
was adding the holiday table and then everything just went blank for
me.
I've never studied this part of access that indepthly before. Please
help,
I'm 80% done with this project and I would like to finish this as soon
as I
can.

:

Okay, no problem. You will need a holiday table. Here is a function
that
returns a date in the future or past that excludes Saturdays,
Sundays, and
any date in the holiday table. Put it in a standard module and
change the
control source of your code:

=AddWorkDays(txtSettlemetDate, 3)

Note, if you have a holiday table, change the table and field name in
this
code. If not, you can create one using any names you like as long as
they
match. My table has an additional field to store the name of the
holiday,
but that isn't important for this function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of
days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from
to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of
working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The
table
should
' : Contain one row for each holiday. As written, the
table is
named
' : tblHolidays and the field used is a Date field named
Holidate
' : No time vaule should be in the Holidate field. Time
values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long)
As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays",
"[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Absolutely not. I would not want to store this in a table. Thanks
for the
formula. Will the 3 days include weekends and holidays as well? I
would
need it to just show business days.

:

Do you mean a control on a form that would be 3 days greater than
the
settlement date stored in the table? If so, create a text box on
your form
and use the Control Source property to calculate that:

=DateAdd("d", 3, txtSettlementDate)

Certianly you don't mean add a field to your table to store a
calculated
value. I am really sure you don't want to violate database
normalization
rules by storing a calculated value.
--
Dave Hargis, Microsoft Access MVP


:

Hey guys,

I'm creating a query that needs to be run off of Trade Date.
However, all
of my data is put into the database under Settlement date
(Trade Date + 3
days). How can I tell the database to add 3 days to all of
the items that
populate, thus creating another field called Trade Date?

 

[Andre Adams]

Absolutely Beautiful man. Thanks for allllllll of you help! I appreciate it.

Unfortunately, you fell victim of wordwrap in what Dave posted (some of his
lines of code appeared as two lines in his post).

Here's a version that shouldn't have that problem:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number
' of days after or before a given date.
' Arguments : OriginalDate The date you want to add or subtract
' from to determine a worday
' : DaysToAdd Either a positive or negative number of
' working days
' : Saturdays and Sundays are not counted
' Holidays can also be omitted using a holiday table.
' The table should contain one row for each holiday.
' As written, the table is named tblHolidays and the field
' used is a Date field named Holidate
' No time vaule should be in the Holidate field.
' Time values may produce incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays( _
OriginalDate As Date, _
DaysToAdd As Long _
) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #" & _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:

On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in function AddWorkDays"
GoTo AddWorkDays_Exit

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hmph. I never used the module tab before. I did everything you told me
to
do and everything seemed great until it told me that the VB Editor had a
syntax error in it. What do I do now?

You bet.
First, copy and paste the code into a standard module. That would be:
Choose the modules pane in the database window
Click new to create a new module - The VB editor (VBE) will open
Paste the code into the blank module.
Save it and give it a name, but the name cannot be the same as the
function
name.

Now, to use it in your query, open the query builder. You will need to
create a calculated field. That is a field name that doesn't exist in
the
table. Then you call the function using the date you want to add the 3
days
to. So in the field name row it would look something like:

TradeDate: AddWorkDays([SettlementDate], 3)

So your query will then have a field name TradeDate that will be 3
working
days after the SettlementDate field in the table.
--
Dave Hargis, Microsoft Access MVP


:

Thank you very much Klatuu. I have no idea how to use this VBA code.
I
don't know where to put it or what each line means in relation to my
data.
Can you break this down just a little bit more for me? The furthest I
got
was adding the holiday table and then everything just went blank for
me.
I've never studied this part of access that indepthly before. Please
help,
I'm 80% done with this project and I would like to finish this as soon
as I
can.

:

Okay, no problem. You will need a holiday table. Here is a function
that
returns a date in the future or past that excludes Saturdays,
Sundays, and
any date in the holiday table. Put it in a standard module and
change the
control source of your code:

=AddWorkDays(txtSettlemetDate, 3)

Note, if you have a holiday table, change the table and field name in
this
code. If not, you can create one using any names you like as long as
they
match. My table has an additional field to store the name of the
holiday,
but that isn't important for this function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of
days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from
to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of
working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The
table
should
' : Contain one row for each holiday. As written, the
table is
named
' : tblHolidays and the field used is a Date field named
Holidate
' : No time vaule should be in the Holidate field. Time
values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long)
As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays",
"[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Absolutely not. I would not want to store this in a table. Thanks
for the
formula. Will the 3 days include weekends and holidays as well? I
would
need it to just show business days.

:

Do you mean a control on a form that would be 3 days greater than
the
settlement date stored in the table? If so, create a text box on
your form
and use the Control Source property to calculate that:

=DateAdd("d", 3, txtSettlementDate)

Certianly you don't mean add a field to your table to store a
calculated
value. I am really sure you don't want to violate database
normalization
rules by storing a calculated value.
--
Dave Hargis, Microsoft Access MVP


:

Hey guys,

I'm creating a query that needs to be run off of Trade Date.
However, all
of my data is put into the database under Settlement date
(Trade Date + 3
days). How can I tell the database to add 3 days to all of
the items that
populate, thus creating another field called Trade Date?

 

[Andre Adams]

Thank you for the correction Douglas, that works great!

Unfortunately, you fell victim of wordwrap in what Dave posted (some of his
lines of code appeared as two lines in his post).

Here's a version that shouldn't have that problem:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number
' of days after or before a given date.
' Arguments : OriginalDate The date you want to add or subtract
' from to determine a worday
' : DaysToAdd Either a positive or negative number of
' working days
' : Saturdays and Sundays are not counted
' Holidays can also be omitted using a holiday table.
' The table should contain one row for each holiday.
' As written, the table is named tblHolidays and the field
' used is a Date field named Holidate
' No time vaule should be in the Holidate field.
' Time values may produce incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays( _
OriginalDate As Date, _
DaysToAdd As Long _
) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays", "[holidate] = #" & _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:

On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in function AddWorkDays"
GoTo AddWorkDays_Exit

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hmph. I never used the module tab before. I did everything you told me
to
do and everything seemed great until it told me that the VB Editor had a
syntax error in it. What do I do now?

You bet.
First, copy and paste the code into a standard module. That would be:
Choose the modules pane in the database window
Click new to create a new module - The VB editor (VBE) will open
Paste the code into the blank module.
Save it and give it a name, but the name cannot be the same as the
function
name.

Now, to use it in your query, open the query builder. You will need to
create a calculated field. That is a field name that doesn't exist in
the
table. Then you call the function using the date you want to add the 3
days
to. So in the field name row it would look something like:

TradeDate: AddWorkDays([SettlementDate], 3)

So your query will then have a field name TradeDate that will be 3
working
days after the SettlementDate field in the table.
--
Dave Hargis, Microsoft Access MVP


:

Thank you very much Klatuu. I have no idea how to use this VBA code.
I
don't know where to put it or what each line means in relation to my
data.
Can you break this down just a little bit more for me? The furthest I
got
was adding the holiday table and then everything just went blank for
me.
I've never studied this part of access that indepthly before. Please
help,
I'm 80% done with this project and I would like to finish this as soon
as I
can.

:

Okay, no problem. You will need a holiday table. Here is a function
that
returns a date in the future or past that excludes Saturdays,
Sundays, and
any date in the holiday table. Put it in a standard module and
change the
control source of your code:

=AddWorkDays(txtSettlemetDate, 3)

Note, if you have a holiday table, change the table and field name in
this
code. If not, you can create one using any names you like as long as
they
match. My table has an additional field to store the name of the
holiday,
but that isn't important for this function:

'---------------------------------------------------------------------------------------
' Procedure : AddWorkDays
' DateTime : 5/8/2006 14:24
' Author : Dave Hargis
' Purpose : Determines the date of a work day (mon-fri) a number of
days
after or
' : before a given date.
' Arguments : OriginalDate The date you want to add or subtract from
to
determine a
' : worday
' : DaysToAdd Either a positive or negative number of
working
days
' : Saturdays and Sundays are not counted
' : Holidays can also be omitted using a holiday table. The
table
should
' : Contain one row for each holiday. As written, the
table is
named
' : tblHolidays and the field used is a Date field named
Holidate
' : No time vaule should be in the Holidate field. Time
values
may produce
' : Incorrect Results
'---------------------------------------------------------------------------------------
'
Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long)
As Date
Dim lngAdd As Long
Dim lngDayCount As Long
Dim lngHolidayCount As Long

On Error GoTo AddWorkDays_Error

If DaysToAdd < 0 Then
lngAdd = -1
Else
lngAdd = 1
End If

AddWorkDays = OriginalDate

Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
If IsNull(DLookup("[holidate]", "tblHolidays",
"[holidate] = #"
& _
AddWorkDays & "#")) Then
lngDayCount = lngDayCount + lngAdd
End If
End If
Loop

AddWorkDays_Exit:
On Error GoTo 0

Exit Function

AddWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddWorkDays of Module modDateFunctions"
GoTo AddWorkDays_Exit

End Function

--
Dave Hargis, Microsoft Access MVP


:

Absolutely not. I would not want to store this in a table. Thanks
for the
formula. Will the 3 days include weekends and holidays as well? I
would
need it to just show business days.

:

Do you mean a control on a form that would be 3 days greater than
the
settlement date stored in the table? If so, create a text box on
your form
and use the Control Source property to calculate that:

=DateAdd("d", 3, txtSettlementDate)

Certianly you don't mean add a field to your table to store a
calculated
value. I am really sure you don't want to violate database
normalization
rules by storing a calculated value.
--
Dave Hargis, Microsoft Access MVP


:

Hey guys,

I'm creating a query that needs to be run off of Trade Date.
However, all
of my data is put into the database under Settlement date
(Trade Date + 3
days). How can I tell the database to add 3 days to all of
the items that
populate, thus creating another field called Trade Date?

 

[robert scholz]

Hey guys,

I'm creating a query that needs to be run off of Trade Date. However, all
of my data is put into the database under Settlement date (Trade Date + 3
days). How can I tell the database to add 3 days to all of the items
that
populate, thus creating another field called Trade Date?