Display date ranges in subform

[bishop0071]

I have tblWO_Points and that is included in frmClients as a subform, frmClientsSubform. I have a Date_WO field that list dates (Monday, January 1, 2013) I would like to add a form control within frmClients, where I can have a first date and second date range. I would like it so that my date ranges will always be default for the Saturdays as first date range and Fridays as second date range. Currently my frmClientsSubform list all the dates for each client.

First Date: Saturday, January 19, 2013


Second Date: Friday, January 25, 2013

[June7]

You have text field with value like 'Monday, January 1, 2013'? Why not a date/time field with 1/1/2013 value? If you want data treated like a date, should be in a date/time field and stored as a date value. Then there are functions specifically for working with date values.

To calculate the date of Saturday for the current week:

Date()-WeekDay(Date(),vbSaturday)+1

Date of Friday for the current week:

Date()-WeekDay(Date(),vbSaturday)+7

[bishop0071]

They are date/time fields. Sorry did not mean to confuse you about that. How do I set those formulas in the frmClients so it brings up those dates in the frmClientsSubForm?

Thanks

[June7]

You can use those expressions in the DefaultValue property of control.

[bishop0071]

Can't get it to work. Keep getting #Name? error

[June7]

Sorry, will have to use 7 in place of vbSaturday.

vbSaturday is a constant for use in VBA procedures.

Date()-Weekday(Date(),7)+1

[bishop0071]

The dates shows Saturdays and Fridays as default but in the subform it still shows dates for all records. Also when I change the first date it will still show the first date of the record but the second date comes out correct.

Example: If I put in 1/18 as first date the first record in subform always appear which starts at 1/14.

[June7]

The DefaultValue property will populate field when initiating a new record. Don't understand what you mean by 'it still shows dates for all records'? Don't understand what you want to do. Please describe in detail the behavior you want.

[bishop0071]

When I open the frmClients I want to see Saturday-Friday records in the subform if there are any. Under a Field2 I am only allowing them to accumulate 16 points max. So instead of creating date search fields where I have to pick Saturday and Friday dates every time I look at a new record.

[June7]

You want to use these date values as criteria to retrieve records that fall within the two dates?

If you want form to display only records meeting those date criteria, then must apply those dates as filter criteria. Several ways to accomplish:

1. Put parameter in query used as form RecordSource in criteria row under date field: BETWEEN Date()-Weekday(Date(),7)+1 AND Date()-Weekday(Date(),7)+7

2. Put the criteria in form Filter property: [fieldname] BETWEEN Date()-Weekday(Date(),7)+1 AND Date()-Weekday(Date(),7)+7

3. For dynamic filtering, have UNBOUND textboxes in form Header section for entering date values, DefaultValue property can populate the textboxes. Code in button Click event to execute filter