Calculated controls

[bbrazeau via AccessMonster.com]

Ok, Calculated controls. Someone correct me if I’m wrong.
1). Do not belong as fields in tables.
2). Do belong in forms or queries.(anywhere else?)
3). If used in a form: Don’t show up in wizard created reports because
reports are based on tables or queries which don’t have the calculated field
value.
4). When used in queries are practically limited to a formula that can be
constructed within the expression builder. (can not easily be set to the
return value of a function defined with VB code, and using fields in current
record of the query, as could be if it were defined at the form level using
the “Me.” Keyword to reference field values within that record)

 

[tina]

comments inline.

Ok, Calculated controls. Someone correct me if I'm wrong.
1). Do not belong as fields in tables.

correct, with possible rare exceptions (i rarely say "never" in Access

2). Do belong in forms or queries.(anywhere else?)

you can use calculated expressions in reports, much the same as you do in
forms.

3). If used in a form: Don't show up in wizard created reports because
reports are based on tables or queries which don't have the calculated field
value.

if the chosen RecordSource does not include the calculated field, then of
course there's nothing there for the wizard to include in the report -
though the Reports Wizard *can* create certain calculated controls for you,
for summing, averaging, etc.

4). When used in queries are practically limited to a formula that can be
constructed within the expression builder. (can not easily be set to the
return value of a function defined with VB code, and using fields in current
record of the query, as could be if it were defined at the form level using
the "Me." Keyword to reference field values within that record)

you can create a custom VBA function that uses the value of one or more
fields in the "current" record in the query. simply add a argument to the
function for each field value you need, and write the function code to
utilize those arguments in place of the "Me" referenced values that you
would use in a form's code.

hth

 

[John Spencer]

1). Do not belong as fields in tables.
Usually true. In rare situations, it may be necessary to store them for
especially complex calculation. BUT it is almost always a bad idea -
calculating is usually faster, doesn't take storage room, and keeps the
result from being out of agreement with the underlying data used to
calculate it.

2). Do belong in forms or queries.(anywhere else?)

And reports

3). If used in a form: Don't show up in wizard created reports because
reports are based on tables or queries which don't have the calculated
field
value.

True, you would calculate the same result in the query used for the report
as you did on the form.

4). When used in queries are practically limited to a formula that can be
constructed within the expression builder. (can not easily be set to the
return value of a function defined with VB code, and using fields in
current
record of the query, as could be if it were defined at the form level
using
the "Me." Keyword to reference field values within that record)

False. Queries (and reports) can use VBA functions. Queries cannot use
functions that use Me. as that is an "alias" for the form or report that the
module is attached to and only works in modules attached (class modules) to
forms and reports.

If you want to refer to fields on a report, you really need to attach that
field to a control on the report (You can set the visible property of the
control to false so it is not displayed.).