Subform Date Filter

[phuser]

I have a Main Form & Subform, on the Main I have a combo Box with Date Range
criteria, This Week, Last Week... etc.
There is no parent child relationship. Is it possible to date filter the
subform from the Main form? and if so what would the statement be?

 

[John Vinson]

I have a Main Form & Subform, on the Main I have a combo Box with Date Range
criteria, This Week, Last Week... etc.
There is no parent child relationship. Is it possible to date filter the
subform from the Main form? and if so what would the statement be?

You could base the Subform on a query referencing the combo box.
Depending on what's included in etc. you may need a VBA function, or
you might be able to use a big snarky Switch() function as the
criterion, something like

= Switch([Forms]![mainform]![cboDateRange] = "This Week",

DateAdd("d", -Weekday(Date()), Date()),
[Forms]![mainform]![cboDateRange] = "Last Week",
DateAdd("d", -7-Weekday(Date()), Date()),
<etc. etc.>)
AND
< Switch([Forms]![mainform]![cboDateRange] = "This Week",
DateAdd("d", 7-Weekday(Date()), Date()),
[Forms]![mainform]![cboDateRange] = "Last Week",
DateAdd("d", -Weekday(Date()), Date()),
<etc. etc.>)

John W. Vinson[MVP]

 

[phuser]

Totally friggin lost,

That didnt work for me, I added the VB code to the "On Change" for that Main
Form combo but I kept getting syntax errors, what was the "d" for ?
Dont understand what to do in the query either.

Sorry for being such a noob.

I have a Main Form & Subform, on the Main I have a combo Box with Date
Range
criteria, This Week, Last Week... etc.
There is no parent child relationship. Is it possible to date filter the
subform from the Main form? and if so what would the statement be?

You could base the Subform on a query referencing the combo box.
Depending on what's included in etc. you may need a VBA function, or
you might be able to use a big snarky Switch() function as the
criterion, something like

= Switch([Forms]![mainform]![cboDateRange] = "This Week",

DateAdd("d", -Weekday(Date()), Date()),
[Forms]![mainform]![cboDateRange] = "Last Week",
DateAdd("d", -7-Weekday(Date()), Date()),
<etc. etc.>)
AND
< Switch([Forms]![mainform]![cboDateRange] = "This Week",
DateAdd("d", 7-Weekday(Date()), Date()),
[Forms]![mainform]![cboDateRange] = "Last Week",
DateAdd("d", -Weekday(Date()), Date()),
<etc. etc.>)

John W. Vinson[MVP]

 

[John Vinson]

Totally friggin lost,

That didnt work for me, I added the VB code to the "On Change" for that Main
Form combo but I kept getting syntax errors, what was the "d" for ?
Dont understand what to do in the query either.

The AfterUpdate event (as I suggested) would be better than the Change
event (which fires *at every keystroke*, not when a selection is made.

"d" means to calculate the date range in Days (rather than "m" for
Months, "h" for Hours and so on).

Please post your actual code and the error message. Can't help you if
we can't see what you're doing!

John W. Vinson[MVP]

 

[phuser]

Private Sub DateFilter_AfterUpdate()

= Switch([Forms]![frmExport]![DateFilter] = "This Week",
DateAdd("d", -weekday(Date()), Date()), [Forms]![frmExport]![DateFilter] =
"Last Week", DateAdd("d", 7-weekday()), Date())) And
Switch([Forms]![frmExport]![DateFilter] = "This Week", DateAdd("d",
7-Weeday(Date()), Date()), [Forms]![frmExport]![DateFilter] = "Last Week",
DateAdd("d", -Weekday(Date()), Date()))
End Sub

 

[phuser]

Sorry the Error code recieved was

Compile Error:
Expected: line number or label or statement or end of statement