Using 'Not Equal' in a Totals Query

[jas580]

Hi,

I have a totals query with the following:

Date, Max
Amount, Sum
Type, Where (IIf([Forms]![frmLookup]![checkbox]=1,[Type],[Type]<>3)

The query works perfectly when the checkbox=1. But, when it =0, the query
returns empty Date and Amount fields. (Type is selected to be not showing).
The query also worked perfectly when I didn't have the checkbox feature.

I need it to sum all records not of type 3 when the checkbox=0 and can't
figure it out. Does it have something to do with <> in a totals query?
Please help!

Thanks,
Jeff

 

[Michel Walsh]

The value of a checkbox is either 0, either -1, not +1.

If you edit the query in SQL view, you should change it to see something
like:

SELECT MAX(date), SUM(amount)
FROM ...
WHERE IIf( Forms!frmLookup!checkbox, true, Type <>3)



which sums over all the records if Forms!frmLookup!checkbox is checked,
else, it sums only over those where type <> 3.



Hoping it may help,
Vanderghast, Access MVP

 

[Amy Blankenship]

The value of a checkbox is either 0, either -1, not +1.

If you edit the query in SQL view, you should change it to see something
like:

SELECT MAX(date), SUM(amount)
FROM ...
WHERE IIf( Forms!frmLookup!checkbox, true, Type <>3)

I don;t think that will work, because I don't think that "Type<>3" is added
to the where clause as a result of that function. You have to use LIKE to
get IIF functions to work in this manner, and the IIF function would not be
able to make the required change from LIKE to NOT LIKE.

Instead, I think you need to use the OR rows in the Criteria. I've never
tried this with form controls, but I think you "pretend" they are fields in
the query in the way that this KB article does with Query Parameters:

http://support.microsoft.com/kb/290178

HTH;

Amy

 

[Michel Walsh]

In fact


IIf( Forms!frmLookup!checkbox, true, Type <>3)


is a computed expression, which returns a Boolean. If the check box is true
(checked) it returns true, for all records. If the check box is false
(unchecked), the expression returns the result of

Type <> 3


So, essentially, the expression is the same as


Forms!frmLookup!checkbox OR type <> 3





Vanderghast, Access MVP