Formula Help!!!

[Guest]

I have the following expression:

Expr1: IIf([SCHW ADV NETWORK New]="Yes",[TOTAL FEE]*0.15,IIf([SCH ADV
NETWORK New]="Yes New",[SAN FEE],IIf([SCH ADV NETWORK New]="No",0)))

The first part of the expression works when SCHW ADV NETWORK New equals
"Yes", it does not work when it equals ''Yes New", but I do not get an error
either. What am I doing wrong? Please help.

Jeanette

 

[Guest]

IIf([SCH ADV NETWORK New]="No",0)

The above doesn't say what to do if it doesn't ="No".

I'd also doube check that the data in the table is actually "Yes New". A
leading or trailing space would cause a different return as would an extra
space between the words.

 

[Guest]

I do not want anything to happen if it is "No". I checked for leading and
trailing spaces, do you know of anything else I could check?

Jeanette

IIf([SCH ADV NETWORK New]="No",0)

The above doesn't say what to do if it doesn't ="No".

I'd also doube check that the data in the table is actually "Yes New". A
leading or trailing space would cause a different return as would an extra
space between the words.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following expression:

Expr1: IIf([SCHW ADV NETWORK New]="Yes",[TOTAL FEE]*0.15,IIf([SCH ADV
NETWORK New]="Yes New",[SAN FEE],IIf([SCH ADV NETWORK New]="No",0)))

The first part of the expression works when SCHW ADV NETWORK New equals
"Yes", it does not work when it equals ''Yes New", but I do not get an error
either. What am I doing wrong? Please help.

Jeanette

 

[Guest]

You still need to handle the No as all three arguments of an IIf statement
are required. What happens if there is something that isn't Yes, Yes New, or
No? With a nested IIf you still need to handle all the arguments. Also the
way an IIf works, it doesn't necessarily stop evaluating the statement if it
runs into Yes or Yes New. It could still be going all the way down, and past,
No.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I do not want anything to happen if it is "No". I checked for leading and
trailing spaces, do you know of anything else I could check?

Jeanette

IIf([SCH ADV NETWORK New]="No",0)

The above doesn't say what to do if it doesn't ="No".

I'd also doube check that the data in the table is actually "Yes New". A
leading or trailing space would cause a different return as would an extra
space between the words.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have the following expression:

Expr1: IIf([SCHW ADV NETWORK New]="Yes",[TOTAL FEE]*0.15,IIf([SCH ADV
NETWORK New]="Yes New",[SAN FEE],IIf([SCH ADV NETWORK New]="No",0)))

The first part of the expression works when SCHW ADV NETWORK New equals
"Yes", it does not work when it equals ''Yes New", but I do not get an error
either. What am I doing wrong? Please help.

Jeanette

 

[Guest]

Jerry:

I believe it is working now. When I open or run the query a box pops up and
it reads: Enter Parameter Value
SCH ADV NETWORK

I then need to input Yes New in order for it to calculate for Yes New
clients, if I don't type this value in, it does not calculate for the Yes New
clients.

Any thoughts...

Jeanette

You still need to handle the No as all three arguments of an IIf statement
are required. What happens if there is something that isn't Yes, Yes New, or
No? With a nested IIf you still need to handle all the arguments. Also the
way an IIf works, it doesn't necessarily stop evaluating the statement if it
runs into Yes or Yes New. It could still be going all the way down, and past,
No.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I do not want anything to happen if it is "No". I checked for leading and
trailing spaces, do you know of anything else I could check?

Jeanette

IIf([SCH ADV NETWORK New]="No",0)

The above doesn't say what to do if it doesn't ="No".

I'd also doube check that the data in the table is actually "Yes New". A
leading or trailing space would cause a different return as would an extra
space between the words.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have the following expression:

Expr1: IIf([SCHW ADV NETWORK New]="Yes",[TOTAL FEE]*0.15,IIf([SCH ADV
NETWORK New]="Yes New",[SAN FEE],IIf([SCH ADV NETWORK New]="No",0)))

The first part of the expression works when SCHW ADV NETWORK New equals
"Yes", it does not work when it equals ''Yes New", but I do not get an error
either. What am I doing wrong? Please help.

Jeanette

 

[John Spencer]

Well, it could be that you have a name problem.
Do you have two fields - SCHW ADV NETWORK New and SCH ADV NETWORK New?
Note the change from SCHW to SCH.

If not, try fixing the field names. Also, I would explicitly return Null
for the last argument. A query will handle the fact that you didn't do so,
but elsewhere you could have a problem develop.

Does this work as required?

Expr1: IIf([SCHW ADV NETWORK New]="Yes",[TOTAL FEE]*0.15,
IIf([SCHW ADV NETWORK New]="Yes New",[SAN FEE],
IIf([SCHW ADV NETWORK New]="No",0, Null)))


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Something in the query is looking for SCH ADV NETWORK which is not a field in
the table. If this is actually a report, you might also be sorting on a
non-existant SCH ADV NETWORK field.

Other than that, SCH ADV NETWORK is in the criteria of the query and is a
parameter use to prompt you for criteria. In other words, that's how it's
suppose to work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Jerry:

I believe it is working now. When I open or run the query a box pops up and
it reads: Enter Parameter Value
SCH ADV NETWORK

I then need to input Yes New in order for it to calculate for Yes New
clients, if I don't type this value in, it does not calculate for the Yes New
clients.

Any thoughts...

Jeanette

You still need to handle the No as all three arguments of an IIf statement
are required. What happens if there is something that isn't Yes, Yes New, or
No? With a nested IIf you still need to handle all the arguments. Also the
way an IIf works, it doesn't necessarily stop evaluating the statement if it
runs into Yes or Yes New. It could still be going all the way down, and past,
No.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I do not want anything to happen if it is "No". I checked for leading and
trailing spaces, do you know of anything else I could check?

Jeanette

:

IIf([SCH ADV NETWORK New]="No",0)

The above doesn't say what to do if it doesn't ="No".

I'd also doube check that the data in the table is actually "Yes New". A
leading or trailing space would cause a different return as would an extra
space between the words.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have the following expression:

Expr1: IIf([SCHW ADV NETWORK New]="Yes",[TOTAL FEE]*0.15,IIf([SCH ADV
NETWORK New]="Yes New",[SAN FEE],IIf([SCH ADV NETWORK New]="No",0)))

The first part of the expression works when SCHW ADV NETWORK New equals
"Yes", it does not work when it equals ''Yes New", but I do not get an error
either. What am I doing wrong? Please help.

Jeanette