Date Filter - Access 2003

[Darhl Thomason]

I'm trying to filter my database by date ranges. I have text box controls
for txtInstallStartDate and for txtInstallEndDate. I believe I have the
logic worked out to create the filter, but when I add a date into the box,
it brings up the debug window saying I can't assign a value to this object.
I think it has to do with how my code is building the filter string. End
result of my filter string is:
(tblStoreData.InstallDate => "12/01/05" And tblStoreData.InstallDate <=
"12/31/05")

tblStoreData.InstallDate is a Date/Time field with a Short Date format. I
tried running it without the double quotes in the filter string but got the
same result.

Here is the code I am using to build the filter string:
'Start Install Date Filter
If Not IsNull(Me.txtInstallStartDate) Then
If Not IsNull(strFilter) Then
strFilter = strFilter & " And ("
Else
strFilter = strFilter & "("
End If
strFilter = strFilter & "tblStoreData.InstallDate => """ &
Me.txtInstallStartDate & """"
setInstallDate = True
End If
If Not IsNull(Me.txtInstallEndDate) Then
If Not IsNull(strFilter) And setInstallDate = False Then
strFilter = strFilter & " And ("
Else
If setInstallDate = True Then
strFilter = strFilter & " And "
Else
strFilter = strFilter & "("
End If
End If
strFilter = strFilter & "tblStoreData.InstallDate <= """ &
Me.txtInstallEndDate & """"
setInstallDate = True
End If
If setInstallDate = True Then
strFilter = strFilter & ")"
End If
'End Install Date Filter

Thanks for any help!

Darhl

 

[Douglas J. Steele]

Dates need to be delimited with # characters, not quotes. (And, for the sake
of completeness, the dates need to be in mm/dd/yyyy format, or an
unambiguous format such as yyyy-mm-dd, regardless of what the short date
format has been set to through Regional Settings)

 

[John Vinson]

I'm trying to filter my database by date ranges. I have text box controls
for txtInstallStartDate and for txtInstallEndDate. I believe I have the
logic worked out to create the filter, but when I add a date into the box,
it brings up the debug window saying I can't assign a value to this object.
I think it has to do with how my code is building the filter string. End
result of my filter string is:
(tblStoreData.InstallDate => "12/01/05" And tblStoreData.InstallDate <=
"12/31/05")

Dates are NOT text strings. They should be delimited using the #
character, not ".

tblStoreData.InstallDate is a Date/Time field with a Short Date format. I
tried running it without the double quotes in the filter string but got the
same result.

Here is the code I am using to build the filter string:
'Start Install Date Filter
If Not IsNull(Me.txtInstallStartDate) Then
If Not IsNull(strFilter) Then
strFilter = strFilter & " And ("
Else
strFilter = strFilter & "("
End If
strFilter = strFilter & "tblStoreData.InstallDate => """ &
Me.txtInstallStartDate & """"

Try

strFilter = strFilter & "tblStoreData.InstallDate => #" &
Me.txtInstallStartDate & "#"

setInstallDate = True

Is this variable in a Dim statement (hopefully as a Boolean)?

End If
If Not IsNull(Me.txtInstallEndDate) Then
If Not IsNull(strFilter) And setInstallDate = False Then

sort of belt and suspenders here...

Also note that a String variable (unlike a Variant) can never be NULL.

strFilter = strFilter & " And ("
Else
If setInstallDate = True Then
strFilter = strFilter & " And "
Else
strFilter = strFilter & "("
End If
End If
strFilter = strFilter & "tblStoreData.InstallDate <= """ &
Me.txtInstallEndDate & """"
setInstallDate = True

Same drill with the # delimiter...

End If
If setInstallDate = True Then
strFilter = strFilter & ")"
End If
'End Install Date Filter

It would help a lot if you would indicate what line of the code is
generating the error. Have you tried setting a breakpoint and stepping
through the code line by line?

John W. Vinson[MVP]

 

[Darhl Thomason]

OK, so how do I get the dates into mm/dd/yyyy format? In my main table, the
field is set as Short Date. If I set it to Long Date, then the date ends up
being Tuesday, December 6, 2005. Should I set my field to Long Date and set
a custom input mask for the controls?

 

[Darhl Thomason]

I also tried changing the date delimiter to # instead of ". I'm still
getting the error. My filter text now looks like:
(tblStoreData.StatusID = 2) And (tblStoreData.InstallDate => #12/01/05#)

My code for building the filter now looks like"
'Start Install Date Filter
If Not IsNull(Me.txtInstallStartDate) Then
If Not IsNull(strFilter) Then
strFilter = strFilter & " And ("
Else
strFilter = strFilter & "("
End If
strFilter = strFilter & "tblStoreData.InstallDate => #" &
Me.txtInstallStartDate & "#"
setInstallDate = True
End If
If Not IsNull(Me.txtInstallEndDate) Then
If Not IsNull(strFilter) And setInstallDate = False Then
strFilter = strFilter & " And ("
Else
If setInstallDate = True Then
strFilter = strFilter & " And "
Else
strFilter = strFilter & "("
End If
End If
strFilter = strFilter & "tblStoreData.InstallDate <= #" &
Me.txtInstallEndDate & "#"
setInstallDate = True
End If
If setInstallDate = True Then
strFilter = strFilter & ")"
End If
'End Install Date Filter

Thanks!

Darhl

 

[Darhl Thomason]

This worked....I now have the date in mm/dd/yyyy format, please see my other
post about the filter.

Thanks,

Darhl

 

[Darhl Thomason]

Make that filter line:
(tblStoreData.StatusID = 2) And (tblStoreData.InstallDate => #12/01/2005#)

 

[Douglas J Steele]

When the error message is generated, what's the actual line of code it's
complaining about? Is it in your routine that's generating the filter, or is
it somewhere else in your code?

 

[Darhl Thomason]

Doug,

It is at the end of the routine that is generating the filter. The actual
line is:
Me.Filter = strFilter

When I was working through some other filter problems, I found that this is
usually because there is an invalid character that the filter doesn't
like...I don't know enough about filters, especially date filters to know
what exactly it doesn't like.

Thanks!!

d

 

[Douglas J. Steele]

Missed the fact that your filter is actually incorrect. It's >=, not =>.

 

[Darhl Thomason]

Got it! That was exactly the trick!

Thanks a ton for the great eyes!

Darhl