user input field name for control source?

[Guest]

I have a number of reports that rely on billing data. Each month I go in and
edit each report and change the month portion of all the field names to
prepare it for the next months billing. For instance, the billing invoice for
January contains fields named [Jan 06 A], [Jan 06 B] etc. So, for Feb, I must
edit the report and change the "Jan" to "Feb" for it to pull the correct
field data. I really don't want to have 12 versions of each report for each
year. We'd be talking 120 reports for 2006 alone!

There has got to be a way that the report can stop and ask you for a
"variable" and you type in the field name and it replaces the variable name
in the Control Source with the field name you provide.

I tried to do this, but it gives the following error:

"The expression is typed incorrectly or it is too complex to be evaluated.
...."

The expression it is referring to is =sum([A]-), where A and B are my
"variables" for the fields named [Jan 06 A] and [Jan 06 B]. I've tried every
combination of ['s and no ]'s, different variable names etc.

I'm testing this with a very basic newly created report. There is nothing
else in the report except the calculation above, and one other field which
displays text (and doesn't change from Month to Month).

I'm probably overlooking something very obvious here. It just seems to me if
I can put all sorts of variables in my other reports that literally add text
to the report (like [Name] asks you for a name when you run the report and
places what you type in the report header, for instance), I don't see why
this won't work.

Thanks in advance.

 

[Duane Hookom]

Do you have fields with names like [Jan 06 A] or is this the result of a
crosstab?

 

[Guest]

No, no crosstab. The fields are coming directly from a table of billing data.
The only portion of the field that changes every month is the 3 letter month
portion. The rest of the field name for each field is identical every month.

I'm saving my reports each month as Snapshot files so we can go back and
look at the data if needed (instead of modifying the report again). There
just seems to be something I'm missing here about replacing the field names.

Thanks in advance!

Do you have fields with names like [Jan 06 A] or is this the result of a
crosstab?

--
Duane Hookom
MS Access MVP
--

I have a number of reports that rely on billing data. Each month I go in
and
edit each report and change the month portion of all the field names to
prepare it for the next months billing. For instance, the billing invoice
for
January contains fields named [Jan 06 A], [Jan 06 B] etc. So, for Feb, I
must
edit the report and change the "Jan" to "Feb" for it to pull the correct
field data. I really don't want to have 12 versions of each report for
each
year. We'd be talking 120 reports for 2006 alone!

There has got to be a way that the report can stop and ask you for a
"variable" and you type in the field name and it replaces the variable
name
in the Control Source with the field name you provide.

I tried to do this, but it gives the following error:

"The expression is typed incorrectly or it is too complex to be evaluated.
..."

The expression it is referring to is =sum([A]-), where A and B are my
"variables" for the fields named [Jan 06 A] and [Jan 06 B]. I've tried
every
combination of ['s and no ]'s, different variable names etc.

I'm testing this with a very basic newly created report. There is nothing
else in the report except the calculation above, and one other field which
displays text (and doesn't change from Month to Month).

I'm probably overlooking something very obvious here. It just seems to me
if
I can put all sorts of variables in my other reports that literally add
text
to the report (like [Name] asks you for a name when you run the report and
places what you type in the report header, for instance), I don't see why
this won't work.

Thanks in advance.

 

[Duane Hookom]

I wouldn't spend much more time without first normalizing the billing data
table. Is that a possibility?

--
Duane Hookom
MS Access MVP
--

No, no crosstab. The fields are coming directly from a table of billing
data.
The only portion of the field that changes every month is the 3 letter
month
portion. The rest of the field name for each field is identical every
month.

I'm saving my reports each month as Snapshot files so we can go back and
look at the data if needed (instead of modifying the report again). There
just seems to be something I'm missing here about replacing the field
names.

Thanks in advance!

Do you have fields with names like [Jan 06 A] or is this the result of a
crosstab?

--
Duane Hookom
MS Access MVP
--

I have a number of reports that rely on billing data. Each month I go in
and
edit each report and change the month portion of all the field names to
prepare it for the next months billing. For instance, the billing
invoice
for
January contains fields named [Jan 06 A], [Jan 06 B] etc. So, for Feb,
I
must
edit the report and change the "Jan" to "Feb" for it to pull the
correct
field data. I really don't want to have 12 versions of each report for
each
year. We'd be talking 120 reports for 2006 alone!

There has got to be a way that the report can stop and ask you for a
"variable" and you type in the field name and it replaces the variable
name
in the Control Source with the field name you provide.

I tried to do this, but it gives the following error:

"The expression is typed incorrectly or it is too complex to be
evaluated.
..."

The expression it is referring to is =sum([A]-), where A and B are
my
"variables" for the fields named [Jan 06 A] and [Jan 06 B]. I've tried
every
combination of ['s and no ]'s, different variable names etc.

I'm testing this with a very basic newly created report. There is
nothing
else in the report except the calculation above, and one other field
which
displays text (and doesn't change from Month to Month).

I'm probably overlooking something very obvious here. It just seems to
me
if
I can put all sorts of variables in my other reports that literally add
text
to the report (like [Name] asks you for a name when you run the report
and
places what you type in the report header, for instance), I don't see
why
this won't work.

Thanks in advance.

 

[Guest]

OK, stupid question... what do you mean "normalizing"?

Jennie

I wouldn't spend much more time without first normalizing the billing data
table. Is that a possibility?

--
Duane Hookom
MS Access MVP
--

No, no crosstab. The fields are coming directly from a table of billing
data.
The only portion of the field that changes every month is the 3 letter
month
portion. The rest of the field name for each field is identical every
month.

I'm saving my reports each month as Snapshot files so we can go back and
look at the data if needed (instead of modifying the report again). There
just seems to be something I'm missing here about replacing the field
names.

Thanks in advance!

Do you have fields with names like [Jan 06 A] or is this the result of a
crosstab?

--
Duane Hookom
MS Access MVP
--

I have a number of reports that rely on billing data. Each month I go in
and
edit each report and change the month portion of all the field names to
prepare it for the next months billing. For instance, the billing
invoice
for
January contains fields named [Jan 06 A], [Jan 06 B] etc. So, for Feb,
I
must
edit the report and change the "Jan" to "Feb" for it to pull the
correct
field data. I really don't want to have 12 versions of each report for
each
year. We'd be talking 120 reports for 2006 alone!

There has got to be a way that the report can stop and ask you for a
"variable" and you type in the field name and it replaces the variable
name
in the Control Source with the field name you provide.

I tried to do this, but it gives the following error:

"The expression is typed incorrectly or it is too complex to be
evaluated.
..."

The expression it is referring to is =sum([A]-), where A and B are
my
"variables" for the fields named [Jan 06 A] and [Jan 06 B]. I've tried
every
combination of ['s and no ]'s, different variable names etc.

I'm testing this with a very basic newly created report. There is
nothing
else in the report except the calculation above, and one other field
which
displays text (and doesn't change from Month to Month).

I'm probably overlooking something very obvious here. It just seems to
me
if
I can put all sorts of variables in my other reports that literally add
text
to the report (like [Name] asks you for a name when you run the report
and
places what you type in the report header, for instance), I don't see
why
this won't work.

Thanks in advance.

 

[Duane Hookom]

When you have "contains fields named [Jan 06 A], [Jan 06 B] etc." it
suggests you are storing data values in your field names. "Jan 06" is a
value that should be contained in a date field. I assume the "A" and "B" are
also significant data values that should be stored in an Account or Category
field.

For instance assuming you have this data

Dept Jan06A Jan06B
===== ======== =======
HR 1,234 1,345
FN 3,456 4,123
OP 5,146 3,943

This might be better stored in a table like:

Dept BdgtDate Cat Amt
===== ========= ===== ======
HR 1/1/2006 A 1,234
HR 1/1/2006 B 1,345
FN 1/1/2006 A 3,456
FN 1/1/2006 B 4,123
OP 1/1/2006 A 5,146
OP 1/1/2006 B 3,943

There are some great links on this on Jeff Conrad's site
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
--
Duane Hookom
MS Access MVP
--

OK, stupid question... what do you mean "normalizing"?

Jennie

I wouldn't spend much more time without first normalizing the billing
data
table. Is that a possibility?

--
Duane Hookom
MS Access MVP
--

No, no crosstab. The fields are coming directly from a table of billing
data.
The only portion of the field that changes every month is the 3 letter
month
portion. The rest of the field name for each field is identical every
month.

I'm saving my reports each month as Snapshot files so we can go back
and
look at the data if needed (instead of modifying the report again).
There
just seems to be something I'm missing here about replacing the field
names.

Thanks in advance!

:

Do you have fields with names like [Jan 06 A] or is this the result of
a
crosstab?

--
Duane Hookom
MS Access MVP
--

I have a number of reports that rely on billing data. Each month I go
in
and
edit each report and change the month portion of all the field names
to
prepare it for the next months billing. For instance, the billing
invoice
for
January contains fields named [Jan 06 A], [Jan 06 B] etc. So, for
Feb,
I
must
edit the report and change the "Jan" to "Feb" for it to pull the
correct
field data. I really don't want to have 12 versions of each report
for
each
year. We'd be talking 120 reports for 2006 alone!

There has got to be a way that the report can stop and ask you for a
"variable" and you type in the field name and it replaces the
variable
name
in the Control Source with the field name you provide.

I tried to do this, but it gives the following error:

"The expression is typed incorrectly or it is too complex to be
evaluated.
..."

The expression it is referring to is =sum([A]-), where A and B
are
my
"variables" for the fields named [Jan 06 A] and [Jan 06 B]. I've
tried
every
combination of ['s and no ]'s, different variable names etc.

I'm testing this with a very basic newly created report. There is
nothing
else in the report except the calculation above, and one other field
which
displays text (and doesn't change from Month to Month).

I'm probably overlooking something very obvious here. It just seems
to
me
if
I can put all sorts of variables in my other reports that literally
add
text
to the report (like [Name] asks you for a name when you run the
report
and
places what you type in the report header, for instance), I don't
see
why
this won't work.

Thanks in advance.

 

[Guest]

Don't know if you solved this yet, but I have an idea; where ever your month
field exists - create an unbound text box, in the properties of the text box,
in the Control Source field, enter [Type the month]

This will prompt a parameter, and you can type what ever you want, eg. Jan
where ever this text box is it will fill the word Jan.