Union Query to return Null

[dr4ke]

Hi, I recently learnt of the UNION query but am now having some difficulty implementing it to do exactly what I want! Currently the below works, but only when I take 'Query 4' out of the equation - because Query 4 holds some '#Error' and '0' values (which is fine! It does so because of the calculations within the sheet which are trying to divide by 0 because of the data in the table - this may change hense why i'm still keeping the query).



How can I make a Union query show me the fields whether they have values or not? I'm presuming it wont work because of the 'error' value...

 SELECT 
[SANName],[AggregateName], [DailyIncreaseGB], [TimeLeft], [UsedPercentage]
FROM [Query1];
UNION SELECT [SANName],[AggregateName], [DailyIncreaseGB], [TimeLeft], [UsedPercentage]
FROM [Query2];
UNION SELECT [SANName],[AggregateName], [DailyIncreaseGB], [TimeLeft], [UsedPercentage]
FROM [Query3];
UNION SELECT [SANName],[AggregateName], [DailyIncreaseGB], [TimeLeft], [UsedPercentage]
FROM [Query4];

[JoeM]

Have you tried fixing the errors in your underlying queries?

For example, let's say you have a division by zero error. You can change the formula so that it returns 0 instead of an error like this.
Instead of having:

UsedPercentage: [Field1]/[Field2]

try something like this:

UsedPercentage: IIF([Field2]=0,0,[Field1]/[Field2])+0

[dr4ke]

Thank you :-) I will try that very shortly!!

[dr4ke]

It doesn't seem to be working

 IIF([DailyIncreaseGB]=0,0,(Int(Round(LFAS01Aggr0DateComparison.LastFreeGB)/(DailyIncreaseGB),1)))+0

Daily Increase is what returns 0 in the query when dividing the used difference by the date difference.

Then You divide the Free memory by the Daily Increase to get a Time Left.

Any thoughts?

[dr4ke]

I seem to have it working

 IIF([DailyincreaseGB]=0,0,(Int(Round((LFAS01Aggr0DateComparison.LastFreeGB)/(DailyIncreaseGB),1)))) AS TimeLeft

but don't have the '+0' in it -- what does this do?

[JoeM]

but don't have the '+0' in it -- what does this do?

The NZ function, by default, returns a text value. So adding zero just coerces it to return a numeric value.

[dr4ke]

Sorry; the NZ Function??

Where would I put the +0 in this code please?

 IIF([DailyincreaseGB]=0,0,(Int(Round((LFAS01Aggr0DateComparison.LastFreeGB)/(DailyIncreaseGB),1)))) AS TimeLeft

[JoeM]

Wow, I must have "Monday morning brain" today!

I was confusing this with another thread I was helping out on. Of course, you aren't using the NZ function, so you can ignore that statement.
How you have written the formula in post #5 should work fine.

Sorry for the confusion!

[dr4ke]

Aha well it's now Friday morning and you've just given me my first chuckle of the day! So thanks for that :-)

Thanks for all your help! It's very much appreciated.