Treating Public Holidays as Sundays to insert Data into Table

[rosieb]

I’m trying to insert data into a table based on a date range given in two unbound text boxes, the data varies dependent on the day of the week. All well and good, I can manage that.

BUT, if the day is a public holiday I want to treat it as a Sunday. I have a separate table of public holidays

I can’t see how to incorporate this, can anyone point me in the right direction?

So far I have

Dim int1 As Integer
Dim i As Integer
Dim strSQL As String
Dim strDay As String
Dim strDate As Date


int1 = DateDiff("d", StartDate, EndDate)


For i = 0 To int1

strDate = [StartDate] + i

strDay = Format(strDate, "w")


If strDay = 7 Then
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '6');"
    DoCmd.RunSQL strSQL
    
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '7');"
    DoCmd.RunSQL strSQL
    
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '8');"
    DoCmd.RunSQL strSQL
    
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '9');"
    DoCmd.RunSQL strSQL
    
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '10');"
    DoCmd.RunSQL strSQL
    
Else

    If strDay <> 1 Then

        DoCmd.SetWarnings False
        strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '1');"
        DoCmd.RunSQL strSQL
    
        DoCmd.SetWarnings False
        strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '2');"
        DoCmd.RunSQL strSQL
    
        DoCmd.SetWarnings False
        strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '3');"
        DoCmd.RunSQL strSQL
        
        DoCmd.SetWarnings False
        strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '4');"
        DoCmd.RunSQL strSQL
    
        DoCmd.SetWarnings False
        strSQL = "INSERT INTO Periods (PerDate, AM_PM) VALUES (#" & Format(DateAdd("d", i, StartDate), "yyyy-mm-dd") & "#, '5');"
        DoCmd.RunSQL strSQL
        
    End If
    
End If

Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[PHV]

Take a look at the DLookUp function.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rosieb]

PHV
Thanks, just what I needed.

Much appreciated.


Rosie
"Never express yourself more clearly than you think" (Niels Bohr)

 

[MichaelRed]

For a somewhat different approach, see faq181-261. With some minor modification, it should do what you want.





MichaelRed
mlred@verizon.net

 

[rosieb]

MichaelRed
Thanks for that, also much appreciated.


Rosie
"Never express yourself more clearly than you think" (Niels Bohr)