Not Including "0" fields

[mcmichael13]

I have an RMA database, and this particular query is used to create a pivot chart. I have the query pulling data from the table based on a "selection form" of a date range and location of RMA.

Now my problem is, that associated with the RMA is the total value. I want to not include the records where the RMA value is entered as 0.00, but I'm not sure how to do that in the query. I have tried various forms of "is null" "is not null" etc. I'm just unsure as to how to get what I want.

Thanks in advance for your help.

[Edit]

This is my sql as it stands now...

SELECT RMA.DateReceived, RMA.Location, RMA.TotalValue, RMA.RMA, RMA.ReasonforReturn
FROM RMA
WHERE (((RMA.DateReceived) Between [Forms]![DefectCodeRMA]![StartDate] And [Forms]![DefectCodeRMA]![EndDate]) AND ((RMA.Location)=[Forms]![DefectCodeRMA]![Location])) OR (((RMA.DateReceived) Between [Forms]![DefectCodeRMA]![StartDate] And [Forms]![DefectCodeRMA]![EndDate]) AND (([Forms]![DefectCodeRMA]![Location]) Is Null))
ORDER BY RMA.DateReceived;

 

[Alc]

Does this work?

SELECT RMA.DateReceived, RMA.Location, RMA.TotalValue, RMA.RMA, RMA.ReasonforReturn
FROM RMA
WHERE (((RMA.DateReceived) Between [Forms]![DefectCodeRMA]![StartDate] And [Forms]![DefectCodeRMA]![EndDate]) AND ((RMA.Location)=[Forms]![DefectCodeRMA]![Location])) OR (((RMA.DateReceived) Between [Forms]![DefectCodeRMA]![StartDate] And [Forms]![DefectCodeRMA]![EndDate]) AND (IsNull([Forms]![DefectCodeRMA]![Location])))
ORDER BY RMA.DateReceived;

[/quote]

 

[mcmichael13]

oh i apologize for the confusion... that sql works just fine...

i want to add another "AND" in the "WHERE" clause... something like

WHERE (((RMA.DateReceived) Between [Forms]![DefectCodeRMA]![StartDate] And [Forms]![DefectCodeRMA]![EndDate]) AND ((RMA.Location)=[Forms]![DefectCodeRMA]![Location]) AND ((RMA.TotalValue)>0)) OR (((RMA.DateReceived) Between

notice the ((RMA.Location)>0))

but that doesn't filter out the 0 fields that i want it to. It runs fine, just doesn't do it correctly

 

[JANR]

I want to not include the records where the RMA value is entered as 0.00

If a field has a value of NULL then it is empty so a Is Null test against a field with a value of 0 or 0.00 will fail.

so

...([Forms]![DefectCodeRMA]![Location] > 0)))

or

(...[Forms]![DefectCodeRMA]![Location] > 0.00)))

Perhaps??

JR

 

[mcmichael13]

right, thats what i realized too... but its the (RMA.TotalValue) that i want greater than 0, as you can see in my last post.

however it still queries the values into the table. not sure why

 

[Alc]

Does '<> 0' also not work?
How about (Not IsNull(RMA.TotalValue) and RMA.TotalValue <> 0).

 

[mcmichael13]

no, the query is still including these records... hmmm

 

[Alc]

May be a stupid question, but is the field definitely numeric?

 

[mcmichael13]

yes it is numeric... this is confusing haha thanks for your help though

 

[ByteMyzer]

Try:
Nz([Forms]![DefectCodeRMA]![Location], 0) > 0

 

[mcmichael13]

I GOT IT!!!!!!!!!!

I needed to put the ">0" under the "OR"... if someone could explain why, that'd be awesome, but this is my working code now:

SELECT RMA.DateReceived, RMA.Location, RMA.TotalValue, RMA.RMA, RMA.ReasonforReturn
FROM RMA
WHERE (((RMA.DateReceived) Between [Forms]![ReportSelection]![StartDate] And [Forms]![ReportSelection]![EndDate]) AND ((RMA.Location)=[Forms]![ReportSelection]![Location])) OR (((RMA.DateReceived) Between [Forms]![ReportSelection]![StartDate] And [Forms]![ReportSelection]![EndDate]) AND ((RMA.TotalValue)>0) AND (([Forms]![ReportSelection]![Location]) Is Null))
ORDER BY RMA.TotalValue DESC;

again, thanks for your help