Reading Field Names from Labels (headings)

[Yvette T]

I have forms and reports that will need to vary over time as far as what
fields they will show. My fields are all annual numbers. Let's take the
easiest:
Field names = [2006], [2007], [2008] etc.

The user will input what years he/she wants the form/report to have. If
they want to begin with "[2007]" then I am going to change the first column
heading's (Label_name) to "2007" and then I wp=ould like the text boxes below
it to look at the label and convert the text "2007" to the field name [2007].

I wrote this but is does not work:

Function LabelField(LName As String) As Field
'this is used to look at a column heading and extract the correct year for
the form/report
LabelField = Label
End Function

HELP!!!! Thanks YVette

 

[Ron2006]

In the onLoad event

me.lblFirst.caption = ----- load it with the value that you have
saved somewhere
me.lblSecond.caption = me.lblFirst.caption -1
me.lblThird.caption = me.lblSecond.caption - 1


The fieldnames can be the same always, just the caption of the labels
need to be changed.


Ron

 

[John W. Vinson]

On Thu, 6 Mar 2008 07:08:01 -0800, Yvette T <Yvette

I have forms and reports that will need to vary over time as far as what
fields they will show. My fields are all annual numbers.

Then your table design IS WRONG.

This is a rational approach for a spreadsheet, but is simply incorrect design
for a relational database. Storing data in fieldnames (or tablenames either,
some people have *tables* named 2006, 2007 etc.) violates the basic principles
of database design! Years, dates, etc. *are data* and belong in fields, not in
fieldnames.

"Fields are expensive, records are cheap". Your table should have multiple
RECORDS for the different years, not fields. With your current design you will
need to change your Table, all your Queries, all your Forms, all your Reports
every time you run out of predefined years in your table. If you treat the
year as data, your job is MUCH simpler.

 

[Ron2006]

I went back and reread your request and

If those are indeed field names then my comments do not really apply
AND John's do.

I have a report form that looks like it does that but in reality it is
summing records that have a date

1) A control date is entered by the user:
2) On that same form are 4 other hidden controls
3) When they request the report I load those 4 controls with the date
that represents the prior 4 business days.
4) I execute a SUM / group by query that has 5 fields defined with
iif(date = cntrl1,accumfieldname,0) etc for the others
5) In the on load event of the subform based on that query I move
those 5 controls to the lbl.caption for the 5 field names that are the
accumulations for that particular date.

This gives me more control than a crosstab since I may not have data
for a date that should have data.

The table being summed has multiple orders for multiple people, for
multiple dates. as rows.

Ron