Fiscal Week Number for Label and to be used in a Formula

[Dan]

I have inherited a report that someone else created some
time back. The report is being used to determine how many
hours were applied each week to a project. In the report
header is the text "Through Fiscal Week 40" and in the
detail section of the report, it takes the sum of the
hours worked and divides it by a hard coded 40 for several
fields.

Every month I have to go in and change the 40 to what ever
the next Fiscal Week end is for the month in the label in
all of the equations.

I don't want to do this manually anymore, is there a way
that I can have a paramter or variable of some sort that I
can set in the report to 40, and it automatically replace
it in the label and all of the equations?

Seems like this should be pretty simple, but I am new to
Access Reports and don't know where to turn.

Thanks in advance

 

[Marshall Barton]

I have inherited a report that someone else created some
time back. The report is being used to determine how many
hours were applied each week to a project. In the report
header is the text "Through Fiscal Week 40" and in the
detail section of the report, it takes the sum of the
hours worked and divides it by a hard coded 40 for several
fields.

Every month I have to go in and change the 40 to what ever
the next Fiscal Week end is for the month in the label in
all of the equations.

I don't want to do this manually anymore, is there a way
that I can have a paramter or variable of some sort that I
can set in the report to 40, and it automatically replace
it in the label and all of the equations?

Seems like this should be pretty simple, but I am new to
Access Reports and don't know where to turn.

It can be trivially simple, if not particularly elegant, by
replacing the 40 in each of the text box expressions with a
prompt string in square brackets.

="Through Fiscal Week " & [Enter Week Hours]
=Sum(somefield) / [Enter Week Hours]

When you open the report, a pop up dialog box will prompt
you for a value and then use that value where ever a text
box expression uses the prompt string.

 

[Guest]

I don't get prompted for the [Enter Week Hours] when I
open the report, I get the error that MS Jet DB Engine
does not recognize [Enter Week hours] so I must still be
doing something wrong.

-----Original Message-----

I have inherited a report that someone else created some
time back. The report is being used to determine how many
hours were applied each week to a project. In the report
header is the text "Through Fiscal Week 40" and in the
detail section of the report, it takes the sum of the
hours worked and divides it by a hard coded 40 for several
fields.

Every month I have to go in and change the 40 to what ever
the next Fiscal Week end is for the month in the label in
all of the equations.

I don't want to do this manually anymore, is there a way
that I can have a paramter or variable of some sort that I
can set in the report to 40, and it automatically replace
it in the label and all of the equations?

Seems like this should be pretty simple, but I am new to
Access Reports and don't know where to turn.

It can be trivially simple, if not particularly elegant, by
replacing the 40 in each of the text box expressions with a
prompt string in square brackets.

="Through Fiscal Week " & [Enter Week Hours]
=Sum(somefield) / [Enter Week Hours]

When you open the report, a pop up dialog box will prompt
you for a value and then use that value where ever a text
box expression uses the prompt string.

 

[Guest]

Figured out something, maybe not what you were refering
to, but I had to put the [Enter Week Hours] in the query.

-----Original Message-----
I don't get prompted for the [Enter Week Hours] when I
open the report, I get the error that MS Jet DB Engine
does not recognize [Enter Week hours] so I must still be
doing something wrong.

-----Original Message-----

that

I


It can be trivially simple, if not particularly elegant, by
replacing the 40 in each of the text box expressions

with

a
prompt string in square brackets.

="Through Fiscal Week " & [Enter Week Hours]
=Sum(somefield) / [Enter Week Hours]

When you open the report, a pop up dialog box will prompt
you for a value and then use that value where ever a text
box expression uses the prompt string.

.

 

[Marshall Barton]

Interesting(?)

While that will certainly work, just placing the prompt
string (inside square brackets) in a text box expression has
always worked for me. But, whatever works, go with it.
--
Marsh
MVP [MS Access]

Figured out something, maybe not what you were refering
to, but I had to put the [Enter Week Hours] in the query.

-----Original Message-----
I don't get prompted for the [Enter Week Hours] when I
open the report, I get the error that MS Jet DB Engine
does not recognize [Enter Week hours] so I must still be
doing something wrong.

Dan wrote:
I have inherited a report that someone else created some
time back. The report is being used to determine how many
hours were applied each week to a project. In the report
header is the text "Through Fiscal Week 40" and in the
detail section of the report, it takes the sum of the
hours worked and divides it by a hard coded 40 for several
fields.

Every month I have to go in and change the 40 to what ever
the next Fiscal Week end is for the month in the label in
all of the equations.

I don't want to do this manually anymore, is there a way
that I can have a paramter or variable of some sort

that

I
can set in the report to 40, and it automatically replace
it in the label and all of the equations?

Seems like this should be pretty simple, but I am new to
Access Reports and don't know where to turn.


It can be trivially simple, if not particularly elegant, by
replacing the 40 in each of the text box expressions

with

a
prompt string in square brackets.

="Through Fiscal Week " & [Enter Week Hours]
=Sum(somefield) / [Enter Week Hours]

When you open the report, a pop up dialog box will prompt
you for a value and then use that value where ever a text
box expression uses the prompt string.