Hi Preston,
Ther are no stupid questions, only stupid answers!
(Hopefully this reply does not fall into that category.) :-)
The beauty of most functions are that you can use them almost anywhere, and
you can even "wrap" one function inside of another ... as I have here, in
the code below.
I wasn't happy with having to enter a cryptic string of dates MANUALLY as
per the Access Web example that I posted previously, so I built another
seperate function to supply the dates string for me. (Lazy or what, eh?)
Copy and Paste this code into the same module as the other "Access Web"
functions.
Examples of how it can be used are also in the coments section of the code.
My advice, (based on the advice of others that I have learned from in this
NG) is to NOT store any calculated values. Use the function throughout your
application to calculate and display the results for you.
Public Function fGetHolidates(NumDays As Long, Optional StartDate As
Variant) As String
'*******************************************
'Name: fGetHolidates (Function)
'Purpose: Intended to be used with "dhAddWorkDaysA" function from
'
http://www.mvps.org/access/datetime/date0012.htm
' to supply the dates string, but it could be used stand-alone as
well
' (see example in the "output" below.)
'
'Author: Don Leverton
'Date: July 01, 2004, 10:26:32 AM
'Called by:
'Calls:
'Inputs:[NumDays],[CurrDate]
'Output:#7/1/2004#, #8/2/2004#, #9/6/2004#, #10/11/2004#
'Notes: requires the creation of a small table named
'"tblHolidates" which contains Holiday Dates for the current year.
'This example also relies on a table named "tblWorkDays", which
'contains the fields named "CurrDate", and "NumDays"
'Both of the examples shown here are showing this function wrapped
'within the "dhAddWorkDaysA()" function
'Query Usage Example:
'SELECT tblWorkDays.CurrDate, tblWorkDays.NumDays,
'dhAddWorkDaysA([NumDays],[CurrDate],fGetHolidates([NumDays],[CurrDate])) AS
ResultDate
'FROM tblWorkDays;
'Form/Report Usage Example:
'Use
"=dhAddWorkDaysA([NumDays],[CurrDate],fGetHolidates([NumDays],[CurrDate]))"
' as the Control Source for an Unbound Text-box on a form which is bound to
tblWorkDays
'*******************************************
On Error GoTo ErrHandler
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst As DAO.Recordset
Dim EndDate As Date
Dim MySQL As String
Dim strHoliDates As String
'Get StartDate and EndDate
If Not IsDate(StartDate) Then
StartDate = Date
End If
EndDate = DateAdd("d", NumDays, StartDate)
MySQL = ""
MySQL = MySQL & "SELECT tblHolidates.Holidate "
MySQL = MySQL & "FROM tblHolidates "
MySQL = MySQL & "WHERE (((tblHolidates.Holidate) Between #"
MySQL = MySQL & StartDate
MySQL = MySQL & "# And #"
MySQL = MySQL & EndDate
MySQL = MySQL & "#));"
Set rst = MyDB.OpenRecordset(MySQL, dbOpenDynaset)
With rst
If Not .BOF Then
.MoveLast
.MoveFirst
Do Until .EOF
If Len(strHoliDates) > 0 Then
strHoliDates = strHoliDates & ", #" & !Holidate & "#"
Else
strHoliDates = "#" & !Holidate & "#"
End If
If Not .EOF Then
.MoveNext
End If
Loop
Else
Exit Function
End If
..Close
End With
Set rst = Nothing
Set MyDB = Nothing
fGetHolidates = strHoliDates
ExitHere:
Exit Function
ErrHandler:
Dim strErrString As String
strErrString = "Error Information..." & vbCrLf
strErrString = strErrString & "Error#: " & Err.Number
strErrString = strErrString & "Description: " & Err.Description
MsgBox strErrString, vbCritical + vbOKOnly, "Function: fGetHolidates"
Resume ExitHere
End Function
*******************************************
"Pres Holod" <ho***@optonline.net> wrote in message
news:40**********************@news.newsgroups.ws.. .
Don, you have been very helpful with the time you have spent with me.
However, I am still confused (being a hands on sort of learner), I don't
know where to put this function. Does it go in the code? and if so, how
does it get called to work? I assume it can be used in a form. I do
apologize for my stupidity but everything I have learned, I have learned
on my own.
Preston Holod
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!