Link to home
Start Free TrialLog in
Avatar of CPChem1

asked on

What criteria do I use in a query to get a negative number from a table?

MS Access 2010

I have a user who:

Runs a query ,"Over1YearPastDueDebits". (Debits are what other companies owe us and reflects a positive number in a table named 2012RAW)  

And runs a query, "Over1YearPastDueCredits". (Credits are what we owe other companies and reflects a negative number in a table named 2012RAW)

He is trying pull all of the negative numbers (Credits) from 2012RAW table using the Over1YearPastDueCredits, but it idoes not get all of them. It looks like it is getting all of the Debits (psoitive) numbers, but leaves our some of hte negative ones.

My question is: What Criteria do we use to pull all negative numbers from the table? The negative numbers have a - in front of it. We have used the <0 and Not >0, but it returns the same # of records.

Any ideas?

Thank you,
Avatar of Neil Russell
Neil Russell
Flag of United Kingdom of Great Britain and Northern Ireland image

That will depend on your table!! As you have told us nothing except the name of two queries your asking a lot!

Post the two current queries so we can have a clue please
Avatar of CPChem1


Sorry! How do I post the queries?
erm.... Cut and paste?
Attach as text files?
Avatar of Patrick Matthews

Typically, we would need you to open the query, switch to SQL View, and then copy and paste the SQL statement here.

Without the query, and perhaps also without a sample database, it's hard to answer your question.

Generally, to get just negative numbers:

FROM SomeTable
WHERE SomeColumn < 0
Avatar of CPChem1


Thank you so much, matthewspatrick! I have not been in Access for a little while.

Please find a copy of the SQL stmt.

SELECT CompanyCodes.Company, [2012RAW].CoCd, [2012RAW].Account, SoldToNameNumber.[Customer Name], [2012RAW].Docno, [2012RAW].[Inv ref], [2012RAW].Type, [2012RAW].PK, [2012RAW].RCd, [2012RAW].Text, [2012RAW].[Pstg date], [2012RAW].[Bline date], [2012RAW].[Net due dt], [2012RAW].Arrers, [2012RAW].[Amt in loccur], [2012RAW].LCurr
FROM (2012RAW LEFT JOIN SoldToNameNumber ON [2012RAW].Account = SoldToNameNumber.[Customer No]) INNER JOIN CompanyCodes ON [2012RAW].CoCd = CompanyCodes.[Co Cd]
WHERE ((([2012RAW].[Net due dt])<#10/1/2011#) AND (Not ([2012RAW].[Amt in loccur])>0))
ORDER BY [2012RAW].CoCd, [2012RAW].Account;
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CPChem1


Thank again, matthewspatrick!  I tried this and it still returns 74. I agree that yours is s better way to write it! :-)  

Just for giggles if I want to write hte criteria for a negative number is the way to do that using  <0 ?  I watn to be sure that I am not leaving anyting out.
Avatar of CPChem1


Sorry this took so long!