How do you write formulas in Access?

[Tina]

I need to write a formula like this.....

PI= x+2y+3z+4a+5b/x+y+z+a+b

The values in the cells are letters and not numbers. So I will need another
formula to tell it to give it a (x,y,z,a or b) value (I am assuming)? I am
so lost. If anyone can help I would really appreciate it.

 

[Duane Hookom]

If I understand correctly, you might be able to use the Eval() function.
Open any module and check Help to see if this is your answer.

 

[mnature]

There are cells in Excel, but no cells in Access. What you are talking about
doesn't seem to have anything to do with Access. Please clarify.

 

[Tina]

It is in Access, and I was incorrect in calling it a cell. It is a text box
in a form that is linked to a table. The user needs to choose from a list of
acronyms in each text box and then I need to calculate the formula based on
the number of acronyms that appear in the form, however, the problem that I
am having is that the acronyms do not have a value.

 

[OfficeDev18 via AccessMonster.com]

Hi, Tina,

What you can do, if it makes things easier for you, is to declare a, b, c,
etc. as global variables in your form's VBA, and in the form's OnCurrent
event you can initialize them. E.g.

a = 1
b = 2.45
c = a/b

etc.

After the user selects his choices, you can put in the textbox's AfterUpdate
event the following code:

Dim strMid As String, sglNum As Single, sglTot as Single, Cntr As Integer

For Cntr = 1 to Len(Me.YourTextboxName)
strMid = Mid(Me.YourTextboxName,Cntr,1)
.... now you can analyze if you have a number, a letter (that you can
compare with your already assigned global variables), etc. You can compute
them into sglNum, and add the computation to sglTot as you go.
Next Cntr

I think this will get you where you want to be.

Sam

It is in Access, and I was incorrect in calling it a cell. It is a text box
in a form that is linked to a table. The user needs to choose from a list of
acronyms in each text box and then I need to calculate the formula based on
the number of acronyms that appear in the form, however, the problem that I
am having is that the acronyms do not have a value.

There are cells in Excel, but no cells in Access. What you are talking about
doesn't seem to have anything to do with Access. Please clarify.

[quoted text clipped - 6 lines]

 

[Duane Hookom]

Why don't you just provide some sample records and desired calculations in
your text boxes.

 

[John Vinson]

It is in Access, and I was incorrect in calling it a cell. It is a text box
in a form that is linked to a table. The user needs to choose from a list of
acronyms in each text box and then I need to calculate the formula based on
the number of acronyms that appear in the form, however, the problem that I
am having is that the acronyms do not have a value.

Where is the value going to come from? How can Access evaluate x + y
if you don't have the values of x and y SOMEWHERE in the application?

You will need *some* sort of a Table with fields for the acronym and
the value associated with that acronym. The user would be able to
select the desired acronym (preferably from a Combo Box or listbox
presenting only the valid acronyms, rather than typing it into a
textbox); the combo's (or listbox's) bound column would be the numeric
value that you're trying to use.

John W. Vinson[MVP]

 

[Tina]

Thank you! That sounds like exactly what I need!

Hi, Tina,

What you can do, if it makes things easier for you, is to declare a, b, c,
etc. as global variables in your form's VBA, and in the form's OnCurrent
event you can initialize them. E.g.

a = 1
b = 2.45
c = a/b

etc.

After the user selects his choices, you can put in the textbox's AfterUpdate
event the following code:

Dim strMid As String, sglNum As Single, sglTot as Single, Cntr As Integer

For Cntr = 1 to Len(Me.YourTextboxName)
strMid = Mid(Me.YourTextboxName,Cntr,1)
.... now you can analyze if you have a number, a letter (that you can
compare with your already assigned global variables), etc. You can compute
them into sglNum, and add the computation to sglTot as you go.
Next Cntr

I think this will get you where you want to be.

Sam

It is in Access, and I was incorrect in calling it a cell. It is a text box
in a form that is linked to a table. The user needs to choose from a list of
acronyms in each text box and then I need to calculate the formula based on
the number of acronyms that appear in the form, however, the problem that I
am having is that the acronyms do not have a value.

There are cells in Excel, but no cells in Access. What you are talking about
doesn't seem to have anything to do with Access. Please clarify.

[quoted text clipped - 6 lines]

formula to tell it to give it a (x,y,z,a or b) value (I am assuming)? I am
so lost. If anyone can help I would really appreciate it.

 

[Tina]

I think Sam answered my question.

To answer yours....I am not too keen on the terms in access, so please
forgive me. I do have the text box, set up as a combo box taking the acronym
from a table with a value associated with the acronym (so the user doesn't
have to type it in). I didn't have the value as the bound column, so that
may be where my problem lies.

Thanks.