Calculated Fields

[Jules]

I've been reading past messages on calculated fields and realize the general
advice is dont!!... However I need to hold a nominal ledger code against a
record which changes according to branch and charge and which should change
if the branch of department is changed. ie the nominal code is 85MAbbdd8501
where 'bb' is branch ( for which I have a field in table and form) and 'dd'
is department (field assigned). The idea is that the calculation will
concatenate the nominal code.

 

[Bruce]

I think the advice was against a calculated field in a table. You can
calculate in queries or in text boxes with no problem. Base the form on the
query rather than the table, or calculate directly in a text box on the form.
I prefer queries when possible, because all I need to do is bind a text box
to the field. To calculate in a query, in design view go to a blank column
and type something like FullValue: [bb]&[dd]. For concatenating a name, it
would be something like FullName: [First] & " " & [Last]. If a record
contains all of the information needed to perform the calculation, storing a
calculated field is probably redundant.

 

[Jules]

I would prefer to have this field in the table so that I can run
reports/queries using this calculated field - majority of the reports will be
by nominal code - r u saying that I should creat the query and then run the
report using the query and the table?

I think the advice was against a calculated field in a table. You can
calculate in queries or in text boxes with no problem. Base the form on the
query rather than the table, or calculate directly in a text box on the form.
I prefer queries when possible, because all I need to do is bind a text box
to the field. To calculate in a query, in design view go to a blank column
and type something like FullValue: [bb]&[dd]. For concatenating a name, it
would be something like FullName: [First] & " " & [Last]. If a record
contains all of the information needed to perform the calculation, storing a
calculated field is probably redundant.

I've been reading past messages on calculated fields and realize the general
advice is dont!!... However I need to hold a nominal ledger code against a
record which changes according to branch and charge and which should change
if the branch of department is changed. ie the nominal code is 85MAbbdd8501
where 'bb' is branch ( for which I have a field in table and form) and 'dd'
is department (field assigned). The idea is that the calculation will
concatenate the nominal code.

 

[John Vinson]

I would prefer to have this field in the table so that I can run
reports/queries using this calculated field - majority of the reports will be
by nominal code - r u saying that I should creat the query and then run the
report using the query and the table?

A Report can be - and usually WILL be - based on a Query, not directly
on a Table. You may be making the common assumption that data must be
stored in a table in order to report it; that assumption is simply
*wrong*.

Create the Query with the calculated field. Then create the Report,
using that Query as the recordsource for the report. You don't need to
"use the query and the table" - the Query will contain both the fields
pulled directly from the table, and also the calculated fields.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps