If Else expression

G

Guest

My knowledge of expressions is extremely limited. I need to write an If -
Else expression and do not know the correct formating.

If "Mailing Option" field = "Overnight", I want the "Mailing fees" field to
populate with "15"

If "Mailing Option" field = "Same Day", I want the "Mailing fees" field to
populate with "25'

If Mailing Option" field = anything else, I want the "Mailing fees" field to
be blank.

Can someone please help me with this? A detailed example would be wonderful!

Please remember - I am a novice at writing expressions.

Thank you!

Laura E.
 
F

fredg

My knowledge of expressions is extremely limited. I need to write an If -
Else expression and do not know the correct formating.

If "Mailing Option" field = "Overnight", I want the "Mailing fees" field to
populate with "15"

If "Mailing Option" field = "Same Day", I want the "Mailing fees" field to
populate with "25'

If Mailing Option" field = anything else, I want the "Mailing fees" field to
be blank.

Can someone please help me with this? A detailed example would be wonderful!

Please remember - I am a novice at writing expressions.

Thank you!

Laura E.
Click to expand...

Well, no, you don't need If.. Else.
Access has a very useful IIf function.

Directly as the control source of an unbound text control on your
form, or on a report:

=IIf([MailingOption] = "Overnight",15,IIf([MailingOption] = "Same
Day",25,Null))

The above values will display on your form.
You can set the control's label caption to "Mailing fees", but this
value should not be saved to any table. Anytime you need the value,
re-calculate it as above.

The above also assumes that the [MailingOption] field is Text
datatype, and not a Number datatype.

If you were using this value in a further calculation, it would look
something like this:
=[SalePrice] * [Tax] + If([MailingOption] =
"Overnight",15,IIf([MailingOption] = "Same Day",25,0))

Notice I changed Null to 0 as you are performing math with the value,
as opposed to just displaying the value.

If you were doing this calculation in a query, you would use:
MailingFees:IIf([MailingOption] = "Overnight",15,IIf([MailingOption] =
"Same Day",25,Null))

You could then use this new field [MailingFees] in any further
calculation in a report.
 
G

Guest

Thank you - This was very helpful!

I used the expression in a report. Now I need to sum the values of these
calculated fields. They exist in an unbound text control (just as you
instructed). It is titled Text62.

When I create a new unbound text box and change the Data - Control Source
expression to "=Sum([Text62]) with the Running Sum set to over-all, I am
able to save the file in Design view, but when I return to Data View, I get a
message requesting me to enter a parameter value for Text 62.

Is my expression wrong? Can I not add the values of a field that calculates
values?

Any advice?

Thank you!

Laura E


fredg said:
My knowledge of expressions is extremely limited. I need to write an If -
Else expression and do not know the correct formating.

If "Mailing Option" field = "Overnight", I want the "Mailing fees" field to
populate with "15"

If "Mailing Option" field = "Same Day", I want the "Mailing fees" field to
populate with "25'

If Mailing Option" field = anything else, I want the "Mailing fees" field to
be blank.

Can someone please help me with this? A detailed example would be wonderful!

Please remember - I am a novice at writing expressions.

Thank you!

Laura E.
Click to expand...

Well, no, you don't need If.. Else.
Access has a very useful IIf function.

Directly as the control source of an unbound text control on your
form, or on a report:

=IIf([MailingOption] = "Overnight",15,IIf([MailingOption] = "Same
Day",25,Null))

The above values will display on your form.
You can set the control's label caption to "Mailing fees", but this
value should not be saved to any table. Anytime you need the value,
re-calculate it as above.

The above also assumes that the [MailingOption] field is Text
datatype, and not a Number datatype.

If you were using this value in a further calculation, it would look
something like this:
=[SalePrice] * [Tax] + If([MailingOption] =
"Overnight",15,IIf([MailingOption] = "Same Day",25,0))

Notice I changed Null to 0 as you are performing math with the value,
as opposed to just displaying the value.

If you were doing this calculation in a query, you would use:
MailingFees:IIf([MailingOption] = "Overnight",15,IIf([MailingOption] =
"Same Day",25,Null))

You could then use this new field [MailingFees] in any further
calculation in a report.
Click to expand...
 
F

fredg

Thank you - This was very helpful!

I used the expression in a report. Now I need to sum the values of these
calculated fields. They exist in an unbound text control (just as you
instructed). It is titled Text62.

When I create a new unbound text box and change the Data - Control Source
expression to "=Sum([Text62]) with the Running Sum set to over-all, I am
able to save the file in Design view, but when I return to Data View, I get a
message requesting me to enter a parameter value for Text 62.

Is my expression wrong? Can I not add the values of a field that calculates
values?

Any advice?

Thank you!

Laura E

fredg said:
My knowledge of expressions is extremely limited. I need to write an If -
Else expression and do not know the correct formating.

If "Mailing Option" field = "Overnight", I want the "Mailing fees" field to
populate with "15"

If "Mailing Option" field = "Same Day", I want the "Mailing fees" field to
populate with "25'

If Mailing Option" field = anything else, I want the "Mailing fees" field to
be blank.

Can someone please help me with this? A detailed example would be wonderful!

Please remember - I am a novice at writing expressions.

Thank you!

Laura E.
Click to expand...

Well, no, you don't need If.. Else.
Access has a very useful IIf function.

Directly as the control source of an unbound text control on your
form, or on a report:

=IIf([MailingOption] = "Overnight",15,IIf([MailingOption] = "Same
Day",25,Null))

The above values will display on your form.
You can set the control's label caption to "Mailing fees", but this
value should not be saved to any table. Anytime you need the value,
re-calculate it as above.

The above also assumes that the [MailingOption] field is Text
datatype, and not a Number datatype.

If you were using this value in a further calculation, it would look
something like this:
=[SalePrice] * [Tax] + If([MailingOption] =
"Overnight",15,IIf([MailingOption] = "Same Day",25,0))

Notice I changed Null to 0 as you are performing math with the value,
as opposed to just displaying the value.

If you were doing this calculation in a query, you would use:
MailingFees:IIf([MailingOption] = "Overnight",15,IIf([MailingOption] =
"Same Day",25,Null))

You could then use this new field [MailingFees] in any further
calculation in a report.
Click to expand...
Click to expand...

Repeat the calculation.
=Sum(IIf([MailingOption] = "Overnight",15,IIf([MailingOption] = "Same
Day",25,0)))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum Expression 2
Access 3
Populate field based on If statement 5
Randomly populating data fields 1
Default value in a table 4
Expression used in a table 2
How to send filtered results to Reports?? 3
creating a smart mailing list mailing list 2

Top