Help, i,m new to Access. I have a query which includes a field that projects a startdate for each record. The problem i have is i need to place a constraint on the number of starts that I can have in a particular week. For example, my query currently shows 10 records in the 51nd week of 2003; however, due to production I can only have 5 starts, once I have 5 starts in a particular week then startdate needs to be the subsequent week 52nd week of 2003. Hope this makes sense. Help
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Count records in each Week
- Thread starter walkerg
- Start date
SydneyGeek
MrExcel MVP
- Joined
- Aug 5, 2003
- Messages
- 12,251
I can only think of a roundabout way of doing this, but it may get you started. It needs 2 queries --
qryFirst has [MyDate] from the same record source as the form, and a calculated field =DatePart("ww",[MyDate]).
In the Criteria row below the calculated field, put DatePart("ww",[Forms]![MyForm]![MyDate]).
At this point you have all records with the same start week as the current record.
qrySecond has qryFirst as its record source. Place the calculated field on the grid, press the Totals (sigma) button on the toolbar and select Count as the summary function.
Now you have a single field that counts all records with the same start week as the current record. Name this field Tally.
Back to the form. New textbox, with the controlsource =[qrySecond]![Tally]. Call this Counter. Check it out to see if it runs.
Validation: You have 2 options.
(1) At form level, you enter an expression which prevents you from saving a record if the new textbox is >5.
(2) You create another textbox, formatted to be flat, with the same background as the form, with bold red text. This can have whatever message you want. Call this Warning. Now to create some code to drive it. In the Form properties, click the Events tab. Double-click the BeforeUpdate event, then click the ... button at the end of the line to go to the code module. Enter this:
Put the same code in the OnCurrent event of the form.
You'll need to change field, query and form names to suit.
Denis
qryFirst has [MyDate] from the same record source as the form, and a calculated field =DatePart("ww",[MyDate]).
In the Criteria row below the calculated field, put DatePart("ww",[Forms]![MyForm]![MyDate]).
At this point you have all records with the same start week as the current record.
qrySecond has qryFirst as its record source. Place the calculated field on the grid, press the Totals (sigma) button on the toolbar and select Count as the summary function.
Now you have a single field that counts all records with the same start week as the current record. Name this field Tally.
Back to the form. New textbox, with the controlsource =[qrySecond]![Tally]. Call this Counter. Check it out to see if it runs.
Validation: You have 2 options.
(1) At form level, you enter an expression which prevents you from saving a record if the new textbox is >5.
(2) You create another textbox, formatted to be flat, with the same background as the form, with bold red text. This can have whatever message you want. Call this Warning. Now to create some code to drive it. In the Form properties, click the Events tab. Double-click the BeforeUpdate event, then click the ... button at the end of the line to go to the code module. Enter this:
Code:
If [Counter] <=5 Then
[Warning].Visible=False
Else
[Warning].Visible=True
End IfYou'll need to change field, query and form names to suit.
Denis
Upvote
0
Similar threads
