Filter combo on 2 columns

[Jane]

Hello

I have a combo box on a form with student bus details. It displays all the
busses that are available for a specific route, but this can be quite long
list.

The combo is based on a query.
It shows
BusID
PickupPoint
TimeDateDepart
TimeDateArrive

So the 2 dates are the 3rd and 4th columns.

Is it possible to have a button on my form to filter this combo?

If possible I would like to filer the list just to show busses that depart
between
1 day before the trip (the trip date is shown on the form in a text box)
And up to 1 day after the trip date.

I have tried to say
Me.BussList = between #date# AND #date#
But this seems not to work.

Thank you for any assistance you can offer.


Jane Hollin

 

[Douglas J. Steele]

Not sure how you were planning on triggering this. Let's assume you've got a
button on your form (and that the date on your form is input into a text box
named txtTripDate)

In the Click event of that button, you can put code like:

Private Sub MyButton_Click()
Dim dtmEarlier As Date
Dim dtmLater As Date
Dim strSQL As String

If IsDate(Me.txtTripDate & vbNullString) Then
dtmEarlier = DateAdd("d", -1, CDate(Me.txtTripDate)
dtmLater = DateAdd("d", 1, CDate(Me.txtTripDate)
strSQL = "SELECT BusID, PickupPoint, " & _
"TimeDateDepart, TimeDateArrive " & _
"FROM MyTable " & _
"WHERE TimeDateDepart <= " & _
Format(dtmEarlier, "\yyyy\-mm\-dd\#") & _
" AND TimeDateArrive >= " & _
Format(dtmLater, "\yyyy\-mm\-dd\#")
Me!MyCombo.RowSource = strSQL
Else
MsgBox "Please enter a valid date."
End If

End Sub

 

[Lars Brownies]

After
Me!MyCombo.RowSource = strSQL
you'll probably need
Me!MyCombo.Requery

Lars

 

[Douglas J. Steele]

I'm not sure about that. I believe changing the RowSource automatically
forces a requery.

 

[Lars Brownies]

I'm not sure about that. I believe changing the RowSource automatically
forces a requery.

 

[Lars Brownies]

I tested it and I stand corrected.

Lars

I'm not sure about that. I believe changing the RowSource automatically
forces a requery.