strFilter format

[Chad Lupkes]

I have the following code behind a simple form:

----
Option Compare Database

Private Sub B_Clear_Click()

'remove all filters
Me!cboStoreNumber = Null
Me!cboDeptNumber = Null
Me!cboPONumber = Null

' call filter procedure
ApplyFilter


End Sub

Private Sub cboStoreNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboDeptNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboPONumber_AfterUpdate()
ApplyFilter
End Sub


Private Sub ApplyFilter()
Dim strFilter As String

strFilter = "1=1 "

' filter by Store Number?
If Not IsNull(Me!cboStoreNumber) Then
' add groupname filter
' strFilter = strFilter & " AND [Store] = '" & Me!
cboStoreNumber & "'"
strFilter = strFilter & " AND [Store] = " & Me!cboStoreNumber
& ""
End If

' filter by Dept Number?
If Not IsNull(Me!cboDeptNumber) Then
strFilter = strFilter & " AND [Dept] = " & CStr(Me!
cboDeptNumber)
End If

' filter by PO Number?
If Not IsNull(Me!cboPONumber) Then
strFilter = strFilter & " AND [PONumber] = '" & CStr(Me!
cboPONumber) & "'"
End If

' apply filter
Me.Filter = strFilter

' set filtering on
Me.FilterOn = True

End Sub
----

I'm trying to apply the filter to a date field, and it's giving me
trouble. I found the following syntax somewhere else:

If Not IsNull(Me!txtStartDate) Then
strFilter = strFilter & " And [DateField >= #" & _
Me!txtStartDate & "# "
End If

I tried this with my variables, and it didn't work. It will select a
date from the combo box, but will not filter the records on the form
itself.

So, my questions are:

1. What syntax would work better than the date example above?

2. How can I also filter by a checkbox (Yes/No)

I look forward to your reply.

 

[Graham Mandeno]

Hi Chad

So, my questions are:

1. What syntax would work better than the date example above?

Date literals in SQL must be enclosed in hash/pound signs (#) and they do
not follow regional settings for date format, so this can cause problems
outside the USA.

The most common acceptable formats are #mm/dd/yyyy# and #yyyy-mm-dd#. I
prefer the latter, as it is less ambiguous (many countries use dd/mm/yyyy
format, so is 2/06/2008 February 6 or 2 June?)

The easiest way to put the date in the required form is to use the Format
function, and as it is such a common requirement, I suggest a wrapper
function:

Public Function SqlDate( d as Date ) as String
SqlDate = Format( d, "\#yyyy-mm-dd\#" )
End Function

Then you can say:

strFilter = "[DateField]>=" & SqlDate(Me!txtStartDate)

2. How can I also filter by a checkbox (Yes/No)

A checkbox has the value -1 (true) or 0 (false), as does a boolean (yes/no)
field. So you can just say:

strFilter = "[BooleanField]=" & Me!chkBox

If you set the TriState property of the checkbox then it can have three
values:
checked = True (-1)
unchecked (white) = False (0)
unchecked (grey) = Null

You can then choose to filter on the field for either true or false, or
ignore the field altogether for null.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have the following code behind a simple form:

----
Option Compare Database

Private Sub B_Clear_Click()

'remove all filters
Me!cboStoreNumber = Null
Me!cboDeptNumber = Null
Me!cboPONumber = Null

' call filter procedure
ApplyFilter


End Sub

Private Sub cboStoreNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboDeptNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboPONumber_AfterUpdate()
ApplyFilter
End Sub


Private Sub ApplyFilter()
Dim strFilter As String

strFilter = "1=1 "

' filter by Store Number?
If Not IsNull(Me!cboStoreNumber) Then
' add groupname filter
' strFilter = strFilter & " AND [Store] = '" & Me!
cboStoreNumber & "'"
strFilter = strFilter & " AND [Store] = " & Me!cboStoreNumber
& ""
End If

' filter by Dept Number?
If Not IsNull(Me!cboDeptNumber) Then
strFilter = strFilter & " AND [Dept] = " & CStr(Me!
cboDeptNumber)
End If

' filter by PO Number?
If Not IsNull(Me!cboPONumber) Then
strFilter = strFilter & " AND [PONumber] = '" & CStr(Me!
cboPONumber) & "'"
End If

' apply filter
Me.Filter = strFilter

' set filtering on
Me.FilterOn = True

End Sub
----

I'm trying to apply the filter to a date field, and it's giving me
trouble. I found the following syntax somewhere else:

If Not IsNull(Me!txtStartDate) Then
strFilter = strFilter & " And [DateField >= #" & _
Me!txtStartDate & "# "
End If

I tried this with my variables, and it didn't work. It will select a
date from the combo box, but will not filter the records on the form
itself.

So, my questions are:

1. What syntax would work better than the date example above?

2. How can I also filter by a checkbox (Yes/No)

I look forward to your reply.

 

[Chad Lupkes]

Hi Graham,

Well, the system likes the code, and it indicates within the form that
the filter is trying to work, but it's not actually doing the
filtering of the query results. Your explanations are really helping,
but I'm missing something.

I don't have enough experience to know what else I can provide here to
narrow the problem down. Any ideas?

Chad

Hi Chad

So, my questions are:

1. What syntax would work better than the date example above?

Date literals in SQL must be enclosed in hash/pound signs (#) and they do
not follow regional settings for date format, so this can cause problems
outside the USA.

The most common acceptable formats are #mm/dd/yyyy# and #yyyy-mm-dd#. I
prefer the latter, as it is less ambiguous (many countries use dd/mm/yyyy
format, so is 2/06/2008 February 6 or 2 June?)

The easiest way to put the date in the required form is to use the Format
function, and as it is such a common requirement, I suggest a wrapper
function:

Public Function SqlDate( d as Date ) as String
SqlDate = Format( d, "\#yyyy-mm-dd\#" )
End Function

Then you can say:

strFilter = "[DateField]>=" & SqlDate(Me!txtStartDate)

2. How can I also filter by a checkbox (Yes/No)

A checkbox has the value -1 (true) or 0 (false), as does a boolean (yes/no)
field. So you can just say:

strFilter = "[BooleanField]=" & Me!chkBox

If you set the TriState property of the checkbox then it can have three
values:
checked = True (-1)
unchecked (white) = False (0)
unchecked (grey) = Null

You can then choose to filter on the field for either true or false, or
ignore the field altogether for null.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have the following code behind a simple form:

Private Sub B_Clear_Click()

'remove all filters
Me!cboStoreNumber = Null
Me!cboDeptNumber = Null
Me!cboPONumber = Null

' call filter procedure
ApplyFilter

End Sub

Private Sub cboStoreNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboDeptNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboPONumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub ApplyFilter()
Dim strFilter As String

strFilter = "1=1 "

' filter by Store Number?
If Not IsNull(Me!cboStoreNumber) Then
' add groupname filter
' strFilter = strFilter & " AND [Store] = '" & Me!
cboStoreNumber & "'"
strFilter = strFilter & " AND [Store] = " & Me!cboStoreNumber
& ""
End If

' filter by Dept Number?
If Not IsNull(Me!cboDeptNumber) Then
strFilter = strFilter & " AND [Dept] = " & CStr(Me!
cboDeptNumber)
End If

' filter by PO Number?
If Not IsNull(Me!cboPONumber) Then
strFilter = strFilter & " AND [PONumber] = '" & CStr(Me!
cboPONumber) & "'"
End If

' apply filter
Me.Filter = strFilter

' set filtering on
Me.FilterOn = True

End Sub
----

I'm trying to apply the filter to a date field, and it's giving me
trouble. I found the following syntax somewhere else:

If Not IsNull(Me!txtStartDate) Then
strFilter = strFilter & " And [DateField >= #" & _
Me!txtStartDate & "# "
End If

I tried this with my variables, and it didn't work. It will select a
date from the combo box, but will not filter the records on the form
itself.

So, my questions are:

1. What syntax would work better than the date example above?

2. How can I also filter by a checkbox (Yes/No)

I look forward to your reply.

 

[Graham Mandeno]

Hi Chad

What do you mean by saying "it indicates within the form that the filter is
trying to work"?

Can you please copy the SQL of the form's recordsource query and paste it in
in your reply.

Also, add Debug.Print strFilter just before Me.Filter = strFilter and copy
and paste the result of that also.
--
Thanks

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Well, the system likes the code, and it indicates within the form that
the filter is trying to work, but it's not actually doing the
filtering of the query results. Your explanations are really helping,
but I'm missing something.

I don't have enough experience to know what else I can provide here to
narrow the problem down. Any ideas?

Chad

Hi Chad

So, my questions are:

1. What syntax would work better than the date example above?

Date literals in SQL must be enclosed in hash/pound signs (#) and they do
not follow regional settings for date format, so this can cause problems
outside the USA.

The most common acceptable formats are #mm/dd/yyyy# and #yyyy-mm-dd#. I
prefer the latter, as it is less ambiguous (many countries use dd/mm/yyyy
format, so is 2/06/2008 February 6 or 2 June?)

The easiest way to put the date in the required form is to use the Format
function, and as it is such a common requirement, I suggest a wrapper
function:

Public Function SqlDate( d as Date ) as String
SqlDate = Format( d, "\#yyyy-mm-dd\#" )
End Function

Then you can say:

strFilter = "[DateField]>=" & SqlDate(Me!txtStartDate)

2. How can I also filter by a checkbox (Yes/No)

A checkbox has the value -1 (true) or 0 (false), as does a boolean
(yes/no)
field. So you can just say:

strFilter = "[BooleanField]=" & Me!chkBox

If you set the TriState property of the checkbox then it can have three
values:
checked = True (-1)
unchecked (white) = False (0)
unchecked (grey) = Null

You can then choose to filter on the field for either true or false, or
ignore the field altogether for null.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have the following code behind a simple form:

Private Sub B_Clear_Click()

'remove all filters
Me!cboStoreNumber = Null
Me!cboDeptNumber = Null
Me!cboPONumber = Null

' call filter procedure
ApplyFilter

End Sub

Private Sub cboStoreNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboDeptNumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub cboPONumber_AfterUpdate()
ApplyFilter
End Sub

Private Sub ApplyFilter()
Dim strFilter As String

strFilter = "1=1 "

' filter by Store Number?
If Not IsNull(Me!cboStoreNumber) Then
' add groupname filter
' strFilter = strFilter & " AND [Store] = '" & Me!
cboStoreNumber & "'"
strFilter = strFilter & " AND [Store] = " & Me!cboStoreNumber
& ""
End If

' filter by Dept Number?
If Not IsNull(Me!cboDeptNumber) Then
strFilter = strFilter & " AND [Dept] = " & CStr(Me!
cboDeptNumber)
End If

' filter by PO Number?
If Not IsNull(Me!cboPONumber) Then
strFilter = strFilter & " AND [PONumber] = '" & CStr(Me!
cboPONumber) & "'"
End If

' apply filter
Me.Filter = strFilter

' set filtering on
Me.FilterOn = True

End Sub
----

I'm trying to apply the filter to a date field, and it's giving me
trouble. I found the following syntax somewhere else:

If Not IsNull(Me!txtStartDate) Then
strFilter = strFilter & " And [DateField >= #" & _
Me!txtStartDate & "# "
End If

I tried this with my variables, and it didn't work. It will select a
date from the combo box, but will not filter the records on the form
itself.

So, my questions are:

1. What syntax would work better than the date example above?

2. How can I also filter by a checkbox (Yes/No)

I look forward to your reply.