Date Filter - Access 2003

  • Thread starter Thread starter Darhl Thomason
  • Start date Start date
D

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
 
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)
 
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")
Click to expand...

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 & """"
Click to expand...

Try

strFilter = strFilter & "tblStoreData.InstallDate => #" &
Me.txtInstallStartDate & "#"
setInstallDate = True
Click to expand...

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
Click to expand...

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
Click to expand...

Same drill with the # delimiter...
End If
If setInstallDate = True Then
strFilter = strFilter & ")"
End If
'End Install Date Filter
Click to expand...

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]
 
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?
 
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
 
This worked....I now have the date in mm/dd/yyyy format, please see my other
post about the filter.

Thanks,

Darhl
 
Make that filter line:
(tblStoreData.StatusID = 2) And (tblStoreData.InstallDate => #12/01/2005#)
 
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?
 
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
 
Back
Top