Subform Filter problem. Please Help Me!

[2e1fmo]

Hi,

Thanks to those who have already tried to help me with this one.

My problems is trying to filter a subform which contains the following fields

[Date] [TimeStart] and [TimeEnd]

The [Date] field contains the date in the format 04/04/02. I need to filter the subform by hitting a command button. The filter needs to display only records occuring in the current week.

I have this code so far:

Private Sub Command170_Click()
Dim strCond2 As String
Dim FirstDay As String
Dim LastDay As String

FirstDay = Date - WeekDay(Date, 0) + 1
LastDay = DateAdd("w", 4, FirstDay)

strCond2 = "Date > " & FirstDay

[Forms]![Staff]![Child144].Form.FilterOn = True
[Forms]![Staff]![Child144].Form.Filter = strCond2

The variable FirstDay has been set to the date of the monday of the current week, in this weeks case "08/04/02". LastDay has been set to Fridays date for this current week.

strCond2 sets up what I would have thought would work as an expression for the filter. Taking the field Date and selecting anything greater than Mondays Date.

Child144 being the subform name.

When I click the command button, nothing is filtered. Can anybody help me with Code changes if possible.

Many thanks in advance

Daniel.

 

[Rich]

You need to rename your field called Date to something else before you can do anything, it's a reserved word in access and will cause you problems.

 

[2e1fmo]

Had wondered about that before Rich, I think I've found a solution that works thou, keeping the field named Date. (It causes alot of other coding problems to rename Date. The filter condition which seems to work ok is:

FirstDay = Date - WeekDay(Date, 0) + 1
LastDay = DateAdd("w", 4, FirstDay)
FirstDay1 = DatePart("d", FirstDay)
FirstDay2 = DatePart("m", FirstDay)

strCond2 = "datepart(""d"", [date]) >=" & FirstDay1 & " And datepart(""m"", [date]) = " & FirstDay2.

This seems to work ok. Thanks

 

[Rich]

It's likely to cause you more problems if you don't change it