Not getting out of loop

[Gamezy]

I have the following code, and basically it doesn't seem to return the correct value but instead is just jumping to the end of the function.

Public Function WorkingDays2(pstartdte As Date, penddte As Date) As Integer

On Error GoTo Err_WorkingDays2

Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

'start loop
Do While pstartdte <= penddte

For i = 1 To rst.RecordCount Step 1
MsgBox rst("HolidayDate")
rst.MoveNext

'if holiday date = date looping add to intcount
If HolidayDate = pstartdte Then
intCount = intCount + 1
End If
Next i
'Add to the count
pstartdte = pstartdte + 1
Loop


'It doesn't do this bit!
MsgBox (intCount)
WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

It's probably something simple, but I'm still learning VB so I can't quite understand this.

 

[namliam]

Public Function WorkingDays2(pstartdte As Date, penddte As Date) As Integer
    
    On Error GoTo Err_WorkingDays2
    
    Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
    
    'start loop
    Do While pstartdte <= penddte
    
        For i = 1 To rst.RecordCount Step 1
            MsgBox [b][u]rst("HolidayDate")[/b][/u]
            rst.MoveNext
            
            'if holiday date = date looping add to intcount
            If [b][u]HolidayDate[/u][/b] = pstartdte Then
                intcount = intcount + 1
            End If
        Next i
        'Add to the count
        pstartdte = pstartdte + 1
    Loop
    
    
    'It doesn't do this bit!
    MsgBox (intcount)
    WorkingDays2 = intcount
    
Exit_WorkingDays2:
    Exit Function
    
Err_WorkingDays2:
    Select Case Err
    
        Case Else
            MsgBox Err.Description
            Resume Exit_WorkingDays2
    End Select

End Function

You probably dont have Option Exlicit on top of your module(s), check out the bolded 2 statements....

 

[Gamezy]

Uhmmm, what does Option Exlicit do exactly? I'm still learning, so it would be good to know. Thanks for response!

 

[workmad3]

Option Explicit forces you to declare all your variables, using Dim and so on. It can pick up on code mistakes because without it, you could mis type a variable name and it would just create a new variable and assign the value to it... with option explicit VBA will throw up an error because the variable isn't defined.

On a note related to your problem, see if adding the line

Dim intCount as Integer

at the top of your function helps, as I have a feeling that you have scoping problems (i.e you use intCount in an if that is in a for that is in a while without declaring it first in any of them). This basically means that intCount is not keeping its value outside of the if you are using it in

 

[namliam]

His problem is not related to the counter but rather to the not using of rst in the second bolded bit of text...

Good explenation of Explicit...

 

[Gamezy]

Oooohhhh, I see now. Thanks again guys, slowly getting better at this. Gonna be a long time before I hit your level though, I still keep trying to do things as if this were Java!

 

[workmad3]

Then why don't you declare your variables? I know java enforces this no matter what.