Is there an error in the syntax of this expression? (1 Viewer)

Rich_B · Feb 17, 2016

Hi

I have the following expression:

=IIf([WEIGHT CALC]<>"Select Material",Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

The [WEIGHT CALC] cell does not contain "Select Material" so I am expecting the first part of the code to execute but instead it jumps to the final part and returns "-".

Where am I going wrong?

Thank you.

bob fitz · Feb 17, 2016

What does [WEIGHT CALC] contain

bob fitz · Feb 17, 2016

If [WEIGHT CALC] is null then it will be different to "Select Material"

Try:
=IIf(Nz([WEIGHT CALC])<>"Select Material",Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

Rich_B · Feb 17, 2016

bob fitz said:
If [WEIGHT CALC] is null then it will be different to "Select Material"

Try:
=IIf(Nz([WEIGHT CALC])<>"Select Material",Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

Hi

Thank you for your input.

[WEIGHT CALC] contains a value (in this case 2200) which is the result of another expression.

When I use the Nz version you suggested it doesn't return anything, the cell is blank.

Mile-O · Feb 17, 2016

IIf([WEIGHT CALC]<>"Select Material"

[WEIGHT CALC] contains a value (in this case 2200)

Something to note: you are comparing apples with oranges.

Weight Calc is a value of 2200. "Select Material" is a text string. Or in other words, number = text is not a valid comparison.

Rich_B · Feb 17, 2016

Mile-O said:
Something to note: you are comparing apples with oranges.

Weight Calc is a value of 2200. "Select Material" is a text string. Or in other words, number = text is not a valid comparison.

O.K. thank you for this tip.

Any advice as to how to get around it?

JHB · Feb 17, 2016

Rich_B said:
..
Any advice as to how to get around it?

What about -1 instead of?

Rich_B · Feb 17, 2016

JHB said:
What about -1 instead of?

Thank you for your input.

So instead of the "Select Material" prompt, replace with -1?

Grumm · Feb 17, 2016

Maybe you can do it like this :

Code:

=IIf(Cstr(Nz([WEIGHT CALC],0))<>"Select Material",Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

Rich_B · Feb 17, 2016

Grumm said:
Maybe you can do it like this :

Code:

=IIf(Cstr(Nz([WEIGHT CALC],0))<>"Select Material",Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

Thank you for your input.

Unfortunately this made the cell return nothing.

Rich_B · Feb 17, 2016

JHB said:
What about -1 instead of?

Thank you for the suggestion but it didn't make a difference.

Rich_B · Feb 17, 2016

O.K. so I have reduced this expression to the following:

=IIf([WEIGHT CALC]>"0",Sum(140*150*1200),"-")

Even as simplified as this it only returns "-".

[WEIGHT CALC] definitely contains value "2200" which is in number format.

Surely the syntax must be wrong somewhere?

Mile-O · Feb 17, 2016

Rich_B said:
=IIf([WEIGHT CALC]>"0",Sum(140*150*1200),"-")

What happens when you take out Sum( ) and just leave 140*15*1200? It seems redundant there and may be causing an issue.

Code:

IIf(IsNumeric([WEIGHT CALC]), 140*150*1200, 0)

If you change the textbox's Format value to #,##0;[Red](#,##0);- it will turn your 0 to a hyphen.

Minty · Feb 17, 2016

You can't evaluate >"0" with "2200" as a comparison.

If they are both strings it won't work, and if one is a string and one is a number it won't work. You need to use a evaluation that has a guaranteed result.

Rich_B · Feb 17, 2016

Minty said:
You can't evaluate >"0" with "2200" as a comparison.

If they are both strings it won't work, and if one is a string and one is a number it won't work. You need to use a evaluation that has a guaranteed result.

I'm not sure I understand?

I have both now set to number and I'm getting the same result.

Is this what you meant?

Thank you for your help.

arnelgp · Feb 17, 2016

IIf(Instr([WEIGHT CALC] & "","Select Material")=0,Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

Grumm · Feb 17, 2016

What about this :

Code:

=IIf(StrComp(Cstr(Nz([WEIGHT CALC],0)),"Select Material")<>0,Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

Still overkill and it is probably faster for you to just change the datatype of the field WEIGHT CALC to only accept numbers...

smig · Mar 19, 2016

Rich_B said:
Hi

I have the following expression:

=IIf([WEIGHT CALC]<>"Select Material",Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")

The [WEIGHT CALC] cell does not contain "Select Material" so I am expecting the first part of the code to execute but instead it jumps to the final part and returns "-".

Where am I going wrong?

Thank you.

This will only work if the field [WEIGHT CALC] will hold the words "Select Material"
I guess its not what you want.
What do you want to compare realy ?

Is there an error in the syntax of this expression? (1 Viewer)

Rich_B

Access Newbie

bob fitz

AWF VIP

bob fitz

AWF VIP

Rich_B

Access Newbie

Mile-O

Back once again...

Rich_B

Access Newbie

JHB

Have been here a while

Rich_B

Access Newbie

Grumm

Registered User.

Rich_B

Access Newbie

Rich_B

Access Newbie

Rich_B

Access Newbie

Mile-O

Back once again...

Minty

AWF VIP

Rich_B

Access Newbie

arnelgp

..forever waiting... waiting for jellybean!

Grumm

Registered User.

smig

Registered User.

Similar threads

Users who are viewing this thread