Solved Expression to ignore nulls (1 Viewer)

tmyers

Well-known member
Local time
Today, 09:47
Joined
Sep 8, 2020
Messages
1,090
How would you correctly write an expression to return values, but ignore nulls? The problem I have run into is I have (field1*field2)+field3, but sometimes field2 can be empty/null. So until both field 1 and 2 have numbers, the calculated field doesn't return a value which is messing with various items I am trying to display.

I think this would be more of an "Excel" type structure with the IIF function, but am unsure.
 

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
8,738
So what do you actually want to do?

Impute a value (like 0) for field2, and continue completing the calculation?
Or skip doing the calculation entirely (and return [something?] for the destination field), if field2 is Null?
 

tmyers

Well-known member
Local time
Today, 09:47
Joined
Sep 8, 2020
Messages
1,090
I need the calculation to complete. I am adding pricing together then multiplying by quantity.
The expression currently is [Price]+[Freight]+([LampPrice]*[NumberOfLamps]). This does not return anything unless all fields have a value, but not all fields are required to have one. Sometimes freight is prepaid whereas other times lamps are not needed etc.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:47
Joined
Oct 29, 2018
Messages
21,358
I need the calculation to complete. I am adding pricing together then multiplying by quantity.
The expression currently is [Price]+[Freight]+([LampPrice]*[NumberOfLamps]). This does not return anything unless all fields have a value, but not all fields are required to have one. Sometimes freight is prepaid whereas other times lamps are not needed etc.
Hi. I think the question was if the value was null, what value did you want to use in its place to make the calculation continue?
 

Minty

AWF VIP
Local time
Today, 13:47
Joined
Jul 26, 2013
Messages
10,355
The NZ() function is your friend - it will replace a null with something else.
[Price]+[Freight]+([LampPrice]*[NumberOfLamps]
[Price]+NZ([Freight],0)+([LampPrice]*NZ([NumberOfLamps],0)).

Would replace a missing freight or missing No Of lamps value with 0
 

tmyers

Well-known member
Local time
Today, 09:47
Joined
Sep 8, 2020
Messages
1,090
Minty is correct in that I want it to just be 0 so that the rest of the expression calculates. There are, and will be a lot of instances where the only value in the entire expression is [Price]. The rest could be zero, as they are not needed.
 

tmyers

Well-known member
Local time
Today, 09:47
Joined
Sep 8, 2020
Messages
1,090
I tried what you posted Minty, and it threw an error saying I can not use that in a calculated column.

Edit:
I needed to do the IIF(IsNull) function. For once, something actually worked like Excel. Access doesnt allow NZ to be used for calculate fields.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
8,738
I tried what you posted Minty, and it threw an error saying I can not use that in a calculated column.

Edit:
I needed to do the IIF(IsNull) function. For once, something actually worked like Excel. Access doesnt allow NZ to be used for calculate fields.
You mean this is for a calculated column in a table? Normally you should avoid using calculated columns in tables, and just calculate them at run time as needed.

Another tip, longer-term on this issue, consider defaulting these columns' values to 0 rather than leaving them Null.
I have no particular "over arching opinion" on the debate of allowing Nulls or not, but, in some cases it definitely makes sense to default certain columns to zero in the absence of anything else........IF the business meaning of that Null really IS "zero", which I think in your case it is. If freight is not needed then the freight cost is zero.
 

tmyers

Well-known member
Local time
Today, 09:47
Joined
Sep 8, 2020
Messages
1,090
Should I do a query for it rather than a calculated field on the table itself?
 

Isaac

Lifelong Learner
Local time
Today, 06:47
Joined
Mar 14, 2017
Messages
8,738
Yes
The query environment gives you "all" design options, and doesn't limit you as much. And is a better idea for other reasons.
 

tmyers

Well-known member
Local time
Today, 09:47
Joined
Sep 8, 2020
Messages
1,090
Got it. I will make that design change. I learn something every day!
 

Users who are viewing this thread

Top Bottom