Query calculated field with IIF statement

Question

I have an Access table called Snaps which I want to query from Excel in VBA. The following query works:

SELECT SUM(Cash) FROM Snaps WHERE Balance>50 AND SnapshotDate=#2020-06-30 00:00:00#

If I add another condition on a calculated field created in Access which I call IsEligible, and the value of which is a boolean, then, none of the following works:

SELECT SUM(EligibleNotionalBalance) FROM Snaps WHERE ArrearsStatus=Current AND SnapshotDate=#2020-06-30 00:00:00# AND IsEligible = -1

SELECT SUM(EligibleNotionalBalance) FROM Snaps WHERE ArrearsStatus=Current AND SnapshotDate=#2020-06-30 00:00:00# AND IsEligible = TRUE

SELECT SUM(EligibleNotionalBalance) FROM Snaps WHERE ArrearsStatus=Current AND SnapshotDate=#2020-06-30 00:00:00# And IsEligible = 'true'

I have add similar issues in the past, which I overcame by changing the formula in my calculated field. Essentially this consisted in removing the IIF statetement that I had. However, it only showed values when the result was False, which I don't really like. Does anyone have a guess?

Thanks!

Shouldn't SUM queries have a GROUP BY and maybe a HAVING clause too? — braX

Alexis Dussault Alexis Dussault · Accepted Answer · 2020-07-05T22:28:32

So, not sure about the Group by and Having in this case. However, it now works. It seems I had some connection issues, which I realised after shrinking the query to the most simple possible format. The " =-1 " actually works!

Query calculated field with IIF statement

1 Answers