# Thread: Block if without End if (2000)

1. ## 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
Case Is = 7 ' Saturday
Case Else
noDays = noDays + 1
End If
End Select
Next Day

NumSickDays = noDays

Endthis:

End Function</font color=blue>

2. ## 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
Case Is = 7 ' Saturday
Case Else
noDays = noDays + 1
End Select
End If

BTW, shouldn't that be

If Day >= Start And Day <= Finish Then

3. ## 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. ## 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. ## 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. ## 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
Case Else
noDays = noDays + 1
End Select
End If
Next Day

7. ## 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
•