User-defined calculation as text in an update query

[Eric Kelso]

I'm trying to use a column reference that contains a user-defined calculation
as a text string in an update query. For example, if I have a table like the
one below with 4 columns (A, B and D are double; C is text) and 1 record:

A: 2
B: 3
C: [A]+
D: Null

I would like to write an update query that writes the resulting value of C's
equation into D (e.g. 5). The update query SQL statement I've written
("UPDATE XYZ SET XYZ.D = [C]") keeps giving me a type conversion failure.
Does anyone know how to do this or can at least point me in the right
direction?

 

[fredg]

I'm trying to use a column reference that contains a user-defined calculation
as a text string in an update query. For example, if I have a table like the
one below with 4 columns (A, B and D are double; C is text) and 1 record:

A: 2
B: 3
C: [A]+
D: Null

I would like to write an update query that writes the resulting value of C's
equation into D (e.g. 5). The update query SQL statement I've written
("UPDATE XYZ SET XYZ.D = [C]") keeps giving me a type conversion failure.
Does anyone know how to do this or can at least point me in the right
direction?

Don't.
All you need do is store the [A] and fields.
[C] and [D] are not needed.

[A] and are number datatypes or Text datatypes?

If Number:
All you need do, whenever you need the sum of [A] + is do the
calculation, in a form, in a query or in a report.
In an unbound text control on a form or in a report:
= [A] +

This result of 5 should not be stored in any table.

If [A] and are text datatype fields, then use
= [A] &

Once again this result of 23 need not be stored in any table.

 

[Marshall Barton]

I'm trying to use a column reference that contains a user-defined calculation
as a text string in an update query. For example, if I have a table like the
one below with 4 columns (A, B and D are double; C is text) and 1 record:

A: 2
B: 3
C: [A]+
D: Null

I would like to write an update query that writes the resulting value of C's
equation into D (e.g. 5). The update query SQL statement I've written
("UPDATE XYZ SET XYZ.D = [C]") keeps giving me a type conversion failure.
Does anyone know how to do this or can at least point me in the right
direction?

A database is not a spreadsheet! You really have to use a
different paradigm in a database system.

First, as Fred says you do not store the result of a
calculation on some fields in another field.

Second, it is extremely unusual to have users entering
expressions to calculate a value. A calculation is normally
part of the application that was specified at the time you
create the application.

What are you going to do if a user enters an invalid or even
a malicious expression?? Very dangerous and a high
potential security risk.

Typically, when different records require a different
calculation, you would specify a code word/number in field
[C] to specify the calculation and you would provide a
Function to check the code and perform the predefined
calculation and return the result to a query.

 

[Eric Kelso]

A database is not a spreadsheet! You really have to use a
different paradigm in a database system.

First, as Fred says you do not store the result of a
calculation on some fields in another field.

Second, it is extremely unusual to have users entering
expressions to calculate a value. A calculation is normally
part of the application that was specified at the time you
create the application.

What are you going to do if a user enters an invalid or even
a malicious expression?? Very dangerous and a high
potential security risk.

Typically, when different records require a different
calculation, you would specify a code word/number in field
[C] to specify the calculation and you would provide a
Function to check the code and perform the predefined
calculation and return the result to a query.

Thank you for your input. My situtation is probably atypical. The only
users are myself and one other person who, like me, is not all that concerned
with "proper technique" on this issue. We need to store calculated values
for future use for a variety of reasons I don't want to get into. As you
surmised, in the same table, each record might have a different equation.
Managing these equations as separate functions or update queries does not
seem as efficient as a single update query referencing the equation as data
in a text field. If the equation turns out to be incorrect I'm not that
worried -- I'll fix it.

I like your idea of a code word/number in field [C]. That's useful. I
simply was asking if what I had hoped to do was possible and, from what I
understand, it is not. I wasn't looking for a lecture. Thanks again.

 

[Marshall Barton]

A database is not a spreadsheet! You really have to use a
different paradigm in a database system.

First, as Fred says you do not store the result of a
calculation on some fields in another field.

Second, it is extremely unusual to have users entering
expressions to calculate a value. A calculation is normally
part of the application that was specified at the time you
create the application.

What are you going to do if a user enters an invalid or even
a malicious expression?? Very dangerous and a high
potential security risk.

Typically, when different records require a different
calculation, you would specify a code word/number in field
[C] to specify the calculation and you would provide a
Function to check the code and perform the predefined
calculation and return the result to a query.

Thank you for your input. My situtation is probably atypical. The only
users are myself and one other person who, like me, is not all that concerned
with "proper technique" on this issue. We need to store calculated values
for future use for a variety of reasons I don't want to get into. As you
surmised, in the same table, each record might have a different equation.
Managing these equations as separate functions or update queries does not
seem as efficient as a single update query referencing the equation as data
in a text field. If the equation turns out to be incorrect I'm not that
worried -- I'll fix it.

I like your idea of a code word/number in field [C]. That's useful. I
simply was asking if what I had hoped to do was possible and, from what I
understand, it is not.

Well, what you asked for is doable in a limited situation.

Set the calculation for field D in a query to this kind of
thing:
Eval(Replace(Replace([C],"A",[A]),"B",))

Using the Eval function is a potential security problem, so
you may have to change your setting for sandbox mode and/or
lower your level of security.

Regardless of how you go about calculating the result of the
expression, as long as perfomance is not a critical factor,
you should not use an Update query to save the result back
to the table. A Select query that calculates the value on
the fly can be used in almost any situation that a table can
be used.