Function from SQL to VBA Module in Access

[lurch279]

Hey Folks!

I'm hoping you can help me out. I've spent a few hours on this and I
just do not have the knowledge to complete this. It is a simple
snippet from SQL that I need to translate to VBA in Access give me
workdays based upon a Query field plus 2 days...if the 2 days falls on
Saturday, then go to Monday or if Monday is a holiday...etc... so in
the query I would enter the field as:

New Work Day: Dateaddworkday([dtmSchedDate],2) --> The '2' is to add
two days to the Scheduled Date.

I think the problem is with the "SET" in VBA but I just don' know
enough to get past it...Any Help you folks would give would be MOST
appreciated...I have placed my SQL and VBA Code here...sorry if this
seems a bit long...


Thanks!

Lurch

Here is the SQL.....VBA to follow below it.

CREATE FUNCTION dbo.DATEADDWORKDAY_ACCESS(@dtmStartDate datetime,
@numDayCount int)
RETURNS datetime AS
BEGIN

DECLARE @numDayOfWeek int

WHILE (@numDayCount > 0) BEGIN
SET @dtmStartDate = DATEADD(dd, 1, @dtmStartDate)

SET @numDayOfWeek = DATEPART(dw,@dtmStartDate)
IF (@numDayOfWeek <> 1) AND (@numDayOfWeek <> 7) BEGIN

IF (@dtmStartDate not in (select dtmHoliday from
dbo.tblHolidayList)) BEGIN
SET @numDayCount = @numDayCount - 1
END
END
END

RETURN @dtmStartDate

END

Here is what I have with VBA

Public Function DateAddWorkday(dtmStartDate As Date, numDayCount As
Integer)

Dim numDayofWeek As Integer


While numDayCount > 0
Set dtmStartDate = DateAdd("d", 1, dtmStartDate)
Set numDayofWeek = DatePart("w", dtmStartDate)
If (numDayofWeek <> 1) And (numDayofWeek <> 7) Then
If (dtmStartDate <> tblHolidaylist.dtmHoliday) Then
Set numDayCount = numDayCount - 1

End If
End If

Wend

End Function

 

[George Nicholson]

- remove the Sets (in VBA, Set is only used to assign something to an
Objects data types, which is not occuring here)
- Deal with the tblHoliday lookup (verify table name, field name and that
field is a Date field)
- Restrucure for VBA

(Air code)
Public Function DateAddWorkday(dtmStartDate As Date, numDayCount As Integer)

Dim numDayofWeek As Integer
Dim dtmTemp as Date

dtmTemp = DateAdd("d", numDayCount, dtmStartDate)
numDayofWeek = DatePart("w", dtmTemp)

Select Case numDayofWeek
Case 1
'Sunday. Add one day
dtmTemp = DateAdd("d", 1, dtmTemp)
Case 7
'Saturday. Add two days
dtmTemp = DateAdd("d", 2, dtmTemp)
Case Else
'Do Nothing
End Select

Do While Dcount("[dtmHoliday]","tblHolidayList","[dtmHoliday] = #" & dtmTemp
& "#") > 0
'Its in the Holiday list. Add a day & try again
dtmTemp = DateAdd("d", 1, dtmTemp)
Loop

DateAddWorkday = dtmTemp

End Function

HTH,

 

[Lurch279]

- remove the Sets (in VBA, Set is only used to assign something to an
Objects data types, which is not occuring here)
- Deal with the tblHoliday lookup (verify table name, field name and that
field is a Date field)
- Restrucure for VBA

(Air code)
Public Function DateAddWorkday(dtmStartDate As Date, numDayCount As Integer)

Dim numDayofWeek As Integer
Dim dtmTemp as Date

dtmTemp = DateAdd("d", numDayCount, dtmStartDate)
numDayofWeek = DatePart("w", dtmTemp)

Select Case numDayofWeek
Case 1
'Sunday. Add one day
dtmTemp = DateAdd("d", 1, dtmTemp)
Case 7
'Saturday. Add two days
dtmTemp = DateAdd("d", 2, dtmTemp)
Case Else
'Do Nothing
End Select

Do While Dcount("[dtmHoliday]","tblHolidayList","[dtmHoliday] = #" & dtmTemp
& "#") > 0
'Its in the Holiday list. Add a day & try again
dtmTemp = DateAdd("d", 1, dtmTemp)
Loop

DateAddWorkday = dtmTemp

End Function

HTH,

Hey Folks!

I'm hoping you can help me out. I've spent a few hours on this and I
just do not have the knowledge to complete this. It is a simple
snippet from SQL that I need to translate to VBA in Access give me
workdays based upon a Query field plus 2 days...if the 2 days falls on
Saturday, then go to Monday or if Monday is a holiday...etc... so in
the query I would enter the field as:

New Work Day: Dateaddworkday([dtmSchedDate],2) --> The '2' is to add
two days to the Scheduled Date.

I think the problem is with the "SET" in VBA but I just don' know
enough to get past it...Any Help you folks would give would be MOST
appreciated...I have placed my SQL and VBA Code here...sorry if this
seems a bit long...


Here is the SQL.....VBA to follow below it.

CREATE FUNCTION dbo.DATEADDWORKDAY_ACCESS(@dtmStartDate datetime,
@numDayCount int)
RETURNS datetime AS
BEGIN

DECLARE @numDayOfWeek int

WHILE (@numDayCount > 0) BEGIN
SET @dtmStartDate = DATEADD(dd, 1, @dtmStartDate)

SET @numDayOfWeek = DATEPART(dw,@dtmStartDate)
IF (@numDayOfWeek <> 1) AND (@numDayOfWeek <> 7) BEGIN

IF (@dtmStartDate not in (select dtmHoliday from
dbo.tblHolidayList)) BEGIN
SET @numDayCount = @numDayCount - 1
END
END
END

RETURN @dtmStartDate

Here is what I have with VBA

Public Function DateAddWorkday(dtmStartDate As Date, numDayCount As
Integer)

Dim numDayofWeek As Integer

While numDayCount > 0
Set dtmStartDate = DateAdd("d", 1, dtmStartDate)
Set numDayofWeek = DatePart("w", dtmStartDate)
If (numDayofWeek <> 1) And (numDayofWeek <> 7) Then
If (dtmStartDate <> tblHolidaylist.dtmHoliday) Then
Set numDayCount = numDayCount - 1

End If
End If

End Function- Hide quoted text -

- Show quoted text -

George!!!

Wow wow wow WOWOWOW!!!

Thank you SO SO much!! This works to a "T"....

Take care and wow - I owe ya!

Lurch

 

[George Nicholson]

Note that Friday Holidays would cause the existing logic to fail by
returning a Saturday. Adding a Saturday test *within* the Holiday loop (so
we can check the Monday for Holiday status as well) should cover that:

Do While Dcount("[dtmHoliday]","tblHolidayList","[dtmHoliday] = #" & dtmTemp
& "#") > 0
'Its in the Holiday list. Add one day & try again
dtmTemp = DateAdd("d", 1, dtmTemp)
If DatePart("w", dtmTemp) = 7 then
' Oops. We've changed it to a Saturday. Lets change it to Monday,
then test again.
dtmTemp = DateAdd("d", 2, dtmTemp)
End if
Loop

- remove the Sets (in VBA, Set is only used to assign something to an
Objects data types, which is not occuring here)
- Deal with the tblHoliday lookup (verify table name, field name and that
field is a Date field)
- Restrucure for VBA

(Air code)
Public Function DateAddWorkday(dtmStartDate As Date, numDayCount As
Integer)

Dim numDayofWeek As Integer
Dim dtmTemp as Date

dtmTemp = DateAdd("d", numDayCount, dtmStartDate)
numDayofWeek = DatePart("w", dtmTemp)

Select Case numDayofWeek
Case 1
'Sunday. Add one day
dtmTemp = DateAdd("d", 1, dtmTemp)
Case 7
'Saturday. Add two days
dtmTemp = DateAdd("d", 2, dtmTemp)
Case Else
'Do Nothing
End Select

Do While Dcount("[dtmHoliday]","tblHolidayList","[dtmHoliday] = #" &
dtmTemp
& "#") > 0
'Its in the Holiday list. Add a day & try again
dtmTemp = DateAdd("d", 1, dtmTemp)
Loop

DateAddWorkday = dtmTemp

End Function

HTH,

Hey Folks!

I'm hoping you can help me out. I've spent a few hours on this and I
just do not have the knowledge to complete this. It is a simple
snippet from SQL that I need to translate to VBA in Access give me
workdays based upon a Query field plus 2 days...if the 2 days falls on
Saturday, then go to Monday or if Monday is a holiday...etc... so in
the query I would enter the field as:

New Work Day: Dateaddworkday([dtmSchedDate],2) --> The '2' is to add
two days to the Scheduled Date.

I think the problem is with the "SET" in VBA but I just don' know
enough to get past it...Any Help you folks would give would be MOST
appreciated...I have placed my SQL and VBA Code here...sorry if this
seems a bit long...


Here is the SQL.....VBA to follow below it.

CREATE FUNCTION dbo.DATEADDWORKDAY_ACCESS(@dtmStartDate datetime,
@numDayCount int)
RETURNS datetime AS
BEGIN

DECLARE @numDayOfWeek int

WHILE (@numDayCount > 0) BEGIN
SET @dtmStartDate = DATEADD(dd, 1, @dtmStartDate)

SET @numDayOfWeek = DATEPART(dw,@dtmStartDate)
IF (@numDayOfWeek <> 1) AND (@numDayOfWeek <> 7) BEGIN

IF (@dtmStartDate not in (select dtmHoliday from
dbo.tblHolidayList)) BEGIN
SET @numDayCount = @numDayCount - 1
END
END
END

RETURN @dtmStartDate

Here is what I have with VBA

Public Function DateAddWorkday(dtmStartDate As Date, numDayCount As
Integer)

Dim numDayofWeek As Integer

While numDayCount > 0
Set dtmStartDate = DateAdd("d", 1, dtmStartDate)
Set numDayofWeek = DatePart("w", dtmStartDate)
If (numDayofWeek <> 1) And (numDayofWeek <> 7) Then
If (dtmStartDate <> tblHolidaylist.dtmHoliday) Then
Set numDayCount = numDayCount - 1

End If
End If

End Function- Hide quoted text -

- Show quoted text -

George!!!

Wow wow wow WOWOWOW!!!

Thank you SO SO much!! This works to a "T"....

Take care and wow - I owe ya!

Lurch