help with IIF or CASE

[DirtDawg]

I have an sql7 database. I would like to retrieve the information into a XLS. I am trying to use the week numbers as order by. my problem is that I have 7 weeks to dowload. so if the first week is 47 it makes the last week 01 ie
47
48
49
50
51
52
01

Is there a way to make this possible I have some code that i have been working on

If Val(strColBFirst) >= 46 Then
strAndOr = " Or "
strOrderBy = "IIF(wkn >= " & Val(strColBFirst) & "," & _
" wkn - 53, wkn)"
Else
strAndOr = " And "
strOrderBy = " wkn "

'strOrderBy = _
' "IIF(wkn >= " & Val(strColBFirst) & "," & _
' " wkn - 53, wkn)"

End If
If Descending Then
strDesc = " DESC"
End If


Set rst = CreateObject("ADODB.Recordset")
src = "SELECT srv, vslvoy, wkn, etd, jpn, twn FROM vsl " & _
"WHERE srv = '" & strColAMax & "' " & _
"AND (wkn >= '" & strColBFirst & "' " & strAndOr & _
" wkn <= '" & strColBLast & "') ORDER BY " & strOrderBy & strDesc

rst.Open Source:=src, ActiveConnection:=cnn
rst.movenext

The error is always IIF(wkn ">=" any ideas

Thanks in adavance

 

[SonOfEmidec1100]

In the WHERE clause you are treating wkn as a string, in the ORDER BY as numeric.

If it is a string you need to think about leading zeroes as well.