I am trying to filter a subform with an option group I have on the
mainform (yes, I know a combobox is better, but this is to help people
who have less knowledge of computers). The field I am trying to filter
is a date field on the subform. Here is the code I have so far.
tblTimeCard is the table behind the subform. It appears that 3 of my 4
options are working just fine. Its calculating the week that I am
having problems with.
Option 1 = Today
Option 2 = Week
Option 3 = Month
Option 4 = Year

Dim strWhere As String
Dim LngLen as Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

Select Case Me.Frame64
Case 1
strWhere = "(Year([tblTimeCard].[WorkDay]))=Year(Date()) AND
(Month([tblTimeCard].[WorkDay]))=Month(Date()) AND (Day([tblTimeCard].
[WorkDay]))=Day(Date())"

Case 2
strWhere = "(Year([tblTimeCard].[WorkDay])=Year(Date()) AND
(DatePart("ww",[tblTimeCard].[WorkDay],0)=DatePart("ww",Date(),0))"
' I may not have gotten all the parenthesis correct, but I am
assuming it is correct in my code

Case 3
strWhere = "(Year([tblTimeCard].[WorkDay]))=Year(Date()) AND
(Month([tblTimeCard].[WorkDay]))=Month(Date())"

Case 4
strWhere = "(Year([tblTimeCard].[WorkDay]))=Year(Date())"

End Select

lngLen = Len(strWhere)
strWhere = Left$(strWhere, lngLen)
Me.Time_Cards_Subform.Form.Filter = strWhere
Me.Time_Cards_Subform.Form.FilterOn = True

Like I said, it just seems to be the "Case 2" that is having the error
"Compile error: syntax error" message. Any help would be appreciated.
Thanks.

WhathaveIdone? wrote:


I think you are lacking another set of quotes around the ww. Ex:
strWhere = "(Year([tblTimeCard].[WorkDay])=Year(Date()) AND " & _
"(DatePart(""ww"",[tblTimeCard].[WorkDay],0)=DatePart(""ww"",Date(),0))"
Debug.Print strWhere

On Oct 28, 8:18*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Thank you so much!!! That was it!! Perfect simple solution. None of
the examples I had seen on the DatePart function said anything about
that. Thanks again!!