Passing MANY field names to public function as array from Query

[fredalina]

i have a query that i need to send 38 different field names to a module function. The module is set up where the passed fields go into an array and are used in the function. (The function determines the name of the field that contains a value equal to that of the first passed). But i get an error "The expression you entered is too complex."

How can i pass 38 field names to the function? Or, if i can't, is there a way to store the information in the module so i can maybe combine two functions?

Thanks!

 

[RuralGuy]

Why not have the module just use the query?

 

[fredalina]

i guess i don't know what you mean?

 

[RuralGuy]

You can have the module run the query. Maybe I just don't know enough about what you are doing to offer suggestions.

 

[fredalina]

Hmmm... okay, some background.

The query is enormous, not a lot of rows but many, many columns. Basically we have 9 applicable buildings around the country and each building has a value. i want to know first of all if any value at a building exceeds a target number. If it does, great, stop there (and i've completed this portion of the query). If not, i want to know if the sum of the values at any two buildings exceeds the target, and if so, i want to find the closest pair of buildings that exceed the target (combined) geographically.

i've established each pairing into 37 different combinations i'm calling Duo1, Duo2, Duo3, etc. Now i need to cycle through these to determine if any Duo is >= the target, and if so, identify which of the Duos is first (the 1, 2, 3, etc pairings are geographically arranged already). Rather than stack 37 IIf statements, i'd prefer to call a function that sets up the duos as an array and does the comparison there, and then returns the Duo. i have a Select Case that would work already, or i could make it a 2-dimensional array, one with the field name and one with the field value. Either is fine, but Access won't let me call the function passing that many fields.

Perhaps a lookup could work? Not sure how to specify which row in the query to look up though, since that's irrelevant as far as the function at large is concerned (it's going to run through the function once for every row).

 

[RuralGuy]

I'm still not sure why the function could not run the query.

 

[fredalina]

i appreciate your help, but i still don't know what you mean.

 

[boblarson]

Sounds like a normalization nightmare. The data is not normalized and therefore it is a nightmare to try to do anything with it.

 

[RuralGuy]

Something like:
Dim MyRs As DAO.Recordset
Set MyRs = CurrentDb().OpenRecordset("[YourQuery]", dbOpenDynaset)
...in your function would open your query as a RecordSet.

 

[fredalina]

True. The values at the different buildings were calculated from data from 4 different crosstab queries. i would have no idea how to go back in and normalize it from here.

 

[fredalina]

Something like:
Dim MyRs As DAO.Recordset
Set MyRs = CurrentDb().OpenRecordset("[YourQuery]", dbOpenDynaset)
...in your function would open your query as a RecordSet.

How does this pass the value of the duo back to the query? The end result of all this in the function will be one field that is part of the enormous query. i'm probably missing something...

 

[RuralGuy]

Your function would simply run several queries if that's what it takes. Since the data has been flattened, you will struggle no matter how you choose to solve the problem.

 

[fredalina]

Thanks for the help all. i was able to manage it by running a DLookup from the Module. Very slow and ugly, but at least it accomplishes the mission.

 

[RuralGuy]

Excellent! Thanks for the update.

 

[Galaxiom]

i have a query that i need to send 38 different field names to a module function.
The query is enormous, not a lot of rows but many, many columns.

These are two of the most reliable indicators that the data structure is inappropriate. Anything you do will be a kludge until you fix this structure.