Bind variable field to a text box

[ppataki]

Dear All,

I have a text box on a form, and what I would like to do is to bind not a fix field of the datasource (eg Result2004) to this control, but to bind a field that is a result of an expression
eg. if the value of combo0 is 2004, then bind field Result2004 from the datasource, if the value of combo0 is 2005 then bind field Result2005 etc etc
I tried ="result"&me.combo0, but I got a #Name error on the text box

Could you please advise?

Many thanks in advance

 

[Galaxiom]

The problem is inappropriate database structure.

Years should not be fields but values in a field called ResultYear.
Then simply use the combo value in the Record Source query to select the records with 2005 in the ResultYear field.

What was your plan for the future? Adding a new field each January?
This always indicates the structure is wrong and persisting with it will just keep bringing up problems until you change it.

 

[ppataki]

Hello,
I have already found a solution to the below, however
you are right, but how can I store the data per year if I have hundreds of customers as well? The current setup is that there is a table with a record for each customer and each year new fields are added for the new year's data
Could you please advise?

Many thanks

 

[boblarson]

Hello,
I have already found a solution to the below, however
you are right, but how can I store the data per year if I have hundreds of customers as well? The current setup is that there is a table with a record for each customer and each year new fields are added for the new year's data
Could you please advise?

Many thanks

The key is to NOT add new FIELDS but to add ROWS of data. Adding fields means you have a non-normalized solution and that means that it is hard to do meaningful extractions of data. If you are going to have the field added for each year then you might as well just use a spreadsheet as that is how you're treating Access - as just a spreadsheet and not a relational database system.

 

[Galaxiom]

The records for each customer will be identified by their CustomerID number. The years will be identified by an appropriate field. Any record can be uniquely identified by the CustomerID and Year.

Despite appearances database tables are nothing like spreadsheets. The difference cannot be overstated. Designing tables like spreadseets is the most common mistake made by new developers.

Tables store data. Data related to a particular record can be spread throughout a table or multiple tables. Forms and reports are used to enter and display data. In a spreadsheet both these tasks are combined.