Re: Accessing multiple fields in report function

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

  • Re: mailmerge and sql
    ... that is essentially a database application with a document ... the recordset to the Word Template and use it as if I got ... >> using an ADO recordset as a datasource (if it could be ... >Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Need Help..
    ... this part of the script is working. ... The part where we must add disconnected computers to the database i can't ... Dim strComputerName ' The Computer Name to be queried via WMI ... Dim iCursorType ' The Cursor Type for the Recordset ...
    (microsoft.public.windows.server.scripting)
  • Re: How to import certain fields from access database?
    ... I added the code but its not displaying any values in the textboxes. ... The first imports data from a database ... Dim myActiveRecord As Recordset ...
    (microsoft.public.word.mailmerge.fields)
  • Re: How to import certain fields from access database?
    ... Doug Robbins - Word MVP ... The first imports data from a database ... Dim myActiveRecord As Recordset ...
    (microsoft.public.word.mailmerge.fields)
  • Re: How to import certain fields from access database?
    ... Most probably the Combobox Exit event would be the best, ... Dim myActiveRecord As Recordset ...
    (microsoft.public.word.mailmerge.fields)