How to automatically have the 1st day of the week entered in a text box on a report?

[Daniel B]

I have a report that generates the total hours worked by each crew member. When the report is opened, it asks you to enter the starting and ending date of the time period that members of the crew have worked in order to generate the total amount of hours. I also have a text box labeled "week starting" and then the date.

The problem is, that when the report is generated the "week starting" date is not always the first day of that week, but rather the first day that the first crew member on the list worked. I am not very familiar with the database I am dealing with, so I do not how that date is being generated.

If you have any suggestions or advice it would be greatly appreciated. Also if you need more of an explanation I would be happy to elaborate.

Thanks, Dan B

[Rabbit]

Assuming that you mean Monday when you say 1st day of the week, you can calculate it by taking the day of the week you're on, adding 5, modding by 7, and then subtracting the current day by that many days.

x = current day of week
y = monday of that week
d = current date
 
y = d - ((x + 5) Mod 7)

[Daniel B]

Thank you for your help. However, Monday is not always the first day of the week. The company for which this DB was built sometimes has shutdown days on Mondays every other month or so.

As I said in my first thread, the parameters for entering the starting date of the period are usually going to be the first day of that week. I am wondering if once entering that start date, could I make it automatically fill in this other date as the one that was entered?

[Rabbit]

You can take the min date of the dates entered where the week number and year of the date is equal to the current date.

[NeoPa]

Daniel, what are you actually asking for here? It seems you already have a TextBox control that reflects the date of the first day of the week. Clearly then, you're not asking for that (which was the impression from the question), so what is it you require?

[Daniel B]

Okay.. when the report is opened you have to enter a start date and ending date. I would like to have this "week starting" textbox display the same date as the entered start date.

[NeoPa]

Ah. That makes more sense now. You would like to get access to the value entered into the query that the report is bound to. I'm not sure that is possible the way you have it designed (as a parameter in your query). I'm open to being proven wrong here. There may be a dataset object for the report that contains the parameters and their current values, but I don't know of it being able to be used in that context (and I wouldn't hold your breath).

An alternative (and more usual) design is to enter these values into controls on your form instead and open the report using a filter made up from the data in these controls. That way the code within the report can get the info by either :

1. Referring to the controls on the form.
2. Parsing its .Filter property for both values.