May 17 2012, 07:52 AM
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:
Any pointers would be really appreciated.
May 17 2012, 08:08 AM
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.
May 17 2012, 08:11 AM
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?
May 17 2012, 08:20 AM
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.
May 17 2012, 08:32 AM
Here is an example.
May 17 2012, 08:35 AM
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
May 17 2012, 08:40 AM
Try the following in a new Query:
SELECT TestTable.Day, Avg(IIf([Number]=0,Null,[Number])) AS TheNumberAvg
GROUP BY TestTable.Day;
Look at the example I uploaded too.
May 17 2012, 08:55 AM
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?
Just as a BTW, 'Number' is a reserved word in Access and you should try to avoid using it in Access field names.
May 17 2012, 08:59 AM
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.
May 17 2012, 09:03 AM
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?
May 17 2012, 09:05 AM
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.
May 17 2012, 09:08 AM
Is the data type of your field a number?
May 17 2012, 09:10 AM
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.
May 17 2012, 09:14 AM
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.
May 17 2012, 09:16 AM
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.
May 17 2012, 09:20 AM
Raz and I are always pleased to help
May 17 2012, 09:20 AM
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.
May 17 2012, 10:03 AM
'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
GROUP BY TestTable.Day;
May 17 2012, 10:09 AM
Gee thanks River34, where would I be without you?
May 17 2012, 10:17 AM
@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! )
Edit: Belay that last comment to River, still applies once changed to numeric
May 17 2012, 11:58 AM
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!
May 17 2012, 12:03 PM
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.
May 17 2012, 12:36 PM
I think Raz had it all covered before me
- I just got interested and jumped in
, hope Raz didn't mind too much
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here