Unbound Combobox Filter By Date Criteria

[burrina]

I have a unbound combobox on my form that I need to filter by date range. I am using a Value List as the record source.


The combobox is name Combo63 and here is it's Row Source: It is a Value List
The Date I wish to use as the criteria is a date field with NO format assigned and no input mask. EventStart is the control date field.


"All";"Today";"This Week";"Last Week";"This Month";"Last Month";"Next Month"


How can I filter the form with this criteria?

Here is all my code to try and do it. Of course I get an error with so?..

If [Forms]![frmEvent]![Filter]="Today" Then
(Year([nextschddte])=Year(Date()) And Month([nextschddte])=Month(Date()) And Day([nextschddte])=Day(Date()))

If [Forms]![frmEvent]![Filter]="This Week"
(Year([nextschddte])=Year(Date()) And DatePart("ww",[nextschddte],0)=DatePart("ww",Date(),0))

If [Forms]![frmEvent]![Filter]="Last Week"
(Year([nextschddte])=Year(Date()) And Month([nextschddte])=Month(Date()))

If [Forms]![frmEvent]![Filter]="Last Month" Then
(Year([nextschddte])=Year(Date()) And Month([nextschddte])=(Month(Date())-1))

If [Forms]![frmEvent]![Filter]="Next Month".
(Year([nextschddte])=Year(Date()) And Month([nextschddte])=(Month(Date())+1))

If [Forms]![frmEvent]![Filter]="All"
DoCmd.ShowAllRecords

[June7]

Try:

Select Case Me.Combo63
Case "Today"
Me.Filter = "[nextschddte]=Date()"
Case "This Week"
Me.Filter="[nextschddte] BETWEEN Date()-7 AND Date()"
Case "Last Week"
Me.Filter="[nextschddte] BETWEEN Date()-14 AND Date()-7"
Case "Last Month"
Me.Filter="Month([nextschddte]) & Year([nextschddte]) = Month(Date()-1 & Year(Date())"
Case "Next Month"
Me.Filter="Month([nextschddte]) & Year([nextschddte]) = Month(Date()+1 & Year(Date())"
Case "All"
DoCmd.ShowAllRecords
End Select
Me.FilterOn

[burrina]

It does not like that Me.FilterON I put the code on the OnClickEvent, is this correct?

[June7]

Good catch, sorry for slip up. Glad it worked otherwise.

[burrina]

Thanks Again.

[June7]

Another oops. The Last and Next Month probably won't work if crosses years. Try instead:

Case "Last Month"
Me.Filter="Month([nextschddte]) & Year([nextschddte]) = Month(DateAdd("m",-1,Date())) & Year(DateAdd("m",-1,Date()))"
Case "Next Month"
Me.Filter="Month([nextschddte]) & Year([nextschddte]) = Month(DateAdd("m",+1,Date())) & Year(DateAdd("m",+1,Date()))"

[burrina]

syntax error with that, don't know what it is???

[June7]

Rats, try apostrophes instead of quotes around "m".

Another version:

="Format([nextschddte],'mmyyyy') = Format(DateAdd('m',-1,Date()),'mmyyyy')"

="Format([nextschddte],'mmyyyy') = Format(DateAdd('m',+1,Date()),'mmyyyy')"

[burrina]

Yep, no errors this time.. Thank You.