By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

access 2003 form to count fields with data

P: n/a
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?

Feb 22 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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-----
Feb 22 '06 #2

P: n/a
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?

Feb 23 '06 #3

P: n/a
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-----
Feb 23 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.