General discussion

Locked

Help with Access / SQL

By senftk ·
I have a table in access called retailers.
I want to build a query that will only return records where the Cost - payments made is >0
fields involved are [COST],[PAY AMOUNT 1],[PAY AMOUNT 2],[PAY AMOUNT 3],[PAY AMOUNT 4]

Here is what I tried and it returns no records even though there are records that match this criteria:

SELECT*
FROM Retailers
WHERE [COST]-[PAY AMOUNT 1]-[PAY AMOUNT 2]-[PAY AMOUNT 3]-[PAY AMOUNT 4] >0

Can someone clue me into the proper SQL statement needed?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by ReWrite In reply to Help with Access / SQL

It should read like this:

select * from retailers where [cost]>0 or [pay amount 1]>0 or [pay amount 2]>0 or [pay amount 3]>0 or [pay amount 4]>0

This will give you a result set where any of the 5 fields is greater than 0. Not sure if that's what you really want or not based on your question.

Cheers.

RW

Collapse -

by senftk In reply to

I found the problem. The query was correct, it just didn't return results because I had used IF statements to make 0 values return "". The null value couldn't be calculated. I changed the values back to 0's and now everything works.

Collapse -

by senftk In reply to Help with Access / SQL

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums