is not null and >0

[Robin Hood]

Hi
I wonder if anyone can help.

I have made a query to count the payment field criteria stating "is not null and >0"

So that I know the number of payments made for each sales person on the phone.
Unfortunately the query picks up the fields that have 0 in them as a payment and counts them to give false reading.
I don't want the query to count blank fields and fields with 0 in them.
the query is part of a database report that will keep track and updated automatically as the table its getting the info from changes daily.

many thanks in advance

Serge

 

[Big Pat]

I think all you need is >0 without quotes in the criteria line. Any value that is greater then zero can't be null, so that part is redundant

 

[pr2-eugin]

Could you post the Current Query you have??

 

[Robin Hood]

Hi thanks for your replies,

how do i copy and post my query to the forum?

Serge

 

[pr2-eugin]

Just go to the Query DESIGN and switch the view to SQL, you should be able to copy it from there..

 

[Robin Hood]

Thanks Paul,

Here it is.

SELECT [Agents ID].Name, Count(Collect.CAPPEAL) AS CountOfCAPPEAL, DatePart("m",[CAPPDATE_S]) AS expr4, [Agents ID].[Agent ID]
FROM Collect INNER JOIN [Agents ID] ON Collect.AgentId = [Agents ID].[Agent ID]
WHERE (((Collect.ccolamt) Is Not Null And (Collect.ccolamt)>0) AND ((Collect.CAPPDATE_S)>#1/1/2013#))
GROUP BY [Agents ID].Name, DatePart("m",[CAPPDATE_S]), [Agents ID].[Agent ID];

the bit in reds is whats causing me problems.

thanks

Serge

 

[Big Pat]

I'm not good at reading SQL like this, but try this

Open your query in design view

On the criteria line under ccolamt enter >0
On the criteria line under CAPPDATE enter >#1/1/2013#

Make sure these are both on the SAME criteria line (which results in AND)

I *think* that should work

 

[pr2-eugin]

What is this portion?

DatePart("m",[CAPPDATE_S])

Since it comes up as just nothing but a normal expression, change the GROUP BY to EXPRESSION in the Query Design Grid view..

 

[Robin Hood]

Hi,

DatePart("m",[CAPPDATE_S])

Is linking the date under the correct month. eg 12/12/12 would come under Dec.

cheers

Serge

 

[pr2-eugin]

So did you change it to EXPRESSION from the drop down list of TOTAL's available?