The old date problem.

[TOPSie]

Please forgive me I am feeling a bit fragile. I "improved" a bit of code mid August and went away on holiday on the 31st. Next day and irate customer is texting as his code no longer works.

Here is the scenario:
The code loads and calculates the next working day and populates a Text box value with Now or Now + 1 (on a Sunday) or Now + 2 (on a Saturday)

I then construct a search (SQL) to include :- WHERE (DeliveryDate= #" & Int(txtNextDeliveryDay.Value) & "#)

Which worked fine in testing in mid August, but come the 1st of September the WHERE clause starts comparing with 9th January



My deputy saved the day, and my holiday with
DeliveryDate >= DateSerial(" & Year(d) & "," & Month(d) & "," & Day(d) & ") " & _
" AND DeliveryDate < DateSerial(" & Year(dNext) & "," & Month(dNext) & "," & Day(dNext) & ");

But I was just wondering if there was a more elegant (and shorter) solution

[Minty]

If you always format your dates using the YYYY-MM-DD format you will never go wrong.
It's completely unambiguous, and apart from the separator (' instead of # ) is the same as the format SQL server uses.
It's clean and easy to see when you debug.print as well.

Use a function :

Function SqlDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Based around function from Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SqlDate = Format$(varDate, "\#yyyy-mm-dd\#")
        Else
            SqlDate = Format$(varDate, "\#yyyy-mm-dd hh:nn:ss\#")
        End If
    End If
End Function
Public Function Check

Then whenever you are building a SQL string simply use SqlDate(Me.MydateField)

So your original would be
WHERE (DeliveryDate= " & SqlDate(Me.txtNextDeliveryDay) & ")"

[jojowhite]

how many Days do you count for the Next Delivery Date?
Supposed you have a OrderDate (txtOrderDate) and your Next Delivery Date is computed as 2 Days after the OrderDate.
You can create a function for this, ie:

Public Function fnNextDeliveryDate(ByVal dteStart As Date, ByVal intNumberOfDays As Integer, Optional ByVal strHolidayTable As String = "", Optional ByVal HolidayField As String = "") As Date
    Dim strDay As String
    Dim retDate As Date
    retDate = dteStart + intNumberOfDays
    Do While True
        strDay = Format$(retDate, "ddd")
        If InStr("/Sat/Sun/", "/" & strDay & "/") = 0 Then
            ' retDate is OK but need to check if it holiday
            ' Check your Holiday table if holiday on this day
            If Len(strHolidayTable) <> 0 Then
                If DCount("1", strHolidayTable, HolidayField & " = #" & Format$(retDate, "m/d/yyyy") & "#") = 0 Then
                    ' If No holiday the Exit our loop
                    Exit Do
                End If
            Else
                Exit Do
            End If
        End If
        ' increment the retDate
        retDate = retDate + 1
    Loop
    fnNextDeliveryDate = retDate
End Function

you assign this on your txtNextDeliveryDay (no holiday table):

Me.txtNextDeliveryDay =

fnNextDeliveryDate(Me.txtOrderDate, 2)   ' if no holiday table

with holiday table:

Me.txtNextDeliveryDay = fnNextDeliveryDate(Me.txtOrderDate, 2, "tblHoliday", "DateFieldName") 

[Welshgasman]

I would just use format()

[Gustav]

First, always handle dates as Date, not strings, not numbers, no exceptions. Jojo's code will fail in any non-English environment.

Next, you can use my function DateNextWorkday found in module DateWork: VBA.Date/DateWork.bas at master · GustavBrock/VBA.Date

Full code here: https://github.com/GustavBrock/VBA.Date

[Ken Sheridan]

For this sort of thing I would use an auxiliary calendar table, which can be created with the following function:

Public Function MakeCalendar_DAO(ByVal strTable As String, _
                            ByVal dtmStart As Date, _
                            ByVal dtmEnd As Date, _
                            ParamArray varDays() As Variant)
                            
    ' Accepts:  Name of calendar table to be created: String.
    '           Start date for calendar: DateTime.
    '           End date for calendar: DateTime.
    '           Days of week to be included in calendar
    '           as value list, e,g 2,3,4,5,6 for Mon-Fri
    '           (use 0 to include all days of week)

    Dim dbs As DAO.Database, tdf As DAO.TableDef
    Dim strSQL As String
    Dim dtmDate As Date
    Dim varDay As Variant
    Dim intDayIndex As Integer
    
    Set dbs = CurrentDb
    
    ' does table exist? If so get user confirmation to delete it
    On Error Resume Next
    Set tdf = dbs.TableDefs(strTable)
    If Err = 0 Then
        If MsgBox("Replace existing table: " & _
            strTable & "?", vbYesNo + vbQuestion, _
            "Delete Table?") = vbYes Then
            strSQL = "DROP TABLE " & strTable
            dbs.Execute strSQL, dbFailOnError
        ElseIf MsgBox("Append rows to existing table: " & _
            strTable & "?", vbYesNo + vbQuestion, _
            "Append to Table?") = vbYes Then
            GoTo AppendRows
        Else
            Exit Function
        End If
    End If
    On Error GoTo 0
    
    ' create new table
    strSQL = "CREATE TABLE " & strTable & _
        "(calDate DATETIME, DayIndex SMALLINT, " & _
        "CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
    dbs.Execute strSQL, dbFailOnError
    
    ' refresh database window
    Application.RefreshDatabaseWindow
       
AppendRows:
    If varDays(0) = 0 Then
        ' fill table with all dates
        For dtmDate = dtmStart To dtmEnd
            intDayIndex = intDayIndex + 11
            strSQL = "INSERT INTO " & strTable & "(calDate,dayIndex) " & _
               "VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#," & intDayIndex & ")"
               
            dbs.Execute strSQL, dbFailOnError
        Next dtmDate
    Else
        ' fill table with dates of selected days of week and non holiday dates only
        For dtmDate = dtmStart To dtmEnd
            ' exclude public or discretionary holidays
            If IsNull(DLookup("holDate", "PubHols", "holDate =#" & Format(dtmDate, "yyyy-mm-dd") & "#")) Then
                For Each varDay In varDays()
                    If Not IsNull(varDay) Then
                        If Weekday(dtmDate) = varDay Then
                            intDayIndex = intDayIndex + 1
                            strSQL = "INSERT INTO " & strTable & "(calDate,dayIndex) " & _
                                "VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#," & intDayIndex & ")"
                            dbs.Execute strSQL
                        End If
                    End If
                Next varDay
            End If
        Next dtmDate
    End If
    
End Function

The code references a PubHols table with column holDate to exclude public holidays from the count. As public holidays are added to this table in the future the calendar table can be re-indexed with the following function:

Public Function ReindexCalendar(strCalendar As String)

    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim intDayIndex As Integer
    
    strSQL = "SELECT * FROM " & strCalendar & " ORDER By calDate"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
     
    With rst
        .MoveFirst
        Do While Not .EOF
            intDayIndex = intDayIndex + 1
            .Edit
            .Fields("dayIndex") = intDayIndex
            .Update
            .MoveNext
        Loop
    End With

End Function

To add working days to any date the following function can be called:

Public Function CalDateAdd(strCalendar As String, AddDays As Integer, Optional DateFrom) As Date

    ' Accepts:  Name of calendar table to be used: String.
    '           Number of days to add: Integer.
    '           Date to which days are to be added (Optional). Default = current date.

    Dim intDayIndexFrom As Integer

    If IsMissing(DateFrom) Then DateFrom = VBA.Date
    
    intDayIndexFrom = DMin("DayIndex", strCalendar, "calDate > #" & Format(DateFrom, "yyyy-mm-dd") & "#") - 1
    CalDateAdd = DLookup("CalDate", strCalendar, "dayIndex = " & intDayIndexFrom + AddDays)
    
End Function

[ArviLaanemets]

Sorry! I was assuming I was in Office forum at moment, and explained how to do this in Excel!
But generally, the same approach applies to Access or to Access back-end on SQL Server too! Only instead of formulas in columns you have to run procedures to fill calculated fields whenever expanding the calendar table.

************************************************** *************************
I also have used an auxiliary calendar table for such cases.

It is a separate workbook with calendar table where all dates, from some start one until some years into future one, are listed in leftmost column. And it can have any number of calculated columns (depending on what you need), e.g. indicating a workday (1 when workday, 0 otherwise), a holiday (1 when holiday, 0 otherwise), columns calculating the number of days until previous/next workday (0 when the date is workday, a number > 0 otherwise), etc. Generally, all calculated columns return a numeric values.
To make calculating holidays easier, this workbook can also have a table where conditions for various holidays (like start and end dates in case holydays were added/removed at some moment), and a table with list of all holidays for same time period as the one the calendar table covers.
The calendar table is a Defined Table, so whenever your expand the list of dates, all formulas in calculated columns are expanded automatically too.

Then you can either have a copy of this original table (all formulas replaced by values) for general use on some network resource in your LAN, and your Excel applications query it, or you can have a copy of it (all formulas replaced by values again) in any workbook you create, and which needs it.

In your case now, for every date you can simply read the number of days until previous or next workday from calendary table (using either VLOOKUP or SUMIFS), and distract from it from original date, or add it to it.