I have tried and tried but still can't work this out. How can I write an expression that ignores zeros in the table. I've tried all sorts and still can't do it. This being my last try:

Expr1: Avg(IIf([TestTable].[number]=0,Null,[TestTable].[number]))

Any pointers would be really appreciated.

hatstand,

In order for Me to get what you have above to work, I had to Group By another Field and/or use Expression in the Total row of the Query.

RAZMaddaz

Hi, Thanks for the reply.

I'm really lost with this. Do I create a query that removes zeros, by using >0. Then run a query on that query using average on that?

Can I see the SQL of your Query? View the Query in SQL and copy the SQL on a new Reply.

Note: If there are other Fields involved and you are just doing this and without grouping the Query by another Field, then what you originally had will not work, you will need to Group By another Field and use an expression too.

Here is an example.

Hi,

The SQL is based on a test table and query I made to get it working. So it's very basic. I'm basically looking to average the Number Field that has some zero values. But as Access treats them as a value. I would like to ignore them.

SELECT TestTable.Day, TestTable.Number
FROM TestTable;

Try the following in a new Query:

SELECT TestTable.Day, Avg(IIf([Number]=0,Null,[Number])) AS TheNumberAvg
FROM TestTable
GROUP BY TestTable.Day;

Look at the example I uploaded too.

Hi Hatstand

Strictly mathematically an average will include values that are 0
Hence an average for 0,0,0,0,0,4,5,6,7,8 should be 3 (sum of values)/Count of Values) (30/10)

Access treats a 0 as a valid result, if you have records that do not (yet) have a result then the entry in the record should be null hence
Average for _,_,_,_,_,4,5,6,7,8 = 6 (30/5) (where the '_' represents a null value)

Your code line Expr1: Avg(IIf([TestTable].[number]=0,Null,[TestTable].[number])) gave the result of 6 for average of 0,0,0,0,0,4,5,6,7,8 which is what I would have expected.

Can you give a little more detail, or a number set that is not giving you the expected result?

Cheers

Edit:
Just as a BTW, 'Number' is a reserved word in Access and you should try to avoid using it in Access field names.

I couldn't look at the example. I'm only using a works 2003 version. I think. I tried the SQL and it says 'Datatype mismatch in criteria expression'. I really can't see why.

I can't see why Expr1: Avg(IIf([TestTable].[number]=0,Null,[TestTable].[number])) doesn't work either. It gives a message 'Data type mismatch in criteria expression'. I am doing something really daft and just can't see?

Nothing should be in the Criteria. However if the Number Field in your Table is a Data Type of Text, then you will get that error.


I created the dbase in Access2003, so it should work. Here is another version in Access2000.

If you could upload a copy of your dbase, then maybe we could get a better understanding. However, if you do, do not include any private data.

Is the data type of your field a number?

Hi,
I've uploaded the file. It's just made up simple data to help me learn it. Before I use it in the 'real' world.

Hi hatstand

The Number field in the table has a data type of 'Text'

As all the figures in the table are integers, change the data type to 'Number' and you should be good to go.

hth

I've done it. I had the Number field as text. Doah! I really appreciate the time and patience to help me. It's very much appreciated.

Once again a big thanks.

Raz and I are always pleased to help

Awesome!!! Glad we could help!!!!

Before you go any further with you database, change the name of some of your Fields like Bernie suggested. Number and Day are reserved words used for function in Access and could cause problems in the future if you continue to use them.

RAZMaddaz

'Nothing should be in the Criteria. However if the Number Field in your Table is a Data Type of Text, then you will get that error.'

Actually Raz, you can use the criteria and eliminate the if statement:

SELECT TestTable.Day, Avg(TestTable.Number) AS AvgOfNumber
FROM TestTable
WHERE (((TestTable.Number)<>0))
GROUP BY TestTable.Day;

Gee thanks River34, where would I be without you?

@Raz: Good catch on the reserved field names, I missed the 'Day"

@River: Very true, but would still recommend changing the data type, as if non numeric data is entered in the field (by mistake) you would still get the error!
(also need the 0 in quotes "0" - but I'm being pedantic! )

Cheers

Edit: Belay that last comment to River, still applies once changed to numeric

Probably sitting there in Maryland as bored as I am. I never seem to have enough work to keep me busy so I come here to 'try' to learn stuff. Gonna be a lot worse next month when this job ends and I'm unemployed. Now THAT'S boring!

Pere, you guys already had the other issues covered (text fields, etc.) I just tossed in the criteria line. I didn't open the database to see what was going on because I had read all the posts and I knew you were taking care of business! Certainly no shortage of answers on this forum.

Hi River

I think Raz had it all covered before me - I just got interested and jumped in , hope Raz didn't mind too much !