access 2003 form to count fields with data

[cdownload]

I have 7 fields to key to on a form. Is there a way to count the number
fields that have for example, a value greater than zero?

[MGFoster]

cd*******@alltel.net wrote:

I have 7 fields to key to on a form. Is there a way to count the number
fields that have for example, a value greater than zero?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In a Command Button's click event, something like this:

dim ctl as control
dim i as integer

for each ctl in me.controls
If Not IsNull(ctl) Then
if isnumeric(ctl) then
if ctl.value > 0 then i = i + 1
endif
endif
next ctl

debug.print "Controls > 0: " & i

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/zlI4echKqOuFEgEQIDUgCeOZhdKb9rPjQym+MEYqXWjawIhD4A oMax
0lf2O0LvePL0BQUUBkrgoeJX
=aqUt
-----END PGP SIGNATURE-----

[cdownload]

I have three fields before the seven mentioned and one field after. Is
there a way to just look at the middle seven fields. Also, can this be
applied to a continuous form?

[MGFoster]

cd*******@alltel.net wrote:

I have three fields before the seven mentioned and one field after. Is
there a way to just look at the middle seven fields. Also, can this be
applied to a continuous form?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To run on selected controls you'd have to name them something like this
"Control_1" "Control_2", etc., and iterate thru them like this:

dim j as integer

for j = 1 to 7
set ctl = Me("Control_" & j)
If Not IsNull(ctl) Then
if isnumeric(ctl) then
if ctl.value > 0 then i = i + 1
endif
endif
next i

Instead of iterating thru the controls of each record on a continuous
form you shud just query the table(s) that supply the recordset to the
form. E.g.:

SELECT Count(IIf(IsNumeric(colA),1,NULL))
+ Count(IIf(IsNumeric(colB),1,NULL))
+ Count(IIf(IsNumeric(colC),1,NULL)) As NotNullColumns
FROM table_name
WHERE colA > 0 OR ColB > 0 OR ColC > 0

This is a kludgey query. The problem w/ this is really a DB design
issue -- the data types of each column should be strictly enforced.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/40B4echKqOuFEgEQJwzwCfZW/hqZfMecyQ7wnDAcAZMJjA5TAAnRJf
TZa/daV8X7cJb1NJd9zRisMU
=C/lY
-----END PGP SIGNATURE-----