DLookup Query

[JC3]

Hi

I wonder if I anyone can help me. I have a number of queries to calculate the Date a call is due excluding weekends.

This works by

1. Adding three days to Date.
2. If Day is Saturday or Sunday then add extra days.
3. If End Date is Saturday or Sunday then add extra days.

While this works fine I have ended up with about four queries with extremely complicated Expressions in them to do the calculations. I wanted to simplify this by using a Function. After lots of browsing of this and other sites. I have compiled a rough draft of the first stage. I have not added any of the functions for the weekend as yet. I decided to test this against a query. The SQL for the query is as follows.

SELECT tblCatalogueSales.ID, tblCatalogueSales.SAPDate, CalcCCallDate() AS CCallDueDate
FROM tblCatalogueSales
WHERE (((tblCatalogueSales.SAPDate) Is Not Null));

and my Function is this.

Dim dtStart As Date
Dim dtCurr As Date
Dim noDays As Double
Dim i As Integer
Dim ID As Long

ID = DLookup("ID", "qryTest")
i = 0
noDays = 3
dtStart = Nz(DLookup("SAPDate", "qryTest", "[ID]=" & [ID]))
dtCurr = dtStart

If Not IsNull(dtCurr) Then
Do While i < noDays
If Weekday(dtCurr, vbMonday) <= 5 Then
i = i + 1
End If
dtCurr = dtCurr + 1
Loop
End If

CalcCCallDate = dtCurr

This simply adds the three days plus weekends on to the start date. But when I run the query it only calculates the End Date on the first Date on the query and puts the same date on all the subsequent dates. I am sure that it is something simple I am doing but for the life of me I can't see where I am going wrong.

Can anyone help?

 

[RuralGuy]

Here's some more generic code you can use:

Public Function AdjWorkDays(dteStart As Date, _
                            intNumDays As Long, _
                            Optional blnAdd As Boolean = True) As Date
AdjWorkDays = dteStart
Do While intNumDays > 0
   If blnAdd Then
      '-- Adding WorkDays
      AdjWorkDays = AdjWorkDays + 1
   Else
      '-- Subtracting WorkDays
      AdjWorkDays = AdjWorkDays - 1
   End If
   If Weekday(AdjWorkDays, vbMonday) <= 5 Then
[COLOR=Red]'-- Use the following code if you have a "Holiday" table
'   If Weekday(dteCurrDate, vbMonday) <= 5 And IsNull(DLookup("[Holiday]", "tblHolidays", "[HolDate] = #" & dteCurrDate & "#")) Then[/COLOR]
       intNumDays = intNumDays - 1
   End If
Loop
End Function

And using my WorkDay code I would think your query would look more like:

SELECT tblCatalogueSales.ID, tblCatalogueSales.SAPDate, _
       AdjWorkDays(SAPDate, 3, True) AS CCallDueDate _
FROM tblCatalogueSales _
WHERE (((tblCatalogueSales.SAPDate) Is Not Null));

 

[JC3]

Thanks Rural Guy. Thats a lot simpler and I have learned something knew. All in all a great response. Thanks much.

 

[RuralGuy]

You're welcome. Glad I could help.