Syntax issue with listbox.rowsource

craigachan

Registered User.
Local time
Yesterday, 21:28
Joined
Nov 9, 2007
Messages
285
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.

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.
 
SQL Dates must be formatted to #mm/dd/yyyy#

for example
Format([eom], "mm/dd/yyyy")

I prefer to do it all in one step and explicitly escape the literal characters:

& Format([eom], "\#mm\/dd\/yyyy\#")
 
I think the problem is with the clauses out of order. WHERE comes before ORDER BY.
 
That would be a problem. Never even noticed the ORDER BY.

The date formatting always jumps out as a likely cause to those of us not in the USA.
 
Thank you all it worked well and took care of the problem. I'll have to watch those things next time.
 
Happy to help. The date thing is certainly a potential issue, but because of how you were setting it I don't think it was one here.
 

Users who are viewing this thread

Back
Top Bottom