Syntax issue with listbox.rowsource (1 Viewer)

craigachan

Registered User.
Local time
Today, 00:08
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.

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.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:08
Joined
Jan 20, 2009
Messages
12,849
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\#")
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:08
Joined
Aug 30, 2003
Messages
36,118
I think the problem is with the clauses out of order. WHERE comes before ORDER BY.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 18:08
Joined
Jan 20, 2009
Messages
12,849
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.
 

craigachan

Registered User.
Local time
Today, 00:08
Joined
Nov 9, 2007
Messages
282
Thank you all it worked well and took care of the problem. I'll have to watch those things next time.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:08
Joined
Aug 30, 2003
Messages
36,118
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

Top Bottom