Start Date and End Date Filter Code Miscommunication

[R.Jones]

Hi!

On Allen Browne's website, link posted below, I am trying to use a portion of
Method 2 that is applicable to my situation.
http://allenbrowne.com/casu-08.html

I have two text boxes on a search/filter form that are the Start Date and End
Date, and I would like for them to be part of the search criteria (there are
5 other possible search criterium). The way the code is right now, posted
below, if I only search by the Start Date, or only by the End Date it works.
However, when I try to search a range using both, I get the error "Run Time
Error 2448 - You Can't Assign A Value To This Object", and when I press Debug,
it takes me to the line of text
"Me.Filter =strWhere"

Both text boxes have medium date formats. The field that they are attached
to is a Date/Time field called [Effective Date]. The form runs off a query
that gathers information from two tables, and the other 4 search criterium
work great, the 5th is another situation like this one.

I would truly appreciate it if anyone could add some insight into what might
be wrong!

Thank you!

Code:
If IsDate(Me.txtEffStartDate) Then
strWhere = strWhere & "([Effective Date] >= " & Format(Me.txtEffStartDate,
conJetDate) & ") AND "
End If
If IsDate(Me.txtEffEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "([Effective Date] < " & Format(Me.txtEffEndDate,
conJetDate) & ") AND "
End If
(where conJetDate is "\#mm\/dd\/yyyy\#")

 

[Alex Dybenko]

Hi,
looks like you have too many AND, try:

If IsDate(Me.txtEffStartDate) Then
strWhere = strWhere & "([Effective Date] >= " & Format(Me.txtEffStartDate,
conJetDate) & ")"
End If
If IsDate(Me.txtEffEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "([Effective Date] < " & Format(Me.txtEffEndDate,
conJetDate) & ")"
End If


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Jim Burke in Novi]

What Alex said. I noticed that you are using >= for StartDate but only < for
EndDate - is that what you really want?

 

[R.Jones via AccessMonster.com]

Thanks guys for your help, but now I get a new error (

Run Time Error 3075 "Syntax Error in date query expression"

Any thoughts still?

What Alex said. I noticed that you are using >= for StartDate but only < for
EndDate - is that what you really want?

Hi!

[quoted text clipped - 34 lines]

End If
(where conJetDate is "\#mm\/dd\/yyyy\#")

 

[John W. Vinson]

Thanks guys for your help, but now I get a new error (

Run Time Error 3075 "Syntax Error in date query expression"

Any thoughts still?

Not without seeing your actual code, no.