Multiple Date Criteria on Form

[Adams]

I would like to have 4 different date criteria'a set up on a form. For example: Jan(Month) Week1, Week2, Week3 and Week4. I can get the first results Jan, Week1 with the following code.

PrivateSub cmbChoose_AfterUpdate()
Me!Week=Format(cmb.choose.column(1)
Me!Begin=Format(cmb.choose.column(2)
Me!End=Format(cmb.choose.column(3)
EndSub

How do I go about getting Week2, Week3 or Week4 on the same form?

Thanks
Karen

 

[MinusM]

The post is not very clear. I guess Week1, Week2, Week3 and Week4 are entries in the combobox.
If so
for i = 0 to cmb.listcount
Me!Week=Format(cmb.choose.column(1,i)
Me!Begin=Format(cmb.choose.column(2,i)
Me!End=Format(cmb.choose.column(3,i)

next

Best of luck

 

[Adams]

Sorry I wasn't very clear with what I need. I am creating a form that will have 4 different date criteria's. Then I would be able to "query by form".

Example

Jan Week1
Jan Week2
Jan Week3
Jan Week4

Then next month will be Feb and so on. Depending on when the query is ran Jan Week1 might only have totals. Then Week2 - Week4 would be blank. I hope that I have not confused you!

Karen

 

[MinusM]

You have not confused me but it is not much clearer either

What do you mean by 4 different criteria?
Are they Week1, Week2, Week3 and Week4
I was assuming Criteria means condition for filtering

What do you want to achieve in the end?
Are you building up an SQL for filtering?

Or is it just that you want to display the results in Week1, Week2, Week3 and Week4 as fields?

If that is so
I assume the month is selcted on some basis, either data entered or Current Month

Then you should have 4 Text boxes with the control source as
"=Whatever calculation between Me!Begin and Me!End and between Week0 and Week1"

Each one will have different controlsource with Week numbers differing

Hope I am clear enough. I really doubt this %-)

Best of luck

 

[Adams]

What I am trying to achieve in the end is to select upto 4 different time periods on a form. Yes, then I would be filtering out the dates in the query based on that time period >=[Forms]![frmReport]![Begin] and <=[Forms]![frmReports]![End]

 

[Zion7]

Hi Karen,
Not sure myself if I've understood enirely.
You want a combo box with 4 options. Each option is a week in January. (as Jan has 4 weeks, Feb has 4, Mar...).
Sounds like ultimately, you will need 52 options. (52 weeks in a year).
1st I would have a combobox for the 12 mths. Make it a Value List, Jan;Feb;Mar;etc...
Another combo for the start of each week.
have the &quot;weekstart&quot; combo, have a value list 1-31.
The user chooses which number corresponds to the start of the week. Now, on the after update event of the &quot;weekstart&quot; combo, have the &quot;weekend&quot; textbox, simply equals 5 + the &quot;weekstart&quot; combo.
You know have your criteria. (I believe?)
Careful on the SQL, the Month may need to be a number, not a name, you can modify the &quot;Month&quot; combo to deal with this.
Have 2 columns, have the 1st column bound, with a number for each month. Make your column widths = 0;1.
Now your users see the name of the month as opposed to the number, but your SQL receives a number. etc...
Hope Karen, this helps?