Code to determine field names to send when calling different module?

[fredalina]

This is kind of a weird one.

i know that to call a module through a query (say it's a module to find the minimum value amongst a number of fields), you would put:

ModuleName([Field1],[Field2],[Field3],...,[Fieldn])

and the code would be in the form of an array:

Function ModuleName(ParamArray FieldArray() As Variant)

But is there code that could generate the field names to go:

ModuleName([B][I]HERE[/I][/B])

?

Suppose i need the minimum of over 100 fields and don't want to type out 100+ [Field1] in the parenthesis.

Thanks!

 

[pbaldy]

It sounds like you may have a normalization issue, so you may want to address your table layout. It would likely make this type of thing much easier. What do the fields represent? That said, you could use some sort of loop to build the list. It would depend on the specifics.

 

[fredalina]

i don't think there's an existing problem unless i misunderstand you. i don't know what you mean by a normalization issue.

Everything works fine, but what i had to do yesterday was copy a row from Access into Excel, copy the header row with field names, and paste into another worksheet transposed. Then i used some basic code to add the brackets to the left and right of the field names and a comma at the end. Then copied and pasted transposed again, then copied THAT and pasted it into Notepad .txt format, only so i could paste it into the () of calling the module. It was either that or type out 137 field names.

i'd like this code to be usable in other applications besides this one query, and i think it would be handy to be able to have code that generates the field names for it.

An example would be if i had box attributes of Height, Width, Length, and Weight. These would be the 4 fields in the query i want the minimum of. i would call my module in a new field like this:

Minimum: ModuleName([Height], [Width], [Length], [Weight])

The module would open in the background and determine the minimum of those 4 fields. All that works, no problem. But suppose i didn't want to type those 4 field names in as above, and wanted something to generate a string with all of a table/query's field names listed in brackets like above. i can do the formatting (brackets, etc), i just don't know how to cycle through the field names like would be needed. Obviously with 4 fields it's not an issue, but if it's a really sizable query (like a crosstab like yesterday's) with dozens or even hundreds of fields, it's a problem to type out all of the field names when calling the Module.

Is that making more sense?

 

[SOS]

i don't know what you mean by a normalization issue.

Read this: http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

Everything works fine,

Or at least it appears to do so, but perhaps not efficiently if not normalized.

...or type out 137 field names.

More than 30-40 fields in a table usually means that the database either 1. has not been properly normalized.
2. is a data warehouse view and not for production but for reporting.

In RELATIONAL DATABASES - think THIN and LONG tables not Short and WIDE tables (that is spreadsheet thinking).

 

[fredalina]

It is a data warehouse reporting database. i don't have control of the database tables or i certainly wouldn't have put them together as is! The vast majority of what i do is ad-hoc queries run from Access as a back-door to the data warehouse tables.

That's an interesting link and would be great if i were designing the database instead of just reporting from it. Can anyone help with the code i'm looking for?

 

[pbaldy]

If the fields have predictable names, a For/Next loop:

For i = 1 To 137
  Debug.Print "Field" & i
Next i

If not, you'll need to open a recordset on the source and step through the fields:

    intColCount = rst.Fields.Count
    For i = 1 To intColCount
        Debug.Print rst.Fields(i - 1).name
    Next i

 

[fredalina]

intColCount = rst.Fields.Count
For i = 1 To intColCount
Debug.Print rst.Fields(i - 1).name
Next i

i think this is just what i was looking for. Thanks!

 

[pbaldy]

No problemo. In future new threads, I would mention the data warehouse bit (if relevant). Most of us see "100 fields" and it's a big normalization red flag. You'll save yourself the grief of explaining it again and get a better answer to start with if you mention it up front. We all understand getting data from a source we can't change!