Re: Accessing multiple fields in report function

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
news:eveyYb4GGHA.1452@xxxxxxxxxxxxxxxxxxxxxxx
> 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.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dan Dorrough" <ddorrough@xxxxxxxxxxx> wrote in message
> news:wc9zf.80302$XJ5.76374@xxxxxxxxxxxxxxxxxxxxxxx
> > 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
> >
> > "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
> > news:uz7YxO4GGHA.2212@xxxxxxxxxxxxxxxxxxxxxxx
> >> 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%20Your%20Survey%202000'
> >>
> >> "Dan Dorrough" <ddorrough@xxxxxxxxxxx> wrote in message
> >> news:ek8zf.79713$XJ5.2813@xxxxxxxxxxxxxxxxxxxxxxx
> >> > 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
> >> >
> >> > "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> wrote in message
> >> > news:e4xRuAxGGHA.2696@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> 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
> >> >>
> >> >> "Dan Dorrough" <ddorrough@xxxxxxxxxxx> wrote in message
> >> >> news:SrXyf.100730$ME5.35671@xxxxxxxxxxxxxxxxxxxxxxx
> >> >> >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?
>
>


.

Relevant Pages