craigachan
Registered User.
- Local time
- Today, 04:23
- Joined
- Nov 9, 2007
- Messages
- 282
I've never been good at sql syntax. I'm trying to change the sorting order of a listbox using an option group. I can use sql to change the rowsource some of the time but not all the time. So I conclude that I don't understand something somewhere.
Everything works until is use Case 4. Then my listbox goes blank. I can't seem to figure out the 'WHERE' syntax and spacing. Can someone tell me what is wrong and why its' wrong? Thanks you.
Code:
Dim strAD As String 'Acending Decending
Dim eom As String
eom = DateSerial(Year(Date), Month(Date) + 1, 0)
If Me.frAD <> 1 Then strAD = "DESC"
Select Case Me.frSort
Case 1 'PName
Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.PName " & strAD
Case 2 'Last Visit
Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.LastVisit " & strAD
Case 3 'Recall Date
Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.Recall " & strAD
Case 4 'Up to end of this month
Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.Recall " & strAD & _
"WHERE QryRecallPtList.Recall < #" & eom & "#"
Case 5 'up to end of next month
End Select
Everything works until is use Case 4. Then my listbox goes blank. I can't seem to figure out the 'WHERE' syntax and spacing. Can someone tell me what is wrong and why its' wrong? Thanks you.