Unanswered: Expression Builder (iif & Or functions)
I need help with an expression in an Access Query. Here is my current expression:
Weeks Supply: IIf([Total Global inventory]<1,0,[Total Global inventory]/[Weekly Avg])
This is great but there are some fields with a "Weekly Avg" of zero so I'm getting "#error" in the field. The above expression prevents "#error" results when the "Total Global Inventory" is zero but doesn't help with the "Weekly Avg" being zero.
Is there a way to say if "Total Global Inventory" or "Weekly Avg" is zero to put a zero in the field. If not, then perform the calculation.
Thanks so much. I just had to change the "Is Null" to "<1" and it worked.
Also, regarding the spaces, I know but the table is linked to a spreadsheet that is updated daily from a systems data dump. This is how they have the field names in the data dump. At this point, I just left it as is so I would not have to manually go in and change the field names everyday. I need to get IT to automatically remove the spaces for me so that no extra steps need to be performed.
Oh, the "Weekly Avg" is part of another query that calculates this field. I created this secondary query from two other querys. The "weekly avg" was just inserted into this secondary query. I'm not sure if that is a bad thing or not but it is not contained in a table because it requires being calculated.