Multiple Criteria Filter

[Chalkie42]

Hi,

I have a form bound to a table and I need to display and edit records. I want to narrow down the records by applying two filters; one combobox to select the ClientID and another to select the date. What I have so far is this:

Private Sub CboClientID_AfterUpdate()

Me.Detail.Visible = True

CboDate.RowSource = "Select AppointmentDate " & _
"FROM tblSample " & _
"WHERE ClientID = '" & CboClientID & "' " & _
"ORDER BY AppointmentDate"
End Sub

then in the cboDate afterupdate event I have:

Private Sub CboDate_AfterUpdate()
Me.Filter = "ClientID = '" & Me.CboClientID & "' AND [AppointmentDate] = #" & Me.CboDate & "#"

Me.FilterOn = True

End Sub

This appears to work with some records and not with others and I don't know why?

Any advice appreciated.

 

[pbaldy]

The code looks fine, so my guess is date format:

http://allenbrowne.com/ser-36.html

http://access.mvps.org/access/datetime/date0005.htm

 

[rzw0wr]

Is ClientID a number or text?

Dale

 

[Chalkie42]

ClientID is text. Both the combo boxes are unbound and the filter works fine in that only the relevant dates show up in cboDate but when one is selected some retreive records and some don't. Foramtting is all fine and records do exist for all dates.

 

[pbaldy]

Did you read the links? Given your location it's likely the problem.

 

[Chalkie42]

Yes I did see the links, I as just answering the previous question.

The Allen Browne link did the trick. Many thanks.

 

[pbaldy]

No problem.