prepare a conditional (if) statement

[houdini]

I'm trying to write a program that will find the amount of commission to pay
a sales rep. In my Input Form, I have an option group with 3 radio buttons.
The group is labled "Who Sold?", and option 1 is Listing agent, option 2 is
Another agent and option 3 is Another firm. In the Query, the field "Who
Sold?" contains the number 1, 2, or 3 depending on what was selected in the
Input Form. There is another file in the Query called "Total Commission".
This is the Commission collected by the Company, and is split with the Sales
Rep. as either 25 or 50%. The file that I'm trying to put in a conditional
"If" statement is "Agent's Commission". Deprnding on whether 1, 2, or 3 is in
"Who Sold?" the "Agent's Commission" should be 1/4 or 1/2 of the "Total
Commission". For some reason, Access requires an "IIf" instead of "If". Most
other programs use "If". The statement I'm trying to write into the Agent's
Commission file is: " If Who Sold? is 1 then divide Total commission by 2,
else if Who Sold? is 2 then divide Total Commission by 4 else if Who Sold? is
3, divide Total Commission by 4. End if.
I've tried writing this a number of different ways. One is:
Agent's Commission=IIf([Who Sold?]=1 Then[Total Commission]/2,
ElseIIf [Who Sold?]=2 Then[total Commission]/4 ElseIIf [Who Sold?]=3
Then[Total Commission]/4) End IIf

Another is:
Agent's Commission=IIf(([Who Sold?]=1 [Total Commission]/2), ElseIIf ([Who
Sold?]=2 [total Commission]/4) ElseIIf ([Who Sold?]=3 [Total Commission]/4))
End IIf

I've tried using the Expression Builder function in the Design mode of
Query. In the Expression builder, on the bottom left, click on "Functions",
and when it open, click on "Built in Functions". This opens a group of
functions on the far right. Scroll down to the "IIf", and click on it. When
you do, at the top of the Expressions Builder Window, it will have a formula
that looks like:
<<expr>>IIf(<<expr>>,<<truepart>>, <<falsepart>>)
I've tries substituting values in there, and they still come back with an
error message. At this point, I'm really getting frustrated. I know that
there's some little thing I'm doing wrong that keeps it from working, but I
can't seem to be able to find it. I really need help.

 

[Wayne Morgan]

You need a nested IIf statement and a calculated field, using your example,
the calculated field's name would be "Agent's Commission". To do this, in
the Field Name box of the query design grid, you would use something like:

[Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, IIf([Who
Sold?]=2, [Total Commission]/4, [Total Commission]/4))

Now, since there are only 3 options and the result of two of them are the
same, you really only have to test for the unique option. In other words, if
the value is 2 or 3 (i.e. the value isn't 1), then you are dividing by 4, so
we really only need to check for a value of 1. If it is 1, then divide by 2;
if it's not 1, then divide by 4.

[Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, [Total
Commission]/4)


Now, you say that you are trying to write this into the "Agent's Commission
file". This will give you the value of the agent's commission displayed in
the query, but won't "put" it anywhere. What are you trying to do with this
value once you have it?

--
Wayne Morgan
MS Access MVP

I'm trying to write a program that will find the amount of commission to
pay
a sales rep. In my Input Form, I have an option group with 3 radio
buttons.
The group is labled "Who Sold?", and option 1 is Listing agent, option 2
is
Another agent and option 3 is Another firm. In the Query, the field "Who
Sold?" contains the number 1, 2, or 3 depending on what was selected in
the
Input Form. There is another file in the Query called "Total Commission".
This is the Commission collected by the Company, and is split with the
Sales
Rep. as either 25 or 50%. The file that I'm trying to put in a conditional
"If" statement is "Agent's Commission". Deprnding on whether 1, 2, or 3 is
in
"Who Sold?" the "Agent's Commission" should be 1/4 or 1/2 of the "Total
Commission". For some reason, Access requires an "IIf" instead of "If".
Most
other programs use "If". The statement I'm trying to write into the
Agent's
Commission file is: " If Who Sold? is 1 then divide Total commission by 2,
else if Who Sold? is 2 then divide Total Commission by 4 else if Who Sold?
is
3, divide Total Commission by 4. End if.
I've tried writing this a number of different ways. One is:
Agent's Commission=IIf([Who Sold?]=1 Then[Total Commission]/2,
ElseIIf [Who Sold?]=2 Then[total Commission]/4 ElseIIf [Who Sold?]=3
Then[Total Commission]/4) End IIf

Another is:
Agent's Commission=IIf(([Who Sold?]=1 [Total Commission]/2), ElseIIf ([Who
Sold?]=2 [total Commission]/4) ElseIIf ([Who Sold?]=3 [Total
Commission]/4))
End IIf

I've tried using the Expression Builder function in the Design mode of
Query. In the Expression builder, on the bottom left, click on
"Functions",
and when it open, click on "Built in Functions". This opens a group of
functions on the far right. Scroll down to the "IIf", and click on it.
When
you do, at the top of the Expressions Builder Window, it will have a
formula
that looks like:
<<expr>>IIf(<<expr>>,<<truepart>>, <<falsepart>>)
I've tries substituting values in there, and they still come back with an
error message. At this point, I'm really getting frustrated. I know that
there's some little thing I'm doing wrong that keeps it from working, but
I
can't seem to be able to find it. I really need help.

 

[JP Bless]

What of Select Case In a Function

Function dCommision(WhoSold, TotComission) as Double
Select Case WhoSold
Case 1
dCommision = TotalComission/1
Case 2
dComission = TotalComission/2
ENd Select
End Function

 

[houdini]

Wayne, thanks for the response.I'm putting this "if" statement in the Query
in design view. I'm putting it into the Field (at the top) in Agent's
Commission. That way, every time I enter a new record, it will figure the
agent's commission for that record, and put it into that field. When I put
in" [Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, [Total

Commission]/4)" in that field, I got an error message. I assume the Agent's Commission was in the field twice, so I removed one. When I tried to run the program, I got an error msg box "Enter Perameter Value" with a white space to put in the perameter value. I have no idea what the perameter value is, or should be. Al Collins

You need a nested IIf statement and a calculated field, using your example,
the calculated field's name would be "Agent's Commission". To do this, in
the Field Name box of the query design grid, you would use something like:

[Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, IIf([Who
Sold?]=2, [Total Commission]/4, [Total Commission]/4))

Now, since there are only 3 options and the result of two of them are the
same, you really only have to test for the unique option. In other words, if
the value is 2 or 3 (i.e. the value isn't 1), then you are dividing by 4, so
we really only need to check for a value of 1. If it is 1, then divide by 2;
if it's not 1, then divide by 4.

[Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, [Total
Commission]/4)


Now, you say that you are trying to write this into the "Agent's Commission
file". This will give you the value of the agent's commission displayed in
the query, but won't "put" it anywhere. What are you trying to do with this
value once you have it?

--
Wayne Morgan
MS Access MVP

I'm trying to write a program that will find the amount of commission to
pay
a sales rep. In my Input Form, I have an option group with 3 radio
buttons.
The group is labled "Who Sold?", and option 1 is Listing agent, option 2
is
Another agent and option 3 is Another firm. In the Query, the field "Who
Sold?" contains the number 1, 2, or 3 depending on what was selected in
the
Input Form. There is another file in the Query called "Total Commission".
This is the Commission collected by the Company, and is split with the
Sales
Rep. as either 25 or 50%. The file that I'm trying to put in a conditional
"If" statement is "Agent's Commission". Deprnding on whether 1, 2, or 3 is
in
"Who Sold?" the "Agent's Commission" should be 1/4 or 1/2 of the "Total
Commission". For some reason, Access requires an "IIf" instead of "If".
Most
other programs use "If". The statement I'm trying to write into the
Agent's
Commission file is: " If Who Sold? is 1 then divide Total commission by 2,
else if Who Sold? is 2 then divide Total Commission by 4 else if Who Sold?
is
3, divide Total Commission by 4. End if.
I've tried writing this a number of different ways. One is:
Agent's Commission=IIf([Who Sold?]=1 Then[Total Commission]/2,
ElseIIf [Who Sold?]=2 Then[total Commission]/4 ElseIIf [Who Sold?]=3
Then[Total Commission]/4) End IIf

Another is:
Agent's Commission=IIf(([Who Sold?]=1 [Total Commission]/2), ElseIIf ([Who
Sold?]=2 [total Commission]/4) ElseIIf ([Who Sold?]=3 [Total
Commission]/4))
End IIf

I've tried using the Expression Builder function in the Design mode of
Query. In the Expression builder, on the bottom left, click on
"Functions",
and when it open, click on "Built in Functions". This opens a group of
functions on the far right. Scroll down to the "IIf", and click on it.
When
you do, at the top of the Expressions Builder Window, it will have a
formula
that looks like:
<<expr>>IIf(<<expr>>,<<truepart>>, <<falsepart>>)
I've tries substituting values in there, and they still come back with an
error message. At this point, I'm really getting frustrated. I know that
there's some little thing I'm doing wrong that keeps it from working, but
I
can't seem to be able to find it. I really need help.

 

[houdini]

JP, Thanks for your response. Unfortunately, you're talking way over my head.
I don't know what Select Case is or what kind of Function it represent. My
programing knowledge is extremely limited. Thanks Al Collins

What of Select Case In a Function

Function dCommision(WhoSold, TotComission) as Double
Select Case WhoSold
Case 1
dCommision = TotalComission/1
Case 2
dComission = TotalComission/2
ENd Select
End Function

I'm trying to write a program that will find the amount of commission to pay
a sales rep. In my Input Form, I have an option group with 3 radio buttons.
The group is labled "Who Sold?", and option 1 is Listing agent, option 2 is
Another agent and option 3 is Another firm. In the Query, the field "Who
Sold?" contains the number 1, 2, or 3 depending on what was selected in the
Input Form. There is another file in the Query called "Total Commission".
This is the Commission collected by the Company, and is split with the Sales
Rep. as either 25 or 50%. The file that I'm trying to put in a conditional
"If" statement is "Agent's Commission". Deprnding on whether 1, 2, or 3 is in
"Who Sold?" the "Agent's Commission" should be 1/4 or 1/2 of the "Total
Commission". For some reason, Access requires an "IIf" instead of "If". Most
other programs use "If". The statement I'm trying to write into the Agent's
Commission file is: " If Who Sold? is 1 then divide Total commission by 2,
else if Who Sold? is 2 then divide Total Commission by 4 else if Who Sold? is
3, divide Total Commission by 4. End if.
I've tried writing this a number of different ways. One is:
Agent's Commission=IIf([Who Sold?]=1 Then[Total Commission]/2,
ElseIIf [Who Sold?]=2 Then[total Commission]/4 ElseIIf [Who Sold?]=3
Then[Total Commission]/4) End IIf

Another is:
Agent's Commission=IIf(([Who Sold?]=1 [Total Commission]/2), ElseIIf ([Who
Sold?]=2 [total Commission]/4) ElseIIf ([Who Sold?]=3 [Total Commission]/4))
End IIf

I've tried using the Expression Builder function in the Design mode of
Query. In the Expression builder, on the bottom left, click on "Functions",
and when it open, click on "Built in Functions". This opens a group of
functions on the far right. Scroll down to the "IIf", and click on it. When
you do, at the top of the Expressions Builder Window, it will have a formula
that looks like:
<<expr>>IIf(<<expr>>,<<truepart>>, <<falsepart>>)
I've tries substituting values in there, and they still come back with an
error message. At this point, I'm really getting frustrated. I know that
there's some little thing I'm doing wrong that keeps it from working, but I
can't seem to be able to find it. I really need help.

 

[Wayne Morgan]

Try removing the brackets from around Agent's Commission. Usually, if there
is a space in the name you need the brackets, but I just double checked and
you don't in this case. Also, if you're getting prompted for a parameter, it
usually means that you've misspelled a field name so Access doesn't
recognize it as a field and instead thinks you've put in a parameter that it
is supposed to prompt for.

This will compute the value in the query. It won't store it in a table. You
normally shouldn't store values that you can calculate, just calculate them
when you need them. If there is a field in the table underlying the query
that is also called Agent's Commission, you're correct, you'll get an error.

--
Wayne Morgan
MS Access MVP

Wayne, thanks for the response.I'm putting this "if" statement in the
Query
in design view. I'm putting it into the Field (at the top) in Agent's
Commission. That way, every time I enter a new record, it will figure the
agent's commission for that record, and put it into that field. When I put
in" [Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, [Total

Commission]/4)" in that field, I got an error message. I assume the
Agent's Commission was in the field twice, so I removed one. When I tried
to run the program, I got an error msg box "Enter Perameter Value" with a
white space to put in the perameter value. I have no idea what the
perameter value is, or should be. Al Collins

 

[houdini]

Wayne, Thanks for the reply. I'm still getting an errormessage."The
expression you entered contains an invalid Syntax". I'm coping the statement
exactly like you showed, and it's still giving an error message. You asked
what I was tring to do with the value. Let me explain. I'm using the field
"Agent's Commission" as both a calculated and permanent field. When an Agent
makes a sale, the commission for that sale is put in the Agent's Commission
for that particular sale. When the agent makes another sale, another
commission is computed based on the sale volumn, and another Agent's
Commission is computed based on that sale. This new sale is also put into the
same Agent's Commission field, but on a separate line. An agent can have any
number of sales, and each commission will be different, and the corresponding
Agent's Commission will be different. At the end of the pay period, all the
separate Agent Commissions will be totaled to determine how much to pay each
sales agent. This can't be a cumulative total because each sale must be
represented and each individual commission must be accounted for. That is the
reason I'm calculating this in the Agent's Commission field. The other thing,
is that on the same form I'm inputing other agents and their sales on
separate lines. As a sale is made for John Smith, I input his information
into a line 1 for example. Sally Jones might be the next sale, and Ralph
Williams may be the third, then John Smith might make the 4th sale, and his
information would be entered in the fourth line. At the end of the pay
period, I do a sort for each Sales Agent and get a report of their sales.
Each sale is on a separate line and each commission is on the same line as
the sale. So, if I can't use the Agent's Commission field as both a
calculated and permanent field, how would you suggest handling it? The field
Total Commission id a Calculated and permanent field. That particular field
is the commission rate times the sales volunme, giving the Total Commission.
The Agent's Commission is then figured off the Total Commission for that
particular sale.

Wayne, thanks for the response.I'm putting this "if" statement in the Query
in design view. I'm putting it into the Field (at the top) in Agent's
Commission. That way, every time I enter a new record, it will figure the
agent's commission for that record, and put it into that field. When I put
in" [Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, [Total

Commission]/4)" in that field, I got an error message. I assume the Agent's Commission was in the field twice, so I removed one. When I tried to run the program, I got an error msg box "Enter Perameter Value" with a white space to put in the perameter value. I have no idea what the perameter value is, or should be. Al Collins

You need a nested IIf statement and a calculated field, using your example,
the calculated field's name would be "Agent's Commission". To do this, in
the Field Name box of the query design grid, you would use something like:

[Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, IIf([Who
Sold?]=2, [Total Commission]/4, [Total Commission]/4))

Now, since there are only 3 options and the result of two of them are the
same, you really only have to test for the unique option. In other words, if
the value is 2 or 3 (i.e. the value isn't 1), then you are dividing by 4, so
we really only need to check for a value of 1. If it is 1, then divide by 2;
if it's not 1, then divide by 4.

[Agent's Commission]: IIf([Who Sold?]=1, [Total Commission]/2, [Total
Commission]/4)


Now, you say that you are trying to write this into the "Agent's Commission
file". This will give you the value of the agent's commission displayed in
the query, but won't "put" it anywhere. What are you trying to do with this
value once you have it?

--
Wayne Morgan
MS Access MVP

I'm trying to write a program that will find the amount of commission to
pay
a sales rep. In my Input Form, I have an option group with 3 radio
buttons.
The group is labled "Who Sold?", and option 1 is Listing agent, option 2
is
Another agent and option 3 is Another firm. In the Query, the field "Who
Sold?" contains the number 1, 2, or 3 depending on what was selected in
the
Input Form. There is another file in the Query called "Total Commission".
This is the Commission collected by the Company, and is split with the
Sales
Rep. as either 25 or 50%. The file that I'm trying to put in a conditional
"If" statement is "Agent's Commission". Deprnding on whether 1, 2, or 3 is
in
"Who Sold?" the "Agent's Commission" should be 1/4 or 1/2 of the "Total
Commission". For some reason, Access requires an "IIf" instead of "If".
Most
other programs use "If". The statement I'm trying to write into the
Agent's
Commission file is: " If Who Sold? is 1 then divide Total commission by 2,
else if Who Sold? is 2 then divide Total Commission by 4 else if Who Sold?
is
3, divide Total Commission by 4. End if.
I've tried writing this a number of different ways. One is:
Agent's Commission=IIf([Who Sold?]=1 Then[Total Commission]/2,
ElseIIf [Who Sold?]=2 Then[total Commission]/4 ElseIIf [Who Sold?]=3
Then[Total Commission]/4) End IIf

Another is:
Agent's Commission=IIf(([Who Sold?]=1 [Total Commission]/2), ElseIIf ([Who
Sold?]=2 [total Commission]/4) ElseIIf ([Who Sold?]=3 [Total
Commission]/4))
End IIf

I've tried using the Expression Builder function in the Design mode of
Query. In the Expression builder, on the bottom left, click on
"Functions",
and when it open, click on "Built in Functions". This opens a group of
functions on the far right. Scroll down to the "IIf", and click on it.
When
you do, at the top of the Expressions Builder Window, it will have a
formula
that looks like:
<<expr>>IIf(<<expr>>,<<truepart>>, <<falsepart>>)
I've tries substituting values in there, and they still come back with an
error message. At this point, I'm really getting frustrated. I know that
there's some little thing I'm doing wrong that keeps it from working, but
I
can't seem to be able to find it. I really need help.

 

[Wayne Morgan]

As stated, this won't save the value in the table. If you want to do that,
then you need to create a textbox on the form and bind it to the [Agent's
Commission] field in the table. Use the BeforeUpdate event of the form to
calculate the commission and assign it to this textbox. When the update is
then saved, the value in that textbox will also be saved.

I understand you are going back and calculating a sum of all of these
commissions. However, it should be possible to go through all of the sales
for the time period mentioned and using the Total Commission and Who Sold
fields, calculate the Agent's Commission in the query as a calculated field
and sum that field. You could group on the agent to get all of them at once,
if desired, with each of their totals. The grouping and/or the summing could
be done in a query or report.