Date By Month Filter

[Guest]

Stuck again. If it werent for this site I would be lost. Thanks to all you
MVP's. I have a query that formats a date field to read only the month and
year that looks like this:

Format$([DateOfService],'mmmm yyyy')

The problem is that I have a form with filters on it and it keeps giving me
run-time error 2448, You cant assign a value to this object. I know I have
my datefilter formated incorrectly. Can someone help, here is the code I am
using:

Private Sub Filter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#mmmm yyyy#"

If Not IsNull(Me.DatabaseFilter) Then
strWhere = strWhere & "([DataBase]=""" & Me.DatabaseFilter & """) AND "
End If
If Not IsNull(Me.LocationFilter) Then
strWhere = strWhere & "([LocationCode]=" & Me.LocationFilter & ") AND "
End If
If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]=" &
Format(Me.DateByMonthFilter, conJetDate) & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Please enter information into at least one field before
clicking the filter button.", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Douglas J. Steele]

Assuming that Date Of Service By Month is a text field, you need quotes
around the value you're checking:

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]='" & _
Format(Me.DateByMonthFilter, conJetDate) & "') AND "
End If

Exagerated for clarity, that's

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]= ' " & _
Format(Me.DateByMonthFilter, conJetDate) & " ' ) AND "
End If

If it's a Date field, then regardless of what you may think, it contains a
complete date. That means you either need to apply the format to the date
field, or compare the date field to a range. (The latter is better):

 

[Guest]

Date Of Service By Month is a date field. Let me tell you what I am trying
to do and maybe you can tell me how you would do it. I want to filter
medical record charts by Database, Location, and Date of Service. Instead
filtering a single date, I want to filter by March 2007, or June 2005, ect...
I have the Date Of Service By Month formated to show the dates the way I
want, I just need the filter to work.

Assuming that Date Of Service By Month is a text field, you need quotes
around the value you're checking:

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]='" & _
Format(Me.DateByMonthFilter, conJetDate) & "') AND "
End If

Exagerated for clarity, that's

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]= ' " & _
Format(Me.DateByMonthFilter, conJetDate) & " ' ) AND "
End If

If it's a Date field, then regardless of what you may think, it contains a
complete date. That means you either need to apply the format to the date
field, or compare the date field to a range. (The latter is better):


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Stuck again. If it werent for this site I would be lost. Thanks to all
you
MVP's. I have a query that formats a date field to read only the month
and
year that looks like this:

Format$([DateOfService],'mmmm yyyy')

The problem is that I have a form with filters on it and it keeps giving
me
run-time error 2448, You cant assign a value to this object. I know I
have
my datefilter formated incorrectly. Can someone help, here is the code I
am
using:

Private Sub Filter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#mmmm yyyy#"

If Not IsNull(Me.DatabaseFilter) Then
strWhere = strWhere & "([DataBase]=""" & Me.DatabaseFilter & """) AND "
End If
If Not IsNull(Me.LocationFilter) Then
strWhere = strWhere & "([LocationCode]=" & Me.LocationFilter & ") AND "
End If
If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]=" &
Format(Me.DateByMonthFilter, conJetDate) & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Please enter information into at least one field before
clicking the filter button.", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Douglas J. Steele]

I'm assuming that DateByMonthFilter also contains a date, since you're
formatting it as a date.

The less efficient method is:

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & _
"(Format([Date Of Service By Month], ""#mmmm yyyy#"")='" & _
Format(Me.DateByMonthFilter, conJetDate) & "') AND "
End If

(note the 2 double quotes around #mmmm yyyy#, and the fact that you cannot
refer to the constant there)

This is less efficient since the function has to run for each row in the
table. Better is:

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month] BETWEEN " & _
Format( _
DateSerial(Year(Me.DateByMonthFilter), Month(Me.DateByMonthFilter),
1), _
conJetDate) & _
" AND " & _
Format( _
DateSerial(Year(Me.DateByMonthFilter), Month(Me.DateByMonthFilter) +
1, 0), _
conJetDate) & _
") AND "
End If

This assumes that Date Of Service By Month only contains dates: no times. If
it does contain times, change that 0 to 1 in the second DateSerial function
call.

(hopefully I've avoided word wrap problems!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Date Of Service By Month is a date field. Let me tell you what I am
trying
to do and maybe you can tell me how you would do it. I want to filter
medical record charts by Database, Location, and Date of Service. Instead
filtering a single date, I want to filter by March 2007, or June 2005,
ect...
I have the Date Of Service By Month formated to show the dates the way I
want, I just need the filter to work.

Assuming that Date Of Service By Month is a text field, you need quotes
around the value you're checking:

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]='" & _
Format(Me.DateByMonthFilter, conJetDate) & "') AND "
End If

Exagerated for clarity, that's

If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]= ' " & _
Format(Me.DateByMonthFilter, conJetDate) & " ' ) AND "
End If

If it's a Date field, then regardless of what you may think, it contains
a
complete date. That means you either need to apply the format to the date
field, or compare the date field to a range. (The latter is better):


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Stuck again. If it werent for this site I would be lost. Thanks to
all
you
MVP's. I have a query that formats a date field to read only the month
and
year that looks like this:

Format$([DateOfService],'mmmm yyyy')

The problem is that I have a form with filters on it and it keeps
giving
me
run-time error 2448, You cant assign a value to this object. I know I
have
my datefilter formated incorrectly. Can someone help, here is the code
I
am
using:

Private Sub Filter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "#mmmm yyyy#"

If Not IsNull(Me.DatabaseFilter) Then
strWhere = strWhere & "([DataBase]=""" & Me.DatabaseFilter & """) AND
"
End If
If Not IsNull(Me.LocationFilter) Then
strWhere = strWhere & "([LocationCode]=" & Me.LocationFilter & ") AND
"
End If
If Not IsNull(Me.DateByMonthFilter) Then
strWhere = strWhere & "([Date Of Service By Month]=" &
Format(Me.DateByMonthFilter, conJetDate) & ") AND "
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "Please enter information into at least one field before
clicking the filter button.", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub