Accessing multiple fields in report function

[Dan Dorrough]

I need to be able to access multiple fields within a VBA function called for
each record of a report (MSAccess 2000). For example, suppose I wanted to
return a string containing the names of all of the boolean fields that are
true for each record, how could I do it? Or suppose I simply wanted a count
of all of the boolean fields that are true in each record, how could I do
that?

Any suggestions would be appreciated.

Dan

 

[Allen Browne]

Dan, you will need to pass all the fields from the record to your function.

If the function is called CountTrue, and you need to pass 3 fields, you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of fields, you
can declare its arguments as a ParamArray. There's an example of parsing and
operating on the array parameters here:
http://allenbrowne.com/func-09.html

 

[Guest]

Since there is no true boolean type in the database you could also tag the
names of the fields that you would like to have tested and instead of passing
each field pass the whole recordset.

public function countTrueFields(byref rsRecordset as Recordset, byval strTag
as String) as integer
dim intCount as Integer
dim fldField as Field
intCount = 0
For Each fldField in rsRecordset.Fields
if Left(fldField.Name, Len(strTag)) = strTag and fldField.Value then
intCount = intCount + 1
end if
next fldField
countTrueFields = intCount
end function

 

[Dan Dorrough]

Allen,

Thanks for the suggestion.

That is similar to the way that I started out to do it (passing each
variable as a parameter). However, it isn't really practical in this
instance. The table being reported on is a survey. Some questions have
50-100 independent choices ("choose all that apply") for answers which would
mean passing 50-100 parameters to the function. The maximum length of an
expression is 2500 bytes (or so) and a function with that many parameters
can exceed the maximum expression length.

I had thought that I could do something like:

Public Function CountSelected(strPrefix As String) As Integer
Dim DB As Database
Dim Tbl As TableDef
Dim fld As Field
CountSelected = 0

Set DB = CurrentDb
Set Tbl = DB.TableDefs("Survey")
For Each fld In Tbl.Fields
If Left(fld.Name, 4) = strPrefix Then
If (Not IsNull(fld)) And fld.Value Then
CountSelected = CountSelected + 1
End If
End If
Next fld

End Function

however, that dosen't work and I'm not sure why. Looks like maybe that
"table" that I get isn't the same as the table being processed. None of the
fields have values. Accessing any of them raises an exception. I don't do
enough MS Access programming to feel confident that this is really a
plausible solution.

I'll take a look at the ParamArray and see if that might suggest a solution.

Dan

Dan, you will need to pass all the fields from the record to your function.

If the function is called CountTrue, and you need to pass 3 fields, you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of fields, you
can declare its arguments as a ParamArray. There's an example of parsing and
operating on the array parameters here:
http://allenbrowne.com/func-09.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need to be able to access multiple fields within a VBA function called
for
each record of a report (MSAccess 2000). For example, suppose I wanted to
return a string containing the names of all of the boolean fields that are
true for each record, how could I do it? Or suppose I simply wanted a
count
of all of the boolean fields that are true in each record, how could I do
that?

 

[Dan Dorrough]

Dirk,

That looks like it ought to work. What might the rsRecordSet parameter look
like when called in a report (I'm pretty new to MSAccess/VBA programming)?

Thanks,

Dan

 

[Allen Browne]

You have a *field* for each possible answer?

There is a better way to design surveys than that. Duane Hookom has an
example here:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion.

That is similar to the way that I started out to do it (passing each
variable as a parameter). However, it isn't really practical in this
instance. The table being reported on is a survey. Some questions have
50-100 independent choices ("choose all that apply") for answers which
would
mean passing 50-100 parameters to the function. The maximum length of an
expression is 2500 bytes (or so) and a function with that many parameters
can exceed the maximum expression length.

I had thought that I could do something like:

Public Function CountSelected(strPrefix As String) As Integer
Dim DB As Database
Dim Tbl As TableDef
Dim fld As Field
CountSelected = 0

Set DB = CurrentDb
Set Tbl = DB.TableDefs("Survey")
For Each fld In Tbl.Fields
If Left(fld.Name, 4) = strPrefix Then
If (Not IsNull(fld)) And fld.Value Then
CountSelected = CountSelected + 1
End If
End If
Next fld

End Function

however, that dosen't work and I'm not sure why. Looks like maybe that
"table" that I get isn't the same as the table being processed. None of
the
fields have values. Accessing any of them raises an exception. I don't do
enough MS Access programming to feel confident that this is really a
plausible solution.

I'll take a look at the ParamArray and see if that might suggest a
solution.

Dan

Dan, you will need to pass all the fields from the record to your function.

If the function is called CountTrue, and you need to pass 3 fields, you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of fields, you
can declare its arguments as a ParamArray. There's an example of parsing and
operating on the array parameters here:
http://allenbrowne.com/func-09.html

I need to be able to access multiple fields within a VBA function called
for
each record of a report (MSAccess 2000). For example, suppose I wanted to
return a string containing the names of all of the boolean fields that are
true for each record, how could I do it? Or suppose I simply wanted a
count
of all of the boolean fields that are true in each record, how could I do
that?

 

[Dan Dorrough]

Yes. I agree that a *field* for each possible answer is a lousy design.
However, I don't have any control over that. The database already exists. My
job (in this case) is to analyze the data that is in the database and print
suitable reports.

Dan

You have a *field* for each possible answer?

There is a better way to design surveys than that. Duane Hookom has an
example here:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for the suggestion.

That is similar to the way that I started out to do it (passing each
variable as a parameter). However, it isn't really practical in this
instance. The table being reported on is a survey. Some questions have
50-100 independent choices ("choose all that apply") for answers which
would
mean passing 50-100 parameters to the function. The maximum length of an
expression is 2500 bytes (or so) and a function with that many parameters
can exceed the maximum expression length.

I had thought that I could do something like:

Public Function CountSelected(strPrefix As String) As Integer
Dim DB As Database
Dim Tbl As TableDef
Dim fld As Field
CountSelected = 0

Set DB = CurrentDb
Set Tbl = DB.TableDefs("Survey")
For Each fld In Tbl.Fields
If Left(fld.Name, 4) = strPrefix Then
If (Not IsNull(fld)) And fld.Value Then
CountSelected = CountSelected + 1
End If
End If
Next fld

End Function

however, that dosen't work and I'm not sure why. Looks like maybe that
"table" that I get isn't the same as the table being processed. None of
the
fields have values. Accessing any of them raises an exception. I don't do
enough MS Access programming to feel confident that this is really a
plausible solution.

I'll take a look at the ParamArray and see if that might suggest a
solution.

Dan

Dan, you will need to pass all the fields from the record to your function.

If the function is called CountTrue, and you need to pass 3 fields, you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of fields, you
can declare its arguments as a ParamArray. There's an example of

parsing
and

operating on the array parameters here:
http://allenbrowne.com/func-09.html

I need to be able to access multiple fields within a VBA function called
for
each record of a report (MSAccess 2000). For example, suppose I

wanted
to

return a string containing the names of all of the boolean fields

that
are

true for each record, how could I do it? Or suppose I simply wanted a
count
of all of the boolean fields that are true in each record, how could

I
do

that?

 

[Allen Browne]

So you do want all these fields, but you don't want to pass all the fields
into the function?

The only other option would seem to be to open a recordset, so the function
itself can loop through the Fields of the Recordset to get the count of
items.

Opening a recordset for each item will be a very inefficient approach
though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes. I agree that a *field* for each possible answer is a lousy design.
However, I don't have any control over that. The database already exists.
My
job (in this case) is to analyze the data that is in the database and
print
suitable reports.

Dan

You have a *field* for each possible answer?

There is a better way to design surveys than that. Duane Hookom has an
example here:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

Allen,

Thanks for the suggestion.

That is similar to the way that I started out to do it (passing each
variable as a parameter). However, it isn't really practical in this
instance. The table being reported on is a survey. Some questions have
50-100 independent choices ("choose all that apply") for answers which
would
mean passing 50-100 parameters to the function. The maximum length of
an
expression is 2500 bytes (or so) and a function with that many parameters
can exceed the maximum expression length.

I had thought that I could do something like:

Public Function CountSelected(strPrefix As String) As Integer
Dim DB As Database
Dim Tbl As TableDef
Dim fld As Field
CountSelected = 0

Set DB = CurrentDb
Set Tbl = DB.TableDefs("Survey")
For Each fld In Tbl.Fields
If Left(fld.Name, 4) = strPrefix Then
If (Not IsNull(fld)) And fld.Value Then
CountSelected = CountSelected + 1
End If
End If
Next fld

End Function

however, that dosen't work and I'm not sure why. Looks like maybe that
"table" that I get isn't the same as the table being processed. None of
the
fields have values. Accessing any of them raises an exception. I don't do
enough MS Access programming to feel confident that this is really a
plausible solution.

I'll take a look at the ParamArray and see if that might suggest a
solution.

Dan

Dan, you will need to pass all the fields from the record to your
function.

If the function is called CountTrue, and you need to pass 3 fields,
you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of fields, you
can declare its arguments as a ParamArray. There's an example of parsing
and
operating on the array parameters here:
http://allenbrowne.com/func-09.html

I need to be able to access multiple fields within a VBA function called
for
each record of a report (MSAccess 2000). For example, suppose I wanted
to
return a string containing the names of all of the boolean fields that
are
true for each record, how could I do it? Or suppose I simply wanted
a
count
of all of the boolean fields that are true in each record, how could I
do
that?

 

[Dan Dorrough]

So you do want all these fields, but you don't want to pass all the fields
into the function?

That is correct. I don't want to pass in all of fields individually.

The only other option would seem to be to open a recordset, so the function
itself can loop through the Fields of the Recordset to get the count of
items.

In Delphi, I would simply pass in (a reference to) the table and then
process the fields in the current record of the table. This would be a very
low overhead operation. Is there anything equivalent that can be done in
Access/VBA?

Dan

Opening a recordset for each item will be a very inefficient approach
though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes. I agree that a *field* for each possible answer is a lousy design.
However, I don't have any control over that. The database already exists.
My
job (in this case) is to analyze the data that is in the database and
print
suitable reports.

Dan

You have a *field* for each possible answer?

There is a better way to design surveys than that. Duane Hookom has an
example here:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

Allen,

Thanks for the suggestion.

That is similar to the way that I started out to do it (passing each
variable as a parameter). However, it isn't really practical in this
instance. The table being reported on is a survey. Some questions have
50-100 independent choices ("choose all that apply") for answers which
would
mean passing 50-100 parameters to the function. The maximum length of
an
expression is 2500 bytes (or so) and a function with that many parameters
can exceed the maximum expression length.

I had thought that I could do something like:

Public Function CountSelected(strPrefix As String) As Integer
Dim DB As Database
Dim Tbl As TableDef
Dim fld As Field
CountSelected = 0

Set DB = CurrentDb
Set Tbl = DB.TableDefs("Survey")
For Each fld In Tbl.Fields
If Left(fld.Name, 4) = strPrefix Then
If (Not IsNull(fld)) And fld.Value Then
CountSelected = CountSelected + 1
End If
End If
Next fld

End Function

however, that dosen't work and I'm not sure why. Looks like maybe that
"table" that I get isn't the same as the table being processed. None of
the
fields have values. Accessing any of them raises an exception. I

don't
do

enough MS Access programming to feel confident that this is really a
plausible solution.

I'll take a look at the ParamArray and see if that might suggest a
solution.

Dan

Dan, you will need to pass all the fields from the record to your
function.

If the function is called CountTrue, and you need to pass 3 fields,
you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of

fields,
you

can declare its arguments as a ParamArray. There's an example of parsing
and
operating on the array parameters here:
http://allenbrowne.com/func-09.html

I need to be able to access multiple fields within a VBA function called
for
each record of a report (MSAccess 2000). For example, suppose I wanted
to
return a string containing the names of all of the boolean fields that
are
true for each record, how could I do it? Or suppose I simply wanted
a
count
of all of the boolean fields that are true in each record, how

could
I

do
that?

 

[Allen Browne]

You can pass the name of the table, and the primary key value, and then
OpenRecordset() to get the data in the particular record.

The performance issue is with the connections you need to open and close the
recordset constantly. There may be a way to avoid that. For example, if this
were happening in a report, you could declare a module level recordset
variable, initialize it in Report_Open, and close it in Report_Close. Then
at any record, you could FindFirst in the already-open recordset, and avoid
the bottleneck.

That general approach (walking an open recordset rather than constantly
opening and closing it) can also be achieved through a class module, or even
a public Recordset variable if you have a way to close it when you are done.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

So you do want all these fields, but you don't want to pass all the
fields
into the function?

That is correct. I don't want to pass in all of fields individually.

The only other option would seem to be to open a recordset, so the function
itself can loop through the Fields of the Recordset to get the count of
items.

In Delphi, I would simply pass in (a reference to) the table and then
process the fields in the current record of the table. This would be a
very
low overhead operation. Is there anything equivalent that can be done in
Access/VBA?

Dan

Opening a recordset for each item will be a very inefficient approach
though.

Yes. I agree that a *field* for each possible answer is a lousy design.
However, I don't have any control over that. The database already exists.
My
job (in this case) is to analyze the data that is in the database and
print
suitable reports.

Dan

You have a *field* for each possible answer?

There is a better way to design surveys than that. Duane Hookom has an
example here:

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

Allen,

Thanks for the suggestion.

That is similar to the way that I started out to do it (passing each
variable as a parameter). However, it isn't really practical in this
instance. The table being reported on is a survey. Some questions have
50-100 independent choices ("choose all that apply") for answers which
would
mean passing 50-100 parameters to the function. The maximum length
of
an
expression is 2500 bytes (or so) and a function with that many
parameters
can exceed the maximum expression length.

I had thought that I could do something like:

Public Function CountSelected(strPrefix As String) As Integer
Dim DB As Database
Dim Tbl As TableDef
Dim fld As Field
CountSelected = 0

Set DB = CurrentDb
Set Tbl = DB.TableDefs("Survey")
For Each fld In Tbl.Fields
If Left(fld.Name, 4) = strPrefix Then
If (Not IsNull(fld)) And fld.Value Then
CountSelected = CountSelected + 1
End If
End If
Next fld

End Function

however, that dosen't work and I'm not sure why. Looks like maybe that
"table" that I get isn't the same as the table being processed. None of
the
fields have values. Accessing any of them raises an exception. I don't
do
enough MS Access programming to feel confident that this is really a
plausible solution.

I'll take a look at the ParamArray and see if that might suggest a
solution.

Dan

Dan, you will need to pass all the fields from the record to your
function.

If the function is called CountTrue, and you need to pass 3 fields,
you
would set the Control Source of your text box to:
=CountTrue([Field1], [Field2], [Field3])

If you need the function to accept an indeterminate number of fields,
you
can declare its arguments as a ParamArray. There's an example of
parsing
and
operating on the array parameters here:
http://allenbrowne.com/func-09.html

I need to be able to access multiple fields within a VBA function
called
for
each record of a report (MSAccess 2000). For example, suppose I
wanted
to
return a string containing the names of all of the boolean fields
that
are
true for each record, how could I do it? Or suppose I simply wanted
a
count
of all of the boolean fields that are true in each record, how could
I
do
that?