Rename report fields and linked data fields via VB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JR_06062005 - 14 Sep 2006 20:32 GMT

I have a report that is linked to a query which has around 52 fields that
change when the fiscal years changes. For example the fields may be
txtFY06sales, txtFY06inventory, etc.... Also the linked data fields in the
query are similarly named. Changing the query is no problem, but changing 52
fields in a report(actually it may be more then 52) is very labor intensive.

Is there an easier way to make the changes rather going into each field and
manually changing them? I was thinking that VB may offer an easier solution,
but I don't know how to do it in VB. I realize I should not have designed
the report like I did, but the fiscal year is ending very soon and I don't
have time to redesign the report and the system that support it. I would
really appreciate any help offered.

Reply to this Message

Marshall Barton - 14 Sep 2006 21:29 GMT

>I have a report that is linked to a query which has around 52 fields that
>change when the fiscal years changes. For example the fields may be
[quoted text clipped - 8 lines]
>have time to redesign the report and the system that support it. I would
>really appreciate any help offered.

You're right, that is one ugly design. The least you should
do is modify the query to alias the fields to a constant
name such as txtFYsales. E.g. each field in the query
design frid would look likeL
txtFYsales: txtFY06sales
This way the report doesn't have to know what year it is and
the bound fields wouldn't be changing every year.

When you get the time, you should redesign your table so
that it has a FY field and a set ot value fields instead of
a wild list ot fields for each FY.

Signature

Marsh
MVP [MS Access]

Reply to this Message

JR_06062005 - 18 Sep 2006 13:13 GMT

Thnaks, using the alias with the query is a good ideas, but my real problem
is not the query, but the name of the fields on the report itself. The name
of the field on the report is something like txtSales0601 where the 06
represents the fiscal year and the 01 the pay period. The pay periods do not
change from year to year. There will always be 26 pay periods. Is there a
way, using VB, to change the name of the field, for example, from
txtSales0601 to txtSales01. The reason I set up the report that way in the
first place is when someone exports to Excel the field name becomes the
column header, which is convenient. Well, it's convenient until the fiscal
year changes.

> >I have a report that is linked to a query which has around 52 fields that
> >change when the fiscal years changes. For example the fields may be
[quoted text clipped - 20 lines]
> that it has a FY field and a set ot value fields instead of
> a wild list ot fields for each FY.

Reply to this Message

Marshall Barton - 18 Sep 2006 15:19 GMT

I understand what you are saying, but trying to mess with
the report is not the solution.

The real problem is the name of the fields in the table.
You should never have information in a table field name.

As I said before, the proper way to design your tables is to
have one field for the year, another for the pay period, and
others for the data associated with the pay period. In
general, your tables should be tall and narrow, not wide and
short. Someone once explained this by saying that table
fields are very expensive, but rows are cheap.

Bottom line, I don't think there is a reasonable way to
twist the report into dealing with your current table/query
structure.

Signature

Marsh
MVP [MS Access]

>Thnaks, using the alias with the query is a good ideas, but my real problem
>is not the query, but the name of the fields on the report itself. The name
[quoted text clipped - 31 lines]
>> that it has a FY field and a set ot value fields instead of
>> a wild list ot fields for each FY.

Reply to this Message