Not equal to zero

[Jessy MM Jules]

Hi
I have access 2007 and have set up a simple table called orders. Within the
table I have set up Order ID, Order Date, Cutomers Name, Total Sales
(currency), Payment Amount (currency), Payment date. I have entered the
revelvant details.

I have set up a query where I have have selected the Customer name, total
sales, payment amount from the table above. In design view I have added a
new column and used the expression builder where I have used total sales -
payment amount and have got my answer which is all correct (negative totals
included ie paid more than owe).

Now I would like to show only those amounts that have are not zero and can
not seem to figure out how to do this. I feel it should be simple but I have
tried >0 in the crieteria etc and many other variations including using the
expression builder again in the criteria field, but it is not returning what
I would like.

Thanks for any help
Jessy

 

[John W. Vinson]

Now I would like to show only those amounts that have are not zero and can
not seem to figure out how to do this. I feel it should be simple but I have
tried >0 in the crieteria etc and many other variations including using the
expression builder again in the criteria field, but it is not returning what
I would like.

well... what exactly ARE you doing? Could you post the SQL of the query,
indicate something about the data in the table, what it's returning and what
it should be returning?

If you might have NULL values they're not equal to zero; and if you have
negative values they're of course not greater than zero. A criterion of

<> 0 OR IS NULL

may be more appropriate.

 

[Jessy MM Jules]

Hi John (thanks for responding )

This is what I have been asked to do for my assignment.

***Create a query (or calculated field) to calculate 'Outstanding balance'.
'Outstanding balance' is 'Total Sale' minus 'Payment amount'.
Select those records where the balance is 'not equal to' zero.***

The query will be based on the table which has Order ID, Order Date,
Customer Name, Total Sale, Payment Amount, Payment Date.

Not sure what you mean by SQL but I have copied this from the SQL view in
the query:

SELECT Orders.CustomerName, Sum(Orders.TotalSale) AS SumOfTotalSale,
Sum(Orders.PaymentAmount) AS SumOfPaymentAmount,
[SumOfTotalSale]-[SumOfPaymentAmount] AS [Outstanding Balance]
FROM Orders
GROUP BY Orders.CustomerName;

Regards
Jessy

 

[John W. Vinson]

Hi John (thanks for responding )

This is what I have been asked to do for my assignment.

Well... it's YOUR homework not mine...

***Create a query (or calculated field) to calculate 'Outstanding balance'.
'Outstanding balance' is 'Total Sale' minus 'Payment amount'.
Select those records where the balance is 'not equal to' zero.***

The query will be based on the table which has Order ID, Order Date,
Customer Name, Total Sale, Payment Amount, Payment Date.

Not sure what you mean by SQL but I have copied this from the SQL view in
the query:

SELECT Orders.CustomerName, Sum(Orders.TotalSale) AS SumOfTotalSale,
Sum(Orders.PaymentAmount) AS SumOfPaymentAmount,
[SumOfTotalSale]-[SumOfPaymentAmount] AS [Outstanding Balance]
FROM Orders
GROUP BY Orders.CustomerName;

Regards
Jessy

This query has no criteria. Open it in the query grid and look at the Criteria
line. It should have something like what I suggested in the Criteria box under
Outstanding Balance.

Does that help?

I'll be leaving for a couple of weeks tomorrow morning so if you need more
help start a new thread.