Date range search using text boxes and returning results to list box

[shondra.allen]

I am trying to create a search form. I want my user to have the ability
to search records in a particular date range. I would like the results
to populate a list box where the user can double click the item needed
and open up the record in another form. I have been successful with the
double click feature in this list box although I am having trouble
getting the date range search to work and I am unsure how to get the
results to show up in the list box.

Here is my data:

List box name: List135

From Date Text Box Name: txtupdatefrom

To Date Text Box Name: txtupdateto
Field in table to be searched: [tblCourse].[DateChanged]

Thank you in advance for any help that you can provide.

 

[Guest]

Hi Shondra,

Try the following code, for a command button named: cmdFillList
The RowSourceType for your list box should be set as Table/Query


Option Compare Database
Option Explicit

Private Sub cmdFillList_Click()
On Error GoTo ProcError

Dim strSQL As String

If IsDate(Me.txtupdatefrom) And IsDate(Me.txtupdateto) Then
strSQL = "SELECT CourseName FROM tblCourse " _
& "WHERE [DateChanged] BETWEEN " _
& "#" & Format(Me.txtupdatefrom, "mm\/dd\/yyyy") & "# " _
& "AND #" & Format(Me.txtupdateto, "mm\/dd\/yyyy") & "#;"

Me.List135.RowSource = strSQL
Else
MsgBox "Please enter a valid date range.", vbCritical, "Invalid Date(s)
Entered..."
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdFillList_Click..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I am trying to create a search form. I want my user to have the ability
to search records in a particular date range. I would like the results
to populate a list box where the user can double click the item needed
and open up the record in another form. I have been successful with the
double click feature in this list box although I am having trouble
getting the date range search to work and I am unsure how to get the
results to show up in the list box.

Here is my data:

List box name: List135

From Date Text Box Name: txtupdatefrom

To Date Text Box Name: txtupdateto
Field in table to be searched: [tblCourse].[DateChanged]

Thank you in advance for any help that you can provide.