Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Block if without End if (2000)

    Ok,ok call me a n00b, but i can't see why i should get this error, it all makes perfect sense to me !

    Pulling out of the first IF statement as i am, will that work? (Its REM'd out at the moment)
    Can i loop using dates as i'm trying to ?


    <font color=blue>Public Function NumSickDays(Start As Variant, Finish As Variant) As Integer

    Dim RangeStart As Date, RangeFinish As Date
    Dim noDays As Integer
    Dim Day As Variant
    RangeStart = CLng(Forms!Switchboard!Begin_Date)
    RangeFinish = CLng(Forms!Switchboard!Finish_Date)
    noDays = 0

    'If Finish < RangeStart Or Start > RangeFinish Then
    ' NumSickDays = 0
    ' Stop
    ' GoTo Endthis
    'End If

    For Day = RangeStart To RangeFinish

    If Day >= Start Or Day <= Finish Then
    Select Case (Weekday(Day))
    Case Is = 1 ' Sunday
    'nada
    Case Is = 7 ' Saturday
    'nada
    Case Else
    noDays = noDays + 1
    End If
    End Select
    Next Day

    NumSickDays = noDays

    Endthis:

    End Function</font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Block if without End if (2000)

    Blocks should be completely nested within other blocks. You should place End Select before End If:

    If Day >= Start Or Day <= Finish Then
    Select Case (WeekDay(Day))
    Case Is = 1 ' Sunday
    'nada
    Case Is = 7 ' Saturday
    'nada
    Case Else
    noDays = noDays + 1
    End Select
    End If


    BTW, shouldn't that be

    If Day >= Start And Day <= Finish Then

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Block if without End if (2000)

    ok will give that a whirl Hans,...

    with regard to the
    <font color=blue>If Finish < RangeStart Or Start > RangeFinish Then
    NumSickDays = 0
    GoTo Endthis
    End If</font color=blue>

    What im doing is counting up the number of sick days that exist between a set of dates. eg:

    Range to look for, = 01/01/2002 (RangeStart) to 31/12/2002 (RangeFinish) ---- Ie: all of 2002

    Sickness period of time is passed from the query as two range dates, to speed things up, i figured if i threw this code in at the start i could
    eliminate the need to do anything. that logic being...

    1) If the last day of sickness (Finish) is before our first range date, (RangeStart) we don't need to do anything. (eg: 10/10/2001)
    2) or, if the first day of sickness (Start) is greater than our last day we are looking over (RangeFinish) again, we don't need to do anything and can exit the function... (eg: 01/01/2003)

    I just wasn't sure of the best way to exit the function. and return 0.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Block if without End if (2000)

    Steve,

    Your idea for avoiding the loop if the dates are completely outside the range looks good to me. To get out of the function, you can use GoTo EndThis (as you have now), or Exit Function. Don't use Stop in production code; it equivalent to placing a breakpoint.

    Strictly speaking, you don't have to use NumSickDays = 0. You declared the return value of NumSickDays to be an Integer. If you never assing it a value, it will return the default value 0. However, explicitly setting NumSickDays = 0 makes the code more self-documenting and easier to understand.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Block if without End if (2000)

    There is something a bit dodgy with the main loop though, its returning 261 all the time. ie: its counting every possible non weekend day.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Block if without End if (2000)

    Steve,

    My question whether you shouldn't have If Day >= Start And Day <= Finish Then was not rethorical. If you use Or, the condition is always True.

    Another point: your code loops through all days of the year (in your example, RangeStart and RangeEnd defined all of 2002). Assuming that people are sick less than a whole year on average, it is more efficient to loop through the period they were sick. This means switching the roles of Start and RangeStart, and the roles of Finish and RangeFinish. Together with the And instead of Or, the loop then becomes:

    For Day = Start To Finish
    If Day >= RangeStart And Day <= RangeFinish Then
    Select Case (WeekDay(Day))
    Case 1, 7 ' Sunday or Saturday
    'nada
    Case Else
    noDays = noDays + 1
    End Select
    End If
    Next Day

  7. #7
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Block if without End if (2000)

    As always Hans, you are correct.

    I did change the code straight after and realised what was going wrong. ahh well better later than never <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •