IF correct THEN UNION

[June7]

Glad you found a solution. It occurs to me that the expression could error if DailyincreaseGB is 0 because with an IIf all terms of the expression must be able to be evaluated, even if it is not the term that returns the value. Handle the possibility of division by 0 with an IIf to provide an alternate value, in this example Null:

IIf([DailyincreaseGB]=0,'No Change',IIf([DailyincreaseGB]>0,'Decrease Of Used',Int(Round(LFAS02Aggr3DateComparison.LastFree GB/IIf(DailyIncreaseGB=0,Null,DailyIncreaseGB),1)))

[dr4ke]

Could you explain this please?

From what I know, which clearly isn't a hell of a lot ,

 IIf([DailyincreaseGB]=0,'No Change'

would surely stop it getting anywhere near to this point

 IIf(DailyIncreaseGB=0,Null

??

I don't understand how it could error if DailyIncreaseGB is '0'; surely that would be captured in the first part and return 'No Change'??

[June7]

Because ALL parts of an IIf are calculated. As stated, every term in the IIf must be able to be evaluated, even if it is not the part that returns value. Division by 0 will error so that term causes the entire expression to error.

You can test this in the Immediate Window of the VBA editor. Paste the following into the window and press Enter (with cursor at end of line).

?IIf(1=1,"Yes",1/1)

then do the same with

?IIf(1=1,"Yes",1/0)

What happens?

[dr4ke]

I get an error .... But I still don't fully understand. I don't know why it would ever divide by 0, if all possible circumstances have been covered (i.e. 1) Is it equal to 0? 2) Is it Bigger than 0? 3) Is it Less than 0?)

I'm not saying you're wrong, clearly you are not! I just would like to understand why this is the case, for future reference. Perhaps it's the below part i'm just not grasping??

Because ALL parts of an IIf are calculated. As stated, every term in the IIf must be able to be evaluated, even if it is not the part that returns value

[June7]

That's just the nature of the IIf function. Every equation in the statement will calculate and therefore must be a valid expression. If any part fails it all fails. Division by 0 is not valid so it throws error. I'm not the architect of VBA, I just use it and so must accept the fact and work around it.

If your data (raw or calculated DailyIncreaseGB) will never have a 0 value then no issue, however, it seems that 0 is getting sent to the IIf (that's why you have an =0 condition) and that is an issue to be dealt with.