Re: Accessing multiple fields in report function

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
>
> --
> 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: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

  • Re: Accessing multiple fields in report function
    ... There is a better way to design surveys than that. ... > Dim Tbl As TableDef ... > For Each fld In Tbl.Fields ... >>> of all of the boolean fields that are true in each record, ...
    (microsoft.public.access.modulesdaovba)
  • Re: reading from txt file into table
    ... .Fields.Append fld ... Dim tdf As DAO.TableDef ... Dim lngInputFile As Long ... Then you can create a query into the text file, ...
    (microsoft.public.access.modulesdaovba)
  • anyone help me?storing the path...
    ... ByVal lpBuffer As String) As Long ... Dim Nullchr As Integer ... (ByVal FileName$, ByVal dwHandle&, ByVal cbBuff&, ByVal lpvData$) ... Dim fld As Field ...
    (microsoft.public.access.modulesdaovba)
  • Re: Nasty Normalization for Newbie
    ... Sandra Daigle [Microsoft Access MVP] ... Normalizing the ... >> Dim tdf As dao.TableDef ... >> For Each fld In tdf.Fields ...
    (microsoft.public.access.gettingstarted)
  • Re: Problem with find first
    ... Dim fld As Access.Field ... I receive the following error on the Dim Statement: ... value from the Row Source Query. ... DoCmd.GoToRecord,, acNewRec ...
    (microsoft.public.access.formscoding)
Loading