MIcrosoft Access Formula to calculate value of a field.

Put this as the Control Source for Field B:

=Switch([FieldA]<=25, 25, [FieldA]>=26 and [FieldA]<=100, 8, [FieldA] >=100 and [FieldA]<=150, 32)

mx

Thank you so much for your help.  Yes the last condition should have been 101 - 150.  Could you offer any suggestions or book of how to learn these types of functions used w/ access?  I have never seen "Switch" function before and I would like to be able to learn more of the different functions that may be available to me.  Thank you again.

BJ  

What is more important is storing derivable data in a table versus displaying it via a query.  Unless you have very strong reasons (like security), you should never store derivable data in a table.

"I have never seen "Switch" function before "

You are not alone :-)
Switch is really handy for multiple conditions like you case ... when IIF's would get out of hand.

Another handy one is Choose() ... great for multiple conditions when working with numeric values ... like an Option Group.

Let me look for a  source ... I have some (lesser known) books at work ... and one has a good list of functions like this ... I will get that tomorrow.  'ping' me back here again later tomorrow as a reminder.  Mean while ... here is the help file stuff on Switch and Choose.  

Switch Function
     

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 & [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.



Remarks

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

None of the expressions is True.


The first True expression has a corresponding value that is Null.
Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.

-----------------
Choose Function
     

Selects and returns a value from a list of arguments.

Syntax

Choose(index, choice-1[, choice-2, ... [, choice-n]])

The Choose function syntax has these parts:

Part Description
index Required. Numeric expression or field that results in a value between 1 and the number of available choices.
choice Required. Variant expression containing one of the possible choices.



Remarks

Choose returns a value from the list of choices based on the value of index. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on.

You can use Choose to look up a value in a list of possibilities. For example, if index evaluates to 3 and choice-1 = "one", choice-2 = "two", and choice-3 = "three", Choose returns "three". This capability is particularly useful if index represents the value in an option group.

Choose evaluates every choice in the list, even though it returns only one. For this reason, you should watch for undesirable side effects. For example, if you use the MsgBox function as part of an expression in all the choices, a message box will be displayed for each choice as it is evaluated, even though Choose returns the value of only one of them.

The Choose function returns a Null if index is less than 1 or greater than the number of choices listed.

If index is not a whole number, it is rounded to the nearest whole number before being evaluated.
********************

mx

"you should never store derivable data in a table."

Actually ... I do not totally agree with that.  There a cases wherein the source data used to make the calc is no longer available for various reasons, thus ... for historical reasons ... you may need to store a calculated value.  But, in general ... I would agree.

mx

Access help is a good source for learning how functions work.  Help, click Answer Wizard, type 'Math Functions' or 'Returning Strings' for a start.

mx:  I said there were exceptions.  What did that add?

sorry gRay .... missed that part.

mx

Thank you and I will ping you as a reminder.  I have one other question if you don't mind answering....  The value that is put into FieldB does not show in the table, like the other data entered.  Is there a way to make that happen.  Sould I ask this in another question ?  or do you know if can I add more points to this question?

That's because that text box is a 'Calculated Field' ... expression just to display in that text box.

So ... to also store it in the underlying table, you can do this instead:

1) In the text box for FieldB ... remove the expression we created.
Instead, make the Control Source FieldB

2)  Then, Put this in the AfterUpdate event of the text box for FieldA:

 Me.FieldB =Switch(Me.FieldA<=25, 25,Me.FieldA >=26 and Me.FieldA<=100, 8, Me.FieldA >100 and Me.FieldA<=150, 32)

which should end up looking like:

Private Sub FieldA_AfterUpdate
    Me.FieldB =Switch(Me.FieldA<=25, 25,Me.FieldA >=26 and Me.FieldA<=100, 8, Me.FieldA >100 and Me.FieldA<=150, 32)
End Sub

This will cause the value to be displayed and updated in the table.

Note ... I changed the systax slightly ... since this is now in code.

Also ... it would be a good idea to set the Name property of your text boxes to something like:

txtFieldA  and txtFieldB ... and then change the code to:


Private Sub txtFieldA_AfterUpdate
    Me.txtFieldB =Switch(Me.txtFieldA<=25, 25,Me.txtFieldA >=26 and Me.txtFieldA<=100, 8, Me.txtFieldA >100 and Me.txtFieldA<=150, 32)
End Sub

Hopefully I did not make any typos here ....

mx

I changed the fields to txtFieldA and txtFieldB and added the code in the form, afterupdate field for txtFieldA to the code, but I  received an error,  object doesn't contain the automation object 'me'.  *the expresion may not result in the name of a macro, the name of a user-defined function or [event procedure].  *there may have been an error evaluation the function, event or macro.

I am not sure what I am doing wrong but will keep trying, and further suggestions would be appreciated. :o)  thanks so much.  bj

Strange ... no way that error s/b happening.

I'm just leaving for LA from San Diego ... be back later tonight.

Remember ... Me  only words in code ... and in a Form/Report or Class module.  Not in Control Sources or queries, etc.

mx

Just wanted to let you know the error was in my renaming the fields.....  for some reason it added an _ after the field names.. and I didn't realize it.  Thanks again for all your help, you have been more than patient and helpful...  If you get a chance to send me the names of the books you were referring to, I would be very grateful.  

Thanks again.

bjm

MX:

I am posting anther formula question.  would really appreciate any help you could give.

Thanks
BJ