Is there an error in the syntax of this expression?

R

Rich_B

Access Newbie
Local time
Today, 06:40
Joined
Feb 12, 2016
Messages
50
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.
 
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),"-")
 
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),"-")
Click to expand...

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.
 
IIf([WEIGHT CALC]<>"Select Material"
Click to expand...
[WEIGHT CALC] contains a value (in this case 2200)
Click to expand...

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.
 
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.
Click to expand...

O.K. thank you for this tip.

Any advice as to how to get around it?
 
Maybe you can do it like this :
Code: 
=IIf(Cstr(Nz([WEIGHT CALC],0))<>"Select Material",Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")
 
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),"-")
Click to expand...

Thank you for your input.

Unfortunately this made the cell return nothing.
 
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?
 
Rich_B said:
=IIf([WEIGHT CALC]>"0",Sum(140*150*1200),"-")
Click to expand...

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.
 
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.
 
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.
Click to expand...

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.
 
IIf(Instr([WEIGHT CALC] & "","Select Material")=0,Sum([H]*[W]*[L]*[WEIGHT CALC]/1000000000),"-")
 
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...
 
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.
Click to expand...

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 ?
 
You must log in or register to reply here.

Similar threads

S
Solved Using a Does Not Contain Criteria in an IIf Statement
Replies
9
Views
523
Pat Hartman
P
D
Solved Syntax error in date in query expression
Replies
8
Views
574
DeWaal
D
F
Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function
Replies
8
Views
945
June7
June7
B
Use the value of a control to effectively act like a variable in CF Rule expressions.
Replies
16
Views
813
MajP
MajP
S
Text box in report to count field in a query/table.
Replies
15
Views
1,298
Skeu-Skeu
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom