calculated filed based on option button choice

[spence]

I'm creating a calculated field in a form and have two
issues:

1. The four currency fields whose sum is to be the
calculated field will not all four contain figures in most
instances. Often only the first field will be populated.
How do I get my field to calculate the sum without having
to enter zeros in my unused fields?

2. Complicating matters is that I need the field's
calculation to be based on a selection from an option
group on the form. If Option 1 is selected, my calulation
is the sum of my currency fields. If Option 2 is selected
is the same sum multiplied by 1.33.

I'm having a lot less luck finding online help writing
basic expressions so your help is much appreciated.

thanks,
spence

 

[fredg]

Spence,
See my comments interspersed below.

I'm creating a calculated field in a form and have two
issues:

1. The four currency fields whose sum is to be the
calculated field will not all four contain figures in most
instances. Often only the first field will be populated.
How do I get my field to calculate the sum without having
to enter zeros in my unused fields?

Wrap the field within the Nz() function:
= Nz(Field1]) + Nz([Field])

Look up the Nz() function in Access help.

2. Complicating matters is that I need the field's
calculation to be based on a selection from an option
group on the form. If Option 1 is selected, my calulation
is the sum of my currency fields. If Option 2 is selected
is the same sum multiplied by 1.33.

Add an unbound control to the form.
Set it's Control Source to:
=IIf([OptionGroupName = 1,Nz([Field1]) + Nz([Field2]),Nz([Field1]) +
Nz([Field2])*1.33)

Notice, were only checking for the value of 1. Any other value results
in the second calculation. If at some other time you add another option,
then change the expression to:

=IIf([OptionGroupName] = 1,Nz([Field1]) +
Nz([Field2]),IIf(OptionGroupName] = 2, Nz([Field1]) +
Nz([Field2])*1.33,Some other calculation here))

I'm having a lot less luck finding online help writing
basic expressions so your help is much appreciated.

thanks,
spence

Hope this helps.

 

[spence]

Thanks for the guidance. Problems endure, however. I
created an unbound text field in my form and pasted the
following syntax in the control source field per your
instruction:

=IIf([OptionGroupName = 1,Nz([Field1]) + Nz([Field2]),Nz
([Field1]) + Nz([Field2])*1.33)

I then substituted the name of my option group and my two
fields. When I try to click off the field I get an error
that says "The expression you entered has a function
containing the wrong number of arguments."

I looked carefully to make sure I didn't mess up any of
the brackets, parathesis, or spaces when I put my own
option group and field names in, but everything looks
exactly as it does in your example. Any idea what might be
going on here?

thanks again,
spence

-----Original Message-----
Spence,
See my comments interspersed below.

I'm creating a calculated field in a form and have two
issues:

1. The four currency fields whose sum is to be the
calculated field will not all four contain figures in most
instances. Often only the first field will be populated.
How do I get my field to calculate the sum without having
to enter zeros in my unused fields?

Wrap the field within the Nz() function:
= Nz(Field1]) + Nz([Field])

Look up the Nz() function in Access help.

2. Complicating matters is that I need the field's
calculation to be based on a selection from an option
group on the form. If Option 1 is selected, my calulation
is the sum of my currency fields. If Option 2 is selected
is the same sum multiplied by 1.33.

Add an unbound control to the form.
Set it's Control Source to:
=IIf([OptionGroupName = 1,Nz([Field1]) + Nz([Field2]),Nz ([Field1]) +
Nz([Field2])*1.33)

Notice, were only checking for the value of 1. Any other value results
in the second calculation. If at some other time you add another option,
then change the expression to:

=IIf([OptionGroupName] = 1,Nz([Field1]) +
Nz([Field2]),IIf(OptionGroupName] = 2, Nz([Field1]) +
Nz([Field2])*1.33,Some other calculation here))

I'm having a lot less luck finding online help writing
basic expressions so your help is much appreciated.

thanks,
spence

Hope this helps.

.

 

[fredg]

Thanks for the guidance. Problems endure, however. I
created an unbound text field in my form and pasted the
following syntax in the control source field per your
instruction:

=IIf([OptionGroupName = 1,Nz([Field1]) + Nz([Field2]),Nz
([Field1]) + Nz([Field2])*1.33)

I then substituted the name of my option group and my two
fields. When I try to click off the field I get an error
that says "The expression you entered has a function
containing the wrong number of arguments."

I looked carefully to make sure I didn't mess up any of
the brackets, parathesis, or spaces when I put my own
option group and field names in, but everything looks
exactly as it does in your example. Any idea what might be
going on here?

thanks again,
spence

-----Original Message-----
Spence,
See my comments interspersed below.

I'm creating a calculated field in a form and have two
issues:

1. The four currency fields whose sum is to be the
calculated field will not all four contain figures in most
instances. Often only the first field will be populated.
How do I get my field to calculate the sum without having
to enter zeros in my unused fields?

Wrap the field within the Nz() function:
= Nz(Field1]) + Nz([Field])

Look up the Nz() function in Access help.

2. Complicating matters is that I need the field's
calculation to be based on a selection from an option
group on the form. If Option 1 is selected, my calulation
is the sum of my currency fields. If Option 2 is selected
is the same sum multiplied by 1.33.

Add an unbound control to the form.
Set it's Control Source to:
=IIf([OptionGroupName = 1,Nz([Field1]) + Nz([Field2]),Nz ([Field1]) +
Nz([Field2])*1.33)

Notice, were only checking for the value of 1. Any other value results
in the second calculation. If at some other time you add another option,
then change the expression to:

=IIf([OptionGroupName] = 1,Nz([Field1]) +
Nz([Field2]),IIf(OptionGroupName] = 2, Nz([Field1]) +
Nz([Field2])*1.33,Some other calculation here))

I'm having a lot less luck finding online help writing
basic expressions so your help is much appreciated.

thanks,
spence

Hope this helps.

.

Spence,
My fault. I corrected the second expression above (the one using more
than 2 options) but forgot to correct this one before I sent it.
I left out a bracket after the OptionGroupName (should have been
[OptionGroupName]).
Also, there should be parenthesis wrapping the second set of addition to
get a correct answer,
i.e. (Nz([Field1]) + Nz([Field2]) ) * 1.33)

otherwise you'll get [Field2] * 1.33 + [Field1]
which is not quite what you wanted.
Try:

=IIf([OptionGroupName] = 1,Nz([Field1]) + Nz([Field2]),(Nz([Field1]) +
Nz([Field2]))*1.33)

 

[spence]

Fred,

I don't know if it's bad form to post thank you messages
but I just wanted to say thanks for your direction on this
issue. It fixed me right up and also gave me a better
understanding of IIf statements that I'm sure will come in
handy as I progress. Thanks again...

spence

-----Original Message-----

Thanks for the guidance. Problems endure, however. I
created an unbound text field in my form and pasted the
following syntax in the control source field per your
instruction:

=IIf([OptionGroupName = 1,Nz([Field1]) + Nz([Field2]),Nz
([Field1]) + Nz([Field2])*1.33)

I then substituted the name of my option group and my two
fields. When I try to click off the field I get an error
that says "The expression you entered has a function
containing the wrong number of arguments."

I looked carefully to make sure I didn't mess up any of
the brackets, parathesis, or spaces when I put my own
option group and field names in, but everything looks
exactly as it does in your example. Any idea what might be
going on here?

thanks again,
spence

-----Original Message-----
Spence,
See my comments interspersed below.


spence wrote:

I'm creating a calculated field in a form and have two
issues:

1. The four currency fields whose sum is to be the
calculated field will not all four contain figures

in
most

instances. Often only the first field will be populated.
How do I get my field to calculate the sum without having
to enter zeros in my unused fields?

Wrap the field within the Nz() function:
= Nz(Field1]) + Nz([Field])

Look up the Nz() function in Access help.

2. Complicating matters is that I need the field's
calculation to be based on a selection from an option
group on the form. If Option 1 is selected, my calulation
is the sum of my currency fields. If Option 2 is selected
is the same sum multiplied by 1.33.

Add an unbound control to the form.
Set it's Control Source to:
=IIf([OptionGroupName = 1,Nz([Field1]) + Nz

([Field2]),Nz
([Field1]) +

Nz([Field2])*1.33)

Notice, were only checking for the value of 1. Any

other
value results

in the second calculation. If at some other time you

add
another option,

then change the expression to:

=IIf([OptionGroupName] = 1,Nz([Field1]) +
Nz([Field2]),IIf(OptionGroupName] = 2, Nz([Field1]) +
Nz([Field2])*1.33,Some other calculation here))


I'm having a lot less luck finding online help writing
basic expressions so your help is much appreciated.

thanks,
spence
Hope this helps.

.

Spence,
My fault. I corrected the second expression above (the one using more
than 2 options) but forgot to correct this one before I sent it.
I left out a bracket after the OptionGroupName (should have been
[OptionGroupName]).
Also, there should be parenthesis wrapping the second set of addition to
get a correct answer,
i.e. (Nz([Field1]) + Nz([Field2]) ) * 1.33)

otherwise you'll get [Field2] * 1.33 + [Field1]
which is not quite what you wanted.
Try:

=IIf([OptionGroupName] = 1,Nz([Field1]) + Nz([Field2]), (Nz([Field1]) +
Nz([Field2]))*1.33)



--
Fred
Please reply only to this newsgroup.
I do not respond to personal e-mail.
.