Subform Date Filter

  • Thread starter Thread starter phuser
  • Start date Start date
P

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?
 
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?
Click to expand...

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",
Click to expand...
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]
 
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.


John Vinson said:
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?
Click to expand...

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",
Click to expand...
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]
Click to expand...
 
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.
Click to expand...

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]
 
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
 
Sorry the Error code recieved was

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