Manipulating dates (again but different) 1

[santastefano]

My problem is writing a loop within a loop (for holiday check) and resetting the date to date+7 after an adjustment for holidays
The first loop works perfectly

ElseIf varSessionsPerWeek = 1 Then
    varSessionsTotal = Forms!frmCourseStart!TotalSessions
    varCode = Forms!frmCourseStart!Code
    varStartDate = Forms!frmMultiSession!Date1
Do While varSessionsTotal > 0
   With rsTimeTable
      .AddNew   'Creates new record in the table
      !Code = varCode
      !StartDate = varStartDate
      !From = varTime1
      !To = varTime2
      .Update   'Confirms the record to the table.
   End With
    varSessionsTotal = varSessionsTotal - 1
    varStartDate = varStartDate + 7
Loop

The holiday table is a table of individual dates
My problem is the breakout to check for holidays and resetting the variable
I have been struggling with this for some time and I would be happy to pay any reasonable fee for a working solution AND PUBLISH THE SOLUTION HERE for any other users with a similar problem.

 

[davejam]

not entirely sure what you need to achieve, haven't seen the previous post, is it the actual start date messing up, may be worth using dateadd(d,7,varDate) function, if the format is coming through funning i think possible theres datevalue(varDate) to sort that out within the dateadd function

Not sure if this is where the problem is, but just in case

cheers

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[santastefano]

Thank you, I am going try your solution because it looks rather elegant but I don't think it solves the problem. I must be making some other stupid mistake.
The first loop is shown in the first post and works perfectly - posts one entry every 7 days.
The second loop within the loop looks like this.

    varSessionsTotal = varSessionsTotal - 1
    varStartDate = varStartDate + 7
        Dim rsHolidays As DAO.Recordset
        Set DB = CurrentDb
        Set rsHolidays = DB.OpenRecordset("tblHolidays", dbopendynaset)
        With rsHolidays
        rsHolidays.FindFirst "Close1 = #" _
        & Format(varStartDate, "mm-dd-yyyy") & "#"
            If NoMatch Then
            DoCmd.CancelEvent
            varStartDate = varStartDate
            Else
            varStartDate = varStartDate + 7
            rsHolidays.FindNext "Close1 = #" _
            & Format(varStartDate, "mm-dd-yyyy") & "#"
                If NoMatch Then
                DoCmd.CancelEvent
                varStartDate = varStartDate
                Else
                varStartDate = varStartDate + 7
                End If
            End If
        End With
Loop

Using 22///2006 as a start date and entering all of August as a holiday it should return
22/7, 29/7, 12/8 but actually returns 22/7, 12/8, 2/9 because the NoMatch line is not working - debugger goes directly to ELSE on the first pass even though there is no match.
Any suggestions gratefully received.

 

[JerryKlmns]

santastefano

You missed the dot before the NoMatch property of your recordset. And I can recall PHV, suggested elsewhere to use
#" & Format(varStartDate, "yyyy-mm-dd") & "#

 

[santastefano]

Thank you very much for spotting my careless error.
Of course you are correct about the date format but because I couldn't make the loop work I changed the format to that shown in the Acces help file.
Thanks again for taking the time to read through and correct the code.
Best regards for the weekend.

 

[santastefano]

A solution.
Perhpas not the best but anyway functions.

ElseIf varSessionsPerWeek = 1 Then
    varSessionsTotal = Forms!frmCourseStart!TotalSessions
    varCode = Forms!frmCourseStart!Code
    varStartDate = Forms!frmMultiSession!Date1
Do While varSessionsTotal > 0
   With rsTimetable
      .AddNew   'Creates new record in the table
      !Code = varCode
      !StartDate = varStartDate
      !From = varTime1
      !To = varTime2
      .Update   'Confirms the record to the table.
   End With
    varSessionsTotal = varSessionsTotal - 1
    varStartDate = varStartDate + 7
    varCount = 1
        Dim rsHolidays As DAO.Recordset
        'Dim strCriteria As Date
        Set DB = CurrentDb
        Set rsHolidays = DB.OpenRecordset("tblHolidays", dbopendynaset)
        With rsHolidays
            .FindFirst "Close1 = #" & Format(varStartDate, "yyyy-mm-dd") & "#"
            Do While Not .NoMatch
            varStartDate = varStartDate + 7
            .FindNext "Close1 = #" & Format(varStartDate, "yyyy-mm-dd") & "#"
            Loop
        End With
    rsHolidays.Close 'Closes the table
    Set rsHolidays = Nothing 'Sets the rsTimeTable Object to nothing.
    Set DB = Nothing
Loop
rsTimetable.Close 'Closes the table
Set rsTimetable = Nothing 'Sets the rsTimeTable Object to nothing.
Set DB = Nothing

Thanks to everybody for the help and advice (and patience) that I have found on this site.

 

[JerryKlmns]

santastefano

Set DB = Nothing

exists twice at the end. If rsTimetable uses it too, then you exterminate it (very brute). Kill the first, leave the second

 

[santastefano]

Jerry, I hope that another thank you from me will not be boring, I will take the risk.
THANK YOU!
George

 

[JerryKlmns]

George,

You are very welcomed.