Date Filter - Access 2003

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in message
...
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

On Mon, 5 Dec 2005 16:52:23 -0800, "Darhl Thomason"
wrote:

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]

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?


"Douglas J. Steele" wrote in message
...
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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in message
...
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

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

"Douglas J. Steele" wrote in message
...
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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in message
...
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

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

Thanks,

Darhl


"Darhl Thomason" wrote in message
...
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?


"Douglas J. Steele" wrote in message
...
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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in message
...
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

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

"Darhl Thomason" wrote in message
...
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

"Douglas J. Steele" wrote in message
...
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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in message
...
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

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 Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Darhl Thomason" wrote in message
...
Make that filter line:
(tblStoreData.StatusID = 2) And (tblStoreData.InstallDate = #12/01/2005#)

"Darhl Thomason" wrote in message
...
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

"Douglas J. Steele" wrote in message
...
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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in message
...
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

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" wrote in message
...
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 Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Darhl Thomason" wrote in message
...
Make that filter line:
(tblStoreData.StatusID = 2) And (tblStoreData.InstallDate =
#12/01/2005#)

"Darhl Thomason" wrote in message
...
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

"Douglas J. Steele" wrote in
message
...
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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in
message
...
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

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in message
...
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" wrote in message
...
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 Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Darhl Thomason" wrote in message
...
Make that filter line:
(tblStoreData.StatusID = 2) And (tblStoreData.InstallDate =
#12/01/2005#)

"Darhl Thomason" wrote in message
...
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

"Douglas J. Steele" wrote in
message
...
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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Darhl Thomason" wrote in
message
...
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