Help with if then statement Please

[zishan619]

Hi:
I have the following code written:
Do Until rs.EOF
If strUID = rs("UID&quot Then

If Week1Start <= rs(&quot;dtsDate&quot <= Week1End Then
MakeDedupedArray Time_WeekA, rs(&quot;TimeCodeID&quot, rs(&quot;dblHours&quot

Debug.Print &quot;UID=&quot;; strUID
Debug.Print &quot;dtsDate=&quot;; rs(&quot;dtsDate&quot, &quot;TimeCode=&quot;; rs(&quot;TimeCodeID&quot, &quot;dblHours=&quot;; rs(&quot;dblHours&quot
Debug.Print &quot;rs=&quot;; rs(&quot;UID&quot

rs.MoveNext

ElseIf Week2Start <= rs(&quot;dtsDate&quot <= Week2End Then
Debug.Print &quot;Week2&quot;
MakeDedupedArray Time_WeekB, rs(&quot;TimeCodeID&quot, rs(&quot;dblHours&quot
rs.MoveNext

Else
Debug.Print &quot;Will do OT Calc&quot;
OT_Calc Tot
'Append
End If
Else
'restart for new UID
End If
Loop

rs.Close
Set rs = Nothing

This code works good for week1 but the code does not pust it out to week 2.
Also When the strUID changes to a different UID I need it to go through and do the same thing again until it gets to the end of the recordset.
Thanks
Z

 

[Kendel]

If Week1Start <= rs(&quot;dtsDate&quot <= Week1End Then

what are you trying to say here?

You need:

If Week1Start <= rs(&quot;dtsDate&quot AND Week1Start <= Week1End

OR:
If Week1Start <= rs(&quot;dtsDate&quot OR Week1Start <= Week1End

 

[zishan619]

Thanks Kendel But it didn't go to the second week. It will go through a week for an analyst with the same UID which represent an analyst. But it will not go to second week and also when the UID changes it stops the program but I want the program to go back and do the same function for the first analyst and so on.
The other function I have which calls this function goes something like this:
Do Until rs.EOF
Filter_Week rs(&quot;UID&quot, rs(&quot;Week1_Start&quot, rs(&quot;Week1_End&quot, rs(&quot;Week2_Start&quot, rs(&quot;Week2_End&quot
rs.MoveNext


Loop
This function is called Week and it starts from here and calls the previous function which is Fliter Week.
Thanks

 

[Kendel]

My Bad. I misunderstood your question.

 

[woodrasj]

I would try and set up so it shows explicitly that you want it less than the week1end
ie

If Week1Start <= rs(&quot;dtsDate&quot;) And Week1End >= _
       rs(&quot;dtsDate&quot;) Then

This may or may not do it but it would be worth a try.

 

[zishan619]

Hi: I tried that already it didn't work. Thanks

 

[woodrasj]

You are going to have to add cDate function in and then put the And in to make it work

tempDate = cdate(rs("dtsDate))
If cdate(Week1Start) <= tempDate and _
    cdate(Week1End) >= tempDate Then

This way it is sure that the dates are all in the same format.

 

[dilettante]

I agree with woodrasj and others.


The subexpression

rs("dtsDate")

is just shorthand (courtesy of ADO's default properties) for

rs.Fields.Item("dtsDate")

and as such returns an object of type &quot;Field.&quot;

This is easily seen via something like

MsgBox TypeName(rs("dtsDate"))

if you want to see for yourself.

What you really want here is

rs.Fields.Item("dtsDate").Value

instead. If you try

MsgBox TypeName(rs.Fields.Item("dtsDate").Value)

you will see that this object is type &quot;Date.&quot;

You can get away with the shorthand again by using

rs("dtsDate").Value

and as suggested above you can coerce the whole thing by using

CDate(rs("dtsDate"))

as well if you choose.


The other (and probably larger) issue is the malformed boolean expression.

If Week1Start <= rs(&quot;dtsDate&quot;) <= Week1End Then

is evaluated as:

If (Week1Start <= rs(&quot;dtsDate&quot;)) <= Week1End Then

So you end up comparing

Week1End

(which here we suppose to be a Date) against the result from the first comparison: the value True or False. VBScript will auto-coerce True to -1 and False to 0 (or actually to a -1.0 or 0.0 Double value due to the Date coming up in the expression). The Date

Week1End

will be coerced to a type &quot;Double.&quot;

This ends up being a comparison between -1.0 or 0.0 and some positive, floating-point number. For example today (January 11, 2004) will be 37997.0! Whether the result of the first comparison was True (-1) or False (0) it will always be less than any Date value coerced to a number. Well, not always. The base Date/Time value is 0.0 = December 30, 1899 at Midnight.

See:

http://msdn.microsoft.com/library/d...ccore/html/_core_the_date_type.asp?frame=true

So your script will [/i]always[/i] take the &quot;If&quot; branch and never get to execute any &quot;Else&quot; or &quot;ElseIf&quot; branch. This will happen no matter what value your

rs("dtsDate")

happens to have... unless it is prior to December 30, 1899 at Midnight.

The upshot of it all is that I would suggest statements like:

If Week1Start <= rs(&quot;dtsDate&quot;).Value And rs(&quot;dtsDate&quot;).Value <= Week1End Then

or possibly:

dtCheck = rs("dtsDate").Value
If Week1Start <= dtCheck And dtCheck <= Week1End Then

Either of these will look for properly typed values from your

dtsDate

field that fall between the two dates you're checking against.