I have an Form in Access 2003.

I am using a listBox in an attempt to display filtered records (by date).

The listBox RowSource value is initially set to:
SELECT Landowners.PermissionRenewalDueDate, Landowners.* FROM Landowners WHERE (((Landowners.PermissionRenewalDueDate)<#9/21/2006#));

FYI: I will be changing the listBox's query to have no WHERE clause once I get this fixed.

In my VBA code, I have a button_click handler to requery and pass a Date to compare the records with - I want to get all records Older (less than) the specified date.  Currently, the date is just hard-coded to today (I wil be adding a Date picker later}.
When I change the Rowsource and execute the Requery (see code below), the listBox ends up being empty. I have even tried setting the SQL string to the same string as the initial Rowsource value and the listBox ends up EMPTY after the button_click handling.

Private Sub btnGetDueDatePast_Click()

    Dim strSQL As String
    Dim dtToday As Date
    Dim sDate As String

    dtToday = Date

    strSQL = "SELECT Landowners.* FROM Landowners ORDER BY [LandownerLastName] WHERE Landowners.PermissionRenewalDueDate < " & dtToday


' ATTEMPT 2 - Still did NOT work
'    sDate = dtToday ' Try using string instead of date for parameter and pass with "#" delimiters

'     strSQL = "SELECT Landowners.* FROM Landowners ORDER BY [LandownerLastName] WHERE Landowners.PermissionRenewalDueDate < #" & sDate & "#"

    Me.lstFiltered = Null
    Me.lstFiltered.RowSource = strSQL
    Me.lstFiltered.Requery
    
    
End Sub

I appreciate and help that can be provided.

Hi !

Try to change your sql statement  with this

strSQL = "SELECT * FROM Landowners WHERE PermissionRenewalDueDate < #" & sDate & "# order by landownerlastname"

Hope it helps
vyx

[Edited by VYX on 23-09-2006 at 07:49 AM GMT]

YUP !!!
That did the trick.
Thanks.
- Bill