in Access 2013 use criteria to check result of expression

[George Pollow]

I am trying to suppress records with 0 inventory. This is my expression:
avail: ([SumOfIn]-[SumOfOut])



What do I key on the Criteria line to suppress records with 0 in "avail"

[JeffChr]

"If you use the Nz function in an expression in a query without using the valueifnull argument, the results will be a zero-length string in the fields that contain null values." http://msdn.microsoft.com/en-us/library/office/ff195223%28v=office.15%29.aspx

I think you need a valueifnull argument because you cannot subtract two strings: (Nz([SumOfIn], 0)-Nz([SumOfOut], 0))

and your criteria could be: Not 0

[George Pollow]

JeffChr

I do not understand your answer so I changed my expression to eliminate the NZ:
avail: ([SumOfIn]-[SumOfOut])

any idea for Criteria?

[June7]

If you want to test for 0 then probably should use the Nz() function because arithmetic with Null results in Null. If either Sum is Null then the calc result will be Null.

Why don't you try the expression Jeff provided?

[George Pollow]

june7

The query runs correctly, without the NZ function, showing records with 0 inventory. I need to find a way to suppress those records.

[JeffChr]

Place "Not 0" in the criteria cell for the [avail] field in your query (don't include the quotes)

[George Pollow]

JeffChr,

When I do that I get a box that says "Enter Parameter Value" for sumofin.

[June7]

Post the complete SQL statement.

[George Pollow]

June7,

I don't do SQL. I just enter data on the database. My expression is avail: ([SumOfIn]-[SumOfOut])

[June7]

You have a query object then you do have SQL. Query objects use SQL. Switch to SQL View to see the statement.

If you want to provide db for analysis, follow instructions at bottom of my post.

[George Pollow]

SELECT [Pubs Subject].Subject, [Pubs Basic].Title, [Pubs Basic].Comment, Sum([Pubs Transaction].[In]) AS SumOfIn, Sum([Pubs Transaction].Out) AS SumOfOut, ([SumOfIn]-[SumOfOut]) AS avail
FROM ([Pubs Basic] INNER JOIN [Pubs Transaction] ON [Pubs Basic].[Rec_Nbr] = [Pubs Transaction].[Rec_Nbr]) INNER JOIN [Pubs Subject] ON [Pubs Basic].Rec_Nbr = [Pubs Subject].Rec_Nbr
GROUP BY [Pubs Subject].Subject, [Pubs Basic].Title, [Pubs Basic].Comment;

[June7]

I don't use many aggregate queries and can't remember if filter criteria can be applied to a field calculated from two aggregate fields. Apparently there is an issue with that.

Think I will have to work with your db to further analyze this issue.

[George Pollow]

Thanks for trying.

[George Pollow]

this has been resolved

[June7]

Want to share how?