Populating a listbox with recordset and comparing dates

Question

My program has 3 fields namely title, start date and end date for the user to enter. I wish to populate my listbox which is called "filteredResults" with results that contains records with tiles equal to the one entered by the user OR that falls into the range of the date.

May I know:

1) How can I populate the listbox with the recordset obtained from the query?

2) How can I compare the date inside the query?

Thanks a lot!

Private Sub FilterProj_Click()
Dim title As String, startDate As Date, endDate As Date
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Project WHERE  ORDER BY [ProjectId] DESC")
filteredResults.Recordset = rs

Answer 1

For Question 1:

Populate directly from query:

Me.mylistbox.rowsource = _
  db.OpenRecordset("SELECT titles FROM Project _
           WHERE title = '" & parameter & "' ORDER BY [ProjectId] DESC")

Populate using Recordset object:

If (rs.RecordCount <> 0) Then 
   Do While Not rs.EOF
      mylistbox.Items.Add(rs.Fields(0).Value)
      rs.MoveNext()
   Loop
End IF

Another method:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT titles from Projects _
       WHERE title = '" & parameter & "' ORDER BY [ProjectId] DESC"

Set db = CurrentDb
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
qdf.Connect = strConn

Set rs = qdf.OpenRecordset()

Set Me.mylistbox.Recordset = rs

Question 2:

Not very clear. I assume you require a parameterized query. By setting a user's value into where, and clauses.

  db.OpenRecordset("SELECT titles FROM Project _ 
      WHERE title = '" & parameter & "' ORDER BY [ProjectId] DESC")

Or you may use the following article to set the parameter in query design view itself.

• Reference from MSDN: Using paramterized queries

Answer 2

Set the row source property for your filteredResults list box to a query which uses a strategy similar to this.

SELECT p.ProjectId, p.title, p.date_field
FROM [Project] AS p
WHERE
    p.title = Forms![YourForm]![txtTitle]
    OR
        (
                p.date_field >= Forms![YourForm]![txtStartDate]
            AND p.date_field <= Forms![YourForm]![txtEndDate]
        )
ORDER BY p.ProjectId DESC

Whenever you want to update the contents of filteredResults to reflect changes to the user-submitted title, start date, and/or end date, you can requery the list box:

Me!filteredResults.Requery

You could trigger the requery from a command button, or from the after update events of the text boxes.

Note I assumed your Project table includes a text field named title and a Date/Time field named date_field. I made up my own names (txtTitle, txtStartDate, txtEndDate) for the text boxes. Set the Format property to "General Date" for txtStartDate and txtEndDate. Substitute the name of your form for YourForm.