How to use a text formula as code formula

  • Thread starter Thread starter Marco
  • Start date Start date
M

Marco

Hello. I have this formula loaded from a text field on a form:
val([field10])+val([field20]) and I would like to use this as a code formula.
I mean, if I use that in VBA code it works, but If I load from a text field
it doesn't work.

I need that my form use that formula stored in a text field to calculate
values.

Regards in advance,
Marco
 
responded to in another newsgroup.

It's rarely necessary to post the same question to more than one group. If
it is necessary, select all the relevant newsgroups in the "To:" or
"Newsgroups:" field. That way, folks who volunteer here don't duplicate
each other's efforts responding, and you don't have to look in multiple
places to see if you got an answer...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi Marco,

You can enter that formula into an unbound textbox on your form; you need to
put an "=" sign before it. Also, if either field10 or field20 are null, the
texbox ill display #Error; you can prevent that by wrapping each part of
your formula in an Nz function. So, in your textbox, enter:
=nz(val([field10]),0)+nz(val([field20]),0)

Note also, that if field10 and field20 are numeric datatypes, you do not
need the val() function; you can just use:
=nz(field10],0)+nz([field20],0)

HTH,

Rob
 
Marco said:
Hello. I have this formula loaded from a text field on a form:
val([field10])+val([field20]) and I would like to use this as a code formula.
I mean, if I use that in VBA code it works, but If I load from a text field
it doesn't work.
Click to expand...


A text box expression requires an = sign as the first
charater.
 
Jeff I'm sorry and I understand you. But this is really imoportante to me.
And I'm very bad in English and I'm probably not explain me to well.

Rob, Marshall.

I have text99 wich contain a text. That text is the formula that I want to
use. So what I'm triyng to do is in VBA put something looked like:

Result=text99

But instead of appear me something looked like: result =
Val([field1])*val([field2])
wich is the formula in text I want the result, such as result= 15 if field1
is 15 and field2 is 2


PLEASE HELP ME.





Marshall Barton said:
Marco said:
Hello. I have this formula loaded from a text field on a form:
val([field10])+val([field20]) and I would like to use this as a code formula.
I mean, if I use that in VBA code it works, but If I load from a text field
it doesn't work.
Click to expand...


A text box expression requires an = sign as the first
charater.
Click to expand...
 
Hi Marco,

I'm still not sure I understand exactly what you're trying to do.

But here's a possible answer. If your textbox control contains a string
which is a formula that you want to use, you may be able to use the Eval
function to get the result into a variable inyour VBA code. But it may be
tricky - I've just done some (brief) testing, and haven't found anything
that works yet.

As an aside, these newsgroups are available in several other languages.
Perhaps if you posted to one in your native language you could explain your
problem better.

Again, HTH,

Rob

Marco said:
Jeff I'm sorry and I understand you. But this is really imoportante to me.
And I'm very bad in English and I'm probably not explain me to well.

Rob, Marshall.

I have text99 wich contain a text. That text is the formula that I want to
use. So what I'm triyng to do is in VBA put something looked like:

Result=text99

But instead of appear me something looked like: result =
Val([field1])*val([field2])
wich is the formula in text I want the result, such as result= 15 if
field1
is 15 and field2 is 2


PLEASE HELP ME.





Marshall Barton said:
Marco said:
Hello. I have this formula loaded from a text field on a form:
val([field10])+val([field20]) and I would like to use this as a code
formula.
I mean, if I use that in VBA code it works, but If I load from a text
field
it doesn't work.
Click to expand...


A text box expression requires an = sign as the first
charater.
Click to expand...
Click to expand...
 
Marco,
Can the user change the formula in the textbox?
Do you have a list of the different formulas that could be in that textbox?
This formula val([field10])+val([field20]) is fairly simple
Does the formula change sometimes by summing different fields other than
field10 and field20?

Jeanette Cunningham
 
Marco said:
I have text99 wich contain a text. That text is the formula that I want to
use. So what I'm triyng to do is in VBA put something looked like:

Result=text99

But instead of appear me something looked like: result =
Val([field1])*val([field2])
wich is the formula in text I want the result, such as result= 15 if field1
is 15 and field2 is 2
Click to expand...


That kind of thing is very complex and I recommend that you
try to find another way to do the job.

As Rob said, you can use the Eval function to evaluate a
formula in a string. BUT, the Eval function is unaware of
fields in a query or unqualified controls on a form. If
your formula were:
Val(Forms!someform.textboxA)*Val(Forms!someform.textboxB)
then you could use another text box with an expression like:
=Eval(text99)
to display the final value.

If that's not how you want to write your formulas, then you
have to replace the fields in the formula with their numeric
value. For your formula the expression would need to be
like:

=Eval(Replace(Replace(text99,"field1",field1),"field2",field2))

Obviously, that becomes absurd very quickly as the number of
fields in all of your formulas increases, which is why
Jeanette asked you to explain more about the different
formulas you want to use.
 
Back
Top