Count for totals

[Roger Bell]

I have inherited a data base that is not truly relational and already has
loads of data in it. There are 6 seperate fields for Children's names EG:
Namechild1, Namechild2 etc etc. There is then a text box called Number of
children in family which has to manually added (no big deal i guess) and the
number enter into this field. Is there any way I can write some code in this
text box that will automatically count the number of children for each
record, which will naturally change then as children are added or deleted.
Thanks for any help

 

[Larry Linson]

I have inherited a data base that is not truly relational
and already has loads of data in it. There are 6 sep-
erate fields for Children's names EG: Namechild1,
Namechild2 etc etc. There is then a text box called
Number of children in family which has to manually
added (no big deal i guess) and the number enter
into this field. Is there any way I can write some
code in this text box that will automatically count
the number of children for each record, which will
naturally change then as children are added or deleted.

You can write code in a standard module, if you wish, and call that from
within a Query, to count those fields.

You can write code, but not in the bound TextBox... calculated Controls
cannot be bound. You could have a Calculated Text Box on your Form, unbound,
and then copy the value to a bound Text Box.

Basically the code for a calculated Text Box will look something like this
(air) code:

= IIF(Not IsNull(txtChild1), 1, 0) +
IIF(Not IsNull(txtChild2), 1, 0) +
IIF(Not IsNull(txtChild3), 1, 0) +
. . .
IIF(Not IsNull(txtChildN),1,0)

You may have to fully-qualify the reference if it is not patently obvious to
Access that "txtChild1" refers to a Text Box.

In the Form's module you can use the Me!txtChildN notation, simplifying the
qualification of the reference.

BUT, it might be a lot better in the long-run if you would invest a little
more time, some Queries, and perhaps a little code, in extracting what
appears to be related child information into a related table.

Larry Linson
Microsoft Access MVP