Counting in text fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have 5 text fields with a possible 2-digit code in each. What I need to do
is to count how many of these fields are filled in. For example,
field1=01
field2=03
field3=blank
field4=06
field5=blank

that gives me a total of 3. I don't need to specify any particular value,
just when they are filled in. I would want to update a new field with that
number.

Any help would be appreciated.

Thanks.
 
Hi,

I have 5 text fields with a possible 2-digit code in each. What I need to do
is to count how many of these fields are filled in. For example,
field1=01
field2=03
field3=blank
field4=06
field5=blank
Click to expand...

Well... this table design is flawed. If you have a one to many
relationship, the proper design is to use two tables in a one to many
relationship; "fields are expensive, records are cheap" as the old
saying goes! What if someday you need a SIXTH code? Do you redesign
your table, all your forms, all your reports, all your queries? Ouch!
that gives me a total of 3. I don't need to specify any particular value,
just when they are filled in. I would want to update a new field with that
number.
Click to expand...

You can use a calculated field:

CountOfFilled: IIF(IsNull([Field1]), 0, 1) + IIF(IsNull([Field2]), 0,
1) + IIF(IsNull([Field3]), 0, 1) + IIF(IsNull([Field4]), 0, 1) +
IIF(IsNull([Field5]), 0, 1)

to get this value. This result should NOT be stored in your table; if
you were to store it and then add a value to Field3, the stored value
would then be WRONG with no automatic way to detect that fact.

John W. Vinson[MVP]
 
Thank you very much...I was thinking about some kind if IIF function, but
could not formulate it; I am wondering why you used IsNull in that..

One thing, though. The stored value would constantly be updated through the
use of a command button running a set of queries.

John Vinson said:
Hi,

I have 5 text fields with a possible 2-digit code in each. What I need to do
is to count how many of these fields are filled in. For example,
field1=01
field2=03
field3=blank
field4=06
field5=blank
Click to expand...

Well... this table design is flawed. If you have a one to many
relationship, the proper design is to use two tables in a one to many
relationship; "fields are expensive, records are cheap" as the old
saying goes! What if someday you need a SIXTH code? Do you redesign
your table, all your forms, all your reports, all your queries? Ouch!
that gives me a total of 3. I don't need to specify any particular value,
just when they are filled in. I would want to update a new field with that
number.
Click to expand...

You can use a calculated field:

CountOfFilled: IIF(IsNull([Field1]), 0, 1) + IIF(IsNull([Field2]), 0,
1) + IIF(IsNull([Field3]), 0, 1) + IIF(IsNull([Field4]), 0, 1) +
IIF(IsNull([Field5]), 0, 1)

to get this value. This result should NOT be stored in your table; if
you were to store it and then add a value to Field3, the stored value
would then be WRONG with no automatic way to detect that fact.

John W. Vinson[MVP]
Click to expand...
 
Thank you very much...I was thinking about some kind if IIF function, but
could not formulate it; I am wondering why you used IsNull in that..
Click to expand...

Well... because that's what you ASKED for. You wanted to count the
non-NULL values. The IsNull function determines whether a value is
NULL or not.
One thing, though. The stored value would constantly be updated through the
use of a command button running a set of queries.
Click to expand...

.... bloating your database, eating your CPU time, and risking update
anomalies if the procedure doesn't run right every single time.

A properly normalized table structure avoids ALL these problems; you
can simply count records in the child table, using a Totals query,
DCount(), or a count function on a subform.

You're making your job *harder* by using this non-normalized design.

John W. Vinson[MVP]
 
The DB was inherited, with all kinds of complicated calculations. This was
the only I can think of...
 
The DB was inherited, with all kinds of complicated calculations. This was
the only I can think of...
Click to expand...

Sorry to hear that!

You'll have to decide, at some point, whether the (possibly large)
one-time effort of restructuring your data to a properly normalized
structure outweighs the (possibly even larger) ongoing effort of
struggling with a non-normalized structure.

John W. Vinson[MVP]
 
Back
Top