473,322 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

calculated text box

I have a field [result] and want to have a calculated field in
a report that counts it. It had control source set to "=count
([result]). As [result] can be also Null, I would like
it to count only values distinct from Null. I tried by
"Control Source=Count([result] Is Not Null), but it doesn't work, it still
counts all rows, even with Null.
How to set Control Source in the calcualted text box, in order to count only
values that are not null?

Thanks,

Zlatko

Jan 20 '06 #1
2 2120
I tested to make sure I was remembering correctly since you're trying to
exclude Nulls, but my testing shows that Nulls aren't counted by Count().
The exception to this is Count(*) will return a count of records in the
recordset, but if you specify a field to count, then Nulls in that field are
ignored.

--
Wayne Morgan
MS Access MVP
"Zlatko Matiæ" <zl***********@sb.t-com.hr> wrote in message
news:dq**********@ss405.t-com.hr...
I have a field [result] and want to have a calculated field in
a report that counts it. It had control source set to "=count
([result]). As [result] can be also Null, I would like
it to count only values distinct from Null. I tried by
"Control Source=Count([result] Is Not Null), but it doesn't work, it still
counts all rows, even with Null.
How to set Control Source in the calcualted text box, in order to count
only
values that are not null?

Jan 20 '06 #2
Well, it surely counts both null and not null values, in my case. Maybe
because underlying query is pass-through query?
Anyway, I think I found a solution: "=Count(IIf(IsNull([result]);Null;1))".

Zlatko

"Wayne Morgan" <co***************************@hotmail.com> je napisao u
poruci interesnoj grupi:Vp*****************@newssvr27.news.prodigy.n et...
I tested to make sure I was remembering correctly since you're trying to
exclude Nulls, but my testing shows that Nulls aren't counted by Count().
The exception to this is Count(*) will return a count of records in the
recordset, but if you specify a field to count, then Nulls in that field
are ignored.

--
Wayne Morgan
MS Access MVP
"Zlatko Matiæ" <zl***********@sb.t-com.hr> wrote in message
news:dq**********@ss405.t-com.hr...
I have a field [result] and want to have a calculated field in
a report that counts it. It had control source set to "=count
([result]). As [result] can be also Null, I would like
it to count only values distinct from Null. I tried by
"Control Source=Count([result] Is Not Null), but it doesn't work, it
still
counts all rows, even with Null.
How to set Control Source in the calcualted text box, in order to count
only
values that are not null?


Jan 21 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jack | last post by:
Hi, I have a asp input form of a financial report. Here most of the fields are text boxes where the user need to input the values corresponding to an item. However, there are few items which are...
14
by: Allen Browne | last post by:
Subform is based on a single-table query that contains a calculated field: Amount: Round(CCur(Nz(*,0)),2) Continuous subform displays this field in a text box named Amount. As user enters new...
1
by: Miguelito Bain | last post by:
hi everybody. i'm trying to find out what you can and can't do with/in calculated fields. any help, pointers, and/or advice would be greatly appreciated. i'm a newbie, but i want to learn, and...
2
by: Mark Kola | last post by:
I have ran into an interesting situation. I have a calculated field based on a combobox column that somtimes is not requerying when I change the combo box value. Combobox cboCustomer 2...
2
by: markm75c | last post by:
Does anyone know of a way to sort a column which isnt databound or an actual field in the database, but is derived from a method? IE. I have a grid showing stats for softball, with a calculated...
2
by: Olveres | last post by:
Hi, I have managed to work out how to add new lines into a calculated text box. However in this text box some of the outcome fields are empty however when previewing the report it includes the...
15
by: Jimmy Stewart | last post by:
I want to use a calculated function for the caption on my tab controls. I used the following code: Me.Page28.Caption = "Expenses & "]" This should display the following: " Expenses " where...
2
by: EdDale | last post by:
Hi and thanks in advance, I am using DMin and DMax to calculate date fields in a form. I need to use these calculated dates in another calculated field. example: =DMax("","B&MPayPeriod"," >=...
9
by: Haas C | last post by:
Hi all! Is there anyway I can override a value in a calculated field on a form? For example: I have a form which displays the following fields based on a query: Premium Due field has the...
2
by: mkbrady | last post by:
I have a query that includes calculated fields that generate numeric results. I have wrapped conversion functions CLng() and CDdl() around the calculated fields to ensure the data types are...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.