Filtering records using 2 combo boxes

[Guest]

I wish to filter the records in a form firstly by the "date" field, then by
the "depot number" field. I can filter the form by depot number on its own
but not by the date on it's own & certainly not by both!

The code for the depot filter is as follows:

Private Sub DepotFilter_AfterUpdate()

Me.Filter = "[Depot] = '" & DepotFilter & "'"
Me.FilterOn = True
Me.Form.Requery

End Sub

The same code does not work for the date field (I presume for data type
reasons).

Can anyone help?

 

[Wayne Morgan]

If Depot is really a number, the syntax should be

Me.Filter = "[Depot] = " & DepotFilter

If it is a number stored as text, what you have is correct. The delimiter
for a date field is a # instead of " or '. Also, to reduce the possibility
of problems, Date shouldn't be the name of the field since it is a reserved
word.

Example:
Me.Filter = "[DateField] = #" & MyDate & "#"

 

[Guest]

Thank you for your response Wayne.

Any idea how I could use both at the same time to filter records?

Regards John.

If Depot is really a number, the syntax should be

Me.Filter = "[Depot] = " & DepotFilter

If it is a number stored as text, what you have is correct. The delimiter
for a date field is a # instead of " or '. Also, to reduce the possibility
of problems, Date shouldn't be the name of the field since it is a reserved
word.

Example:
Me.Filter = "[DateField] = #" & MyDate & "#"

--
Wayne Morgan
MS Access MVP

I wish to filter the records in a form firstly by the "date" field, then by
the "depot number" field. I can filter the form by depot number on its own
but not by the date on it's own & certainly not by both!

The code for the depot filter is as follows:

Private Sub DepotFilter_AfterUpdate()

Me.Filter = "[Depot] = '" & DepotFilter & "'"
Me.FilterOn = True
Me.Form.Requery

End Sub

The same code does not work for the date field (I presume for data type
reasons).

Can anyone help?

 

[Allen Browne]

Just combine the 2 strings, with AND between them, i.e.:
Me.Filter = "([Depot] = " & DepotFilter & ") AND ([DateField] = #" &
MyDate & "#)"
Me.FilterOn = True

BTW, you don't need the Requery.

If you live in a place where the dates are not m/d/y, see also:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your response Wayne.

Any idea how I could use both at the same time to filter records?

Regards John.

If Depot is really a number, the syntax should be

Me.Filter = "[Depot] = " & DepotFilter

If it is a number stored as text, what you have is correct. The delimiter
for a date field is a # instead of " or '. Also, to reduce the
possibility
of problems, Date shouldn't be the name of the field since it is a
reserved
word.

Example:
Me.Filter = "[DateField] = #" & MyDate & "#"

--
Wayne Morgan
MS Access MVP

I wish to filter the records in a form firstly by the "date" field, then
by
the "depot number" field. I can filter the form by depot number on its
own
but not by the date on it's own & certainly not by both!

The code for the depot filter is as follows:

Private Sub DepotFilter_AfterUpdate()

Me.Filter = "[Depot] = '" & DepotFilter & "'"
Me.FilterOn = True
Me.Form.Requery

End Sub

The same code does not work for the date field (I presume for data type
reasons).

Can anyone help?