Subform Date Filter

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?

On Wed, 14 Jun 2006 14:06:06 -0600, "phuser"
wrote:

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]

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" wrote in message
...
On Wed, 14 Jun 2006 14:06:06 -0600, "phuser"
wrote:

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]

On Wed, 14 Jun 2006 15:35:39 -0600, "phuser"
wrote:

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]

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

"John Vinson" wrote in message
...
On Wed, 14 Jun 2006 15:35:39 -0600, "phuser"
wrote:

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]

Sorry the Error code recieved was

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


"John Vinson" wrote in message
...
On Wed, 14 Jun 2006 15:35:39 -0600, "phuser"
wrote:

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]