Calculation question

[mama16]

Currently, I have a query that performs a Total division on a number for all the month. Like Sum([Tot_calls_by_Month])/([Months_Operational]) I want to create a query that'll ask the user for a specific Month ( I've already got that)but the number that I'd like to divide by is unknown to the user. It differs every month. In addition, some fields might be null, I'll like to exclude those. I want Access to pick the fields that contain data and divide those by x value
Any ideas?

 

[mama16]

Can I divide something by where is <>null?

 

[PHV]

Take a look at the Nz and IIf functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mama16]

Perhaps you misunderstood my question,
There are about 24 records for the month of September
The field that I want to do a division, contains numbers such as 54,568 etc... There are some records that do not have any data in it (null)
This is what I put on the criteria "<> null". It gives me everything except for the null values. Good!
What I'd like for Access to do is once it does this
Total:SUM([Average_call_per_month]) I want Access to pick up the values that are not null and divide by that number. I can't tell Access to divide by 24 cause not all the 24 records contain data.

Please, help on this.

Appreciate it

 

[PHV]

Take a look at the diff between Count(*) and Count([field name])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mama16]

I take that back, I need for all the states to show, if I exclude the nulls, certain states won't show.
How can I do this? Do it with the NZ function so it returns a value <> to null?

 

[JonFer]

PHV told you the solution - use Count([FieldName]) and you can remove the "Is Not Null" criteria so you get all of the records.

If you don't get good results, post your SQL.


John

http://www.interautomateconsulting.com