Filter by Date Problem

[sexton75 via AccessMonster.com]

Hello,

I am having a hard time with this one. I have a form that is filtering data
from two unbound text boxes (sdate)- start date and (edate)- end date which
is supposed to allow the user to enter a start and end date to limit results
from the form. (adate) is the activity date it should be filtering on on the
included subform (sFrm_ActivityLog). I formatted all of the fields in date
format, but I keep getting a Runtime error 2442 -you cant assign a value to
this object. Can anyone help me on this?

Here is what I have in the after update part of the code for the sdate and
edate field:

Private Sub SDate_AfterUpdate()

If Not IsNull(Me.SDate) Then
strWhere = strWhere & " AND [adate] >=#" & _
Me!SDate & "# "
End If

If Not IsNull(Me.EDate) Then
strWhere = strWhere & " AND [adate] <=#" & _
Me!EDate & "# "
End If

Me.sFrm_ActivityLog.Form.Filter = strWhere
Me.sFrm_ActivityLog.Form.FilterOn = True
Me.sFrm_ActivityLog.Requery

End Sub

 

[Steve Sanford]

I see two problems.

You did not declare "strSwhere" as a string

Dim strWhere as String

The first two lines of EVERY code page should be

Option Compare Database
Option Explicit

"Option Explicit" requires you to declare all variables. This will save you
from variable name typos. (of course I tpye prefectlllly... no mis-steaks
<g>)

And the filter string, "strWhere" is not valid syntax.
Running your code, "strWhere" with a form I whipped up, results in:

AND [adate] >=#4/1/2008# AND [adate] <=#9/1/2008#

It should look like:

[adate] >=#4/1/2008# AND [adate] <=#9/1/2008#



Here is your code I modified:

'-----------------------------
Dim strWhere As String

If Not IsNull(Me.sdate) Then
strWhere = strWhere & " AND [adate] >=#" & Me!sdate & "# "
End If

If Not IsNull(Me.edate) Then
strWhere = strWhere & " AND [adate] <=#" & Me!edate & "# "
End If

' Debug.Print strWhere

strWhere = Right(strWhere, Len(strWhere) - 5)

' Debug.Print strWhere

Me.sFrm_ActivityLog.Form.Filter = strWhere
Me.sFrm_ActivityLog.Form.FilterOn = True
Me.sFrm_ActivityLog.Requery
'------------------------------------

HTH

 

[sexton75 via AccessMonster.com]

Thanks so much for your help. I can't believe I made such a stupid mistake
on this. I spent hours trying to figure out the missing link and it was in
front of my face the whole time. Many thanks to you and everyone who helps
out on this site. Also, thanks for the advise on the Option Explicit. I
added that too! Have a great day!

I see two problems.

You did not declare "strSwhere" as a string

Dim strWhere as String

The first two lines of EVERY code page should be

Option Compare Database
Option Explicit

"Option Explicit" requires you to declare all variables. This will save you
from variable name typos. (of course I tpye prefectlllly... no mis-steaks
<g>)

And the filter string, "strWhere" is not valid syntax.
Running your code, "strWhere" with a form I whipped up, results in:

AND [adate] >=#4/1/2008# AND [adate] <=#9/1/2008#

It should look like:

[adate] >=#4/1/2008# AND [adate] <=#9/1/2008#

Here is your code I modified:

'-----------------------------
Dim strWhere As String

If Not IsNull(Me.sdate) Then
strWhere = strWhere & " AND [adate] >=#" & Me!sdate & "# "
End If

If Not IsNull(Me.edate) Then
strWhere = strWhere & " AND [adate] <=#" & Me!edate & "# "
End If

' Debug.Print strWhere

strWhere = Right(strWhere, Len(strWhere) - 5)

' Debug.Print strWhere

Me.sFrm_ActivityLog.Form.Filter = strWhere
Me.sFrm_ActivityLog.Form.FilterOn = True
Me.sFrm_ActivityLog.Requery
'------------------------------------

HTH

Hello,

[quoted text clipped - 26 lines]

End Sub

 

[sexton75 via AccessMonster.com]

Thanks so much for your help. I can't believe I made such a stupid mistake
on this. I spent hours trying to figure out the missing link and it was in
front of my face the whole time. Many thanks to you and everyone who helps
out on this site. Also, thanks for the advise on the Option Explicit. I
added that too! Have a great day!

I see two problems.

You did not declare "strSwhere" as a string

Dim strWhere as String

The first two lines of EVERY code page should be

Option Compare Database
Option Explicit

"Option Explicit" requires you to declare all variables. This will save you
from variable name typos. (of course I tpye prefectlllly... no mis-steaks
<g>)

And the filter string, "strWhere" is not valid syntax.
Running your code, "strWhere" with a form I whipped up, results in:

AND [adate] >=#4/1/2008# AND [adate] <=#9/1/2008#

It should look like:

[adate] >=#4/1/2008# AND [adate] <=#9/1/2008#

Here is your code I modified:

'-----------------------------
Dim strWhere As String

If Not IsNull(Me.sdate) Then
strWhere = strWhere & " AND [adate] >=#" & Me!sdate & "# "
End If

If Not IsNull(Me.edate) Then
strWhere = strWhere & " AND [adate] <=#" & Me!edate & "# "
End If

' Debug.Print strWhere

strWhere = Right(strWhere, Len(strWhere) - 5)

' Debug.Print strWhere

Me.sFrm_ActivityLog.Form.Filter = strWhere
Me.sFrm_ActivityLog.Form.FilterOn = True
Me.sFrm_ActivityLog.Requery
'------------------------------------

HTH

Hello,

[quoted text clipped - 26 lines]

End Sub

 

[Steve Sanford]

Been there - done that

Glad I could help
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks so much for your help. I can't believe I made such a stupid mistake
on this. I spent hours trying to figure out the missing link and it was in
front of my face the whole time. Many thanks to you and everyone who helps
out on this site. Also, thanks for the advise on the Option Explicit. I
added that too! Have a great day!

I see two problems.

You did not declare "strSwhere" as a string

Dim strWhere as String

The first two lines of EVERY code page should be

Option Compare Database
Option Explicit

"Option Explicit" requires you to declare all variables. This will save you
from variable name typos. (of course I tpye prefectlllly... no mis-steaks
<g>)

And the filter string, "strWhere" is not valid syntax.
Running your code, "strWhere" with a form I whipped up, results in:

AND [adate] >=#4/1/2008# AND [adate] <=#9/1/2008#

It should look like:

[adate] >=#4/1/2008# AND [adate] <=#9/1/2008#

Here is your code I modified:

'-----------------------------
Dim strWhere As String

If Not IsNull(Me.sdate) Then
strWhere = strWhere & " AND [adate] >=#" & Me!sdate & "# "
End If

If Not IsNull(Me.edate) Then
strWhere = strWhere & " AND [adate] <=#" & Me!edate & "# "
End If

' Debug.Print strWhere

strWhere = Right(strWhere, Len(strWhere) - 5)

' Debug.Print strWhere

Me.sFrm_ActivityLog.Form.Filter = strWhere
Me.sFrm_ActivityLog.Form.FilterOn = True
Me.sFrm_ActivityLog.Requery
'------------------------------------

HTH

Hello,

[quoted text clipped - 26 lines]

End Sub