Designed a query SQL below:

SELECT tblNewOrders.[Date taken], Sum(tblNewOrders.Upsell) AS [Total Upsell], Sum(tblNewOrders.Price) AS [Total Sales], Sum([tblNewOrders]![BI]=True) AS [BI Schs], Sum([tblNewOrders]![CS]=True) AS [CS Schs], Sum(DLookUp(" [tblNewOrders]![Upsell] ","tblNewOrders"," [tblNewOrders]![Product Code] = 1180 or 1177 or 1181 or 1182 or 1183 or 1184 or 5644")) AS [Int Supps]
FROM tblOrders LEFT JOIN tblNewOrders ON tblOrders.[Invoice Code] = tblNewOrders.[Invoice Number]
GROUP BY tblNewOrders.[Date taken]
HAVING (((tblNewOrders.[Date taken]) Is Not Null));

When I run the query the BI Schs and CS schs columns have the correct numbers but are negative instead of positive.

What am I doing wrong?

Hi Harrisw

Are the fields/columns BI Schs and CS schs Yes/No (True/False) fields? I would guess so ... and in that case it is understandable why you get a negative value, once you are aware of the fact that Access stores :

Yes as -1

No as 0

So if you sum 10 rows that have a value of -1 you get -10.

You therefore need to convert these fields from a negative to a positive value. You could multiply the sum by -1 or you could use the Abs function:

---------------------------------------------------------
This example uses the Abs function to compute the absolute value of a number.

Dim MyNumber
MyNumber = Abs(50.3) ' Returns 50.3.
MyNumber = Abs(-50.3) ' Returns 50.3.
------------------------------------------------------------

(Taken from Access 97 Help File).

HTH

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 09-13-2001).]

That explains it.

Cheers

The value of the [Int Supps] column is also incorrect because the following part of your DLookUp() is incorrect:
[tblNewOrders]![Product Code] = 1180 or 1177 or 1181 or 1182 or 1183 or 1184 or 5644

You must repeat the field name as part of each comparison as:
[tblNewOrders]![Product Code] = 1180 or [tblNewOrders]![Product Code] = 1177 or ...
or use the In operator:
[tblNewOrders]![Product Code] In (1180, 1177, 1181, 1182, 1183, 1184, 5644)

I also question your use of DLookUP() in this context at all. You will get the same value returned for each row of the query and moreover, the DLookUp() will recalculate for each row - very slowly.

You should use a separate totals query to sum [Int Supps] by [Date taken] and then join it to this query.

And one more thing. It is very poor practice to use spaces in your table or column names.