Expression Too Complex - Passing 29 Parameters, is that too much (1 Viewer)

[bg18461]

Expression Too Complex - Passing 29 Parameters - (See Post#15 for Database File)

I trying to pass the parameters below into a VBA Case statement, but I am getting a too complex error, I have spent all day sorting this out and this is the last step, any ideas how I can get access to take this expression.

1ST QTR ENERGY MONTH 13-24: FirstQtrEnerMth13to24([MNTH1],[AKW11],[AKW12],
[AKW13],[AKW14],[AKW15],[AKW16],[AKW17],[AKW18],[AKW19],[AKW20],[AKW21],[AKW22],
[AKW23],[AKW24],[NAKW11],[NAKW12],[NAKW13],[NAKW14],[NAKW15],[NAKW16],[NAKW17],
[NAKW18],[NAKW19],[NAKW20],[NAKW21],[NAKW22],[NAKW23],[NAKW24])

 

[Rokhi]

Pass them in an array.
Browse thru the code in this Access Howto MDB courtesy of Ken Getz & Paul Litwin. It should give you some ideas

 

[bg18461]

I cant seem to open that file i am running access 07 and its giving me issues.

What I tried to do was create another query that summed up the akw and nakw values, (this was part of the case function in vba). The new query is called qAkwSum, and the fields created are newakw1,newakw2,newakw3....)

However I do not know how to pass these query results into a parameter for another query when those values are going to be used in a VBA function, any ideas. I thought the syntax would be
1ST QTR ENERGY MONTH 13-24: FirstQtrEnerMth13to24([MNTH1],[qAkwSum].[newakw1],[qAkwSum].[newakw2],etc...
However I am prompted to enter values for qakwsum1,2,etc... instead of the query (qAkwSum) running and computing those values, any ideas.

 

[Rokhi]

Here are some code samples showing how to pass the parameters in an array.

Function SumThemUp(varValues As Variant) As Variant
    ' Find the sum of the values passed in.
 
    Dim varItem As Variant
    Dim varSum As Variant
 
    varSum = 0
    If IsArray(varValues) Then
        For Each varItem In varValues
            varSum = varSum + varItem
        Next varItem
    Else
        varSum = varValues
    End If
    SumThemUp = varSum
End Function
Sub TestSum(Optional varMax As Variant)
 
    Dim intI As Integer
    Dim varItem As Variant
 
    If IsMissing(varMax) Then
        varMax = 10
    Else
        ' Check the boundaries.
        If varMax = 0 Then Exit Sub
        If varMax > 20 Then varMax = 20
        If varMax < 1 Then varMax = 1
    End If
    ' Since your dimensioning to a variable size, you
    ' must use ReDim.
    ReDim aintValues(1 To varMax) As Integer
    ' Can't SET values with For...Each if
    ' using variant array.
    For intI = 1 To varMax
        aintValues(intI) = Int(9 * Rnd + 1)
    Next intI
    Debug.Print "The values are: ";
    For Each varItem In aintValues()
        Debug.Print varItem;
    Next varItem
    Debug.Print
    Debug.Print "The sum is: " & SumThemUp(aintValues())
End Sub
Sub TestUCase(intMax As Integer)
    Dim intI As Integer
    Dim varItem As Variant
    ' Check the boundaries.
    If intMax = 0 Then Exit Sub
    If intMax > 26 Then intMax = 26
    If intMax < 1 Then intMax = 1
    ReDim astrTest(1 To intMax)
    ' Fill the array with lowercase strings.
    Debug.Print "Lower Case:"
    For intI = 1 To intMax
        astrTest(intI) = String(5, 96 + intI)
        Debug.Print astrTest(intI)
    Next intI
    ' Call the function to uppercase the strings.
    UCaseArray astrTest()
    Debug.Print
    Debug.Print "Upper Case:"
    ' Now print out the changed values.
    For Each varItem In astrTest
        Debug.Print varItem
    Next varItem
End Sub
Sub UCaseArray(varValues As Variant)
    ' Convert the entire passed in array to upper case.
    Dim varItem As Variant
    Dim intI As Integer
 
    If IsArray(varValues) Then
        For intI = LBound(varValues) To UBound(varValues)
            varValues(intI) = UCase(varValues(intI))
        Next intI
    Else
        varValues = UCase(varValues)
    End If
End Sub
Function MinValue(ParamArray varValues() As Variant) As Variant
    ' Return the minimum value from a list of values.
    Dim varItem As Variant
    Dim varMin As Variant
    If UBound(varValues) = -1 Then
        MinValue = Null
    Else
        varMin = varValues(LBound(varValues))
        For Each varItem In varValues
            If varItem < varMin Then
                varMin = varItem
            End If
        Next varItem
        MinValue = varMin
    End If
End Function

 

[bg18461]

I think I could use those codes, but I am not summing all the AKW and NAKW values. I am only summing those that have the same ending number together,
NEWAKW11=AKW11+NAKW11, NEWAKW12=AKW12+NAKW12, etc...

(ie AKW11+NAKW11, AKW12+NAKW12, etc...)

However this is not all my vba code do, here is one of my functions as a whole, can anyone help me marry these two ideas together.

Function ThirdQtrEnerMth13to24(dThisMonth As Date, AKW5 As Long, AKW6 As Long, AKW7 As Long, AKW8 As Long, _
AKW9 As Long, AKW10 As Long, AKW11 As Long, AKW12 As Long, AKW13 As Long, AKW14 As Long, AKW15 As Long, _
AKW16 As Long, AKW17 As Long, AKW18 As Long, NAKW5 As Long, NAKW6 As Long, NAKW7 As Long, NAKW8 As Long, _
NAKW9 As Long, NAKW10 As Long, NAKW11 As Long, NAKW12 As Long, NAKW13 As Long, NAKW14 As Long, NAKW15 As Long, _
NAKW16 As Long, NAKW17 As Long, NAKW18 As Long) As Long

Dim sDate As String

    sDate = Month([dThisMonth]) & "/1/" & Year([dThisMonth])
    
    Select Case sDate

        Case "1/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW7 + AKW6 + AKW5 + NAKW7 + NAKW6 + NAKW5
        Case "2/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW8 + AKW7 + AKW6 + NAKW8 + NAKW7 + NAKW6
        Case "3/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW9 + AKW8 + AKW7 + NAKW9 + NAKW8 + NAKW7
        Case "4/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW10 + AKW9 + AKW8 + NAKW10 + NAKW9 + NAKW8
        Case "5/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW11 + AKW10 + AKW9 + NAKW11 + NAKW10 + NAKW9
        Case "6/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW12 + AKW11 + AKW10 + NAKW12 + NAKW11 + NAKW10
        Case "7/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW13 + AKW12 + AKW11 + NAKW13 + NAKW12 + NAKW11
        Case "8/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW14 + AKW13 + AKW12 + NAKW14 + NAKW13 + NAKW12
        Case "9/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW15 + AKW14 + AKW13 + NAKW15 + NAKW14 + NAKW13
        Case "10/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW16 + AKW15 + AKW14 + NAKW16 + NAKW15 + NAKW14
        Case "11/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW17 + AKW16 + AKW15 + NAKW17 + NAKW16 + NAKW15
        Case "12/1/" & Year(dThisMonth)
            ThirdQtrEnerMth13to24 = AKW18 + AKW17 + AKW16 + NAKW18 + NAKW17 + NAKW16
    
    End Select
   
End Function

If I can sum up the AKW with NAKW values before I pass it into my function, it would reduce the amount of fields I need to pass in.

Just so I'm clear, I will use this part of the above function as an example:

Case "1/1/" & Year(dThisMonth)
ThirdQtrEnerMth13to24 = AKW7 + AKW6 + AKW5 + NAKW7 + NAKW6 + NAKW5

If I sum up the akw and nakw ahead of time like this:

NEWAKW7=AKW7+NAKW7
NEWAKW6=AKW6+NAKW6
NEWAKW5=AKW5+NAKW5

Then I would only need to pass NEWAKW5,NEWAKW6,NEWAKW7 in my query into the above function making the statement look like this:

Case "1/1/" & Year(dThisMonth)
ThirdQtrEnerMth13to24 = NEWAKW7 + NEWAKW6 + NEWAKW5

Please help me with this...

 

[Rokhi]

I think you may need to consider a different approach. This looks overly complicated

Can you tell me what your source data looks like and what the broad goal is?

 

[bg18461]

Rokhi, please check your pm.

 

[bg18461]

Can anyone else chime in, i can send you a copy of my de-sensitzed database.

 

[bg18461]

104 views and only one person comments, please forum, help a fellow accessite out

 

[WayneRyan]

bg,

Patience ...

Whenever you're doing something this much out of the ordinary use a VBA function.

Just pass it the PK of each record and let it retrieve the data and do whatever it does.

Just remember to put the function as a PUBLIC function in a module that doesn't have the same name.

hth,
Wayne

 

[bg18461]

Wayne, can you explain a little more, pass the PK, not sure, can I send you the database and vba modules I already have and you give me a little more guidance.

 

[WayneRyan]

bg,

The PK is just the unique identifier for each row in your table.

It can/should be an AutoNumber, but in practice it can even consist of multiple fields.
The function needs to know this.

In your query:

SomeNewField: fnYourFunction([TheAutoNumberField])

Or in the second case:

SomeNewField: fnYourFunction([CompanyDivision], [CompanySection])

Including the argument(s) in the function call ensures that it will be called for each
row of the query.

The function is declared in a module:

Public Function FirstQtrEnerMth13to24(ThePK As Long) As Currency
'
' In your code, use the record's identifier to open a recordset, Use DSum or DLookup,
' or whatever to do your calculations
'
End Function

The good aspect of this is that using the PK, the function is WAY less complicated,
and easier to maintain.

The bad aspect of this is that you will be querying the data twice; 1st to get each
row's PK, and 2nd to get the various values.

If it's a small dataset, that's OK.

btw, Those look like repeating fields and it's quite possible that moving them
to a child table would alleviate the problem.

hth,
Wayne

 

[bg18461]

hey wayne, sent you pm

 

[DALeffler]

hey wayne, sent you pm

Please attach a sample demo db for the rest of us, if you can...

I was able to pare your posted function down to this:

Function ThirdQtrEnerMth13to24(dThisMonth As Date, ParamArray lngKW() As Variant) As Long
Dim I As Integer
'lngKW() is zero based...
I = Month(dThisMonth) - 1
'function should only receive 16 array elements (6 months data) at a time ...
If I > 5 Then I = I - 6

ThirdQtrEnerMth13to24 = lngKW(I) + lngKW(I + 1) + lngKW(I + 2) + _
                            lngKW(I + 8) + lngKW(I + 9) + lngKW(I + 10)
End Function

And get valid results calling the function from a query like so (following the structure I saw using the posted function code, not from the function call in the OP):

KW_Totals: IIf(Month([mnth1])<7,ThirdQtrEnerMth13to24([mnth1],[AKW5],[AKW6],[AKW7],
[AKW8],[AKW9],[AKW10],[AKW11],[AKW12],[NAKW5],[NAKW6],[NAKW7],[NAKW8],[NAKW9],
[NAKW10],[NAKW11],[NAKW12]),
ThirdQtrEnerMth13to24([mnth1],[AKW11],[AKW12],[AKW13],[AKW14],[AKW15],[AKW16],
[AKW17],[AKW18],[NAKW11],[NAKW12],[NAKW13],[NAKW14],[NAKW15],[NAKW16],
[NAKW17],[NAKW18]))

Now the function result and validity is entirely dependent upon which data is passed to the function and is not recommended.

What you really need to do is normalize the data to at least third normal form. If you did that, your query function calls and calculations would become much more simplified & straight forward (but I did have a lot of fun tinkering around with this!)

Good Luck (not that you'll need a bunch: Normalize the data & you'll be there very soon...)

hth,

 

[bg18461]

Thanks team for your help thus far. Just so everyone know the values in the table are repeating values but thats how its formatted in the table thats updated once a month. Is it possible to write a vba function that will split this out into child tables when you update the master table each month? I am not sure if thats the best way to go, but my ears are open to any suggestions.

I have created a de-sensitized database, with a small sampling of the data in the table, its located at http://www.sendspace.com/file/uw1vwk as a zip file. The download link is near the bottom of the page. I would have attached directly in this post but the zip file is 975kb, a little too big for this forum. Please take a look at qCustVitalInfo query, this contains the info where I would like to put this information. The modules modMonths1to12 and modMonths13to24 contain the code I created to properly create the data. By the way, the real table is pretty big, like 54,000 entries.

Below are the query expressions that I was trying to enter in qCustVitalInfo, but I kept getting the TOO COMPLEX error.

1ST QTR REVENUE MONTH 13-24: FirstQtrRevMth13to24([MNTH1],[DEBIT11],[DEBIT12],[DEBIT13],[DEBIT14],[DEBIT15],[DEBIT16],[DEBIT17],[DEBIT18],[DEBIT19],[DEBIT20],[DEBIT21],[DEBIT22],[DEBIT23],[DEBIT24])
2ND QTR REVENUE MONTH 13-24: SecondQtrRevMth13to24([MNTH1],[DEBIT8],[DEBIT9],[DEBIT10],[DEBIT11],[DEBIT12],[DEBIT13],[DEBIT14],[DEBIT15],[DEBIT16],[DEBIT17],[DEBIT18],[DEBIT19],[DEBIT20],[DEBIT21])
3RD QTR REVENUE MONTH 13-24: ThirdQtrRevMth13to24([MNTH1],[DEBIT5],[DEBIT6],[DEBIT7],[DEBIT8],[DEBIT9],[DEBIT10],[DEBIT11],[DEBIT12],[DEBIT13],[DEBIT14],[DEBIT15],[DEBIT16],[DEBIT17],[DEBIT18])
4TH QTR REVENUE MONTH 13-24: FourthQtrRevMth13to24([MNTH1],[DEBIT2],[DEBIT3],[DEBIT4],[DEBIT5],[DEBIT6],[DEBIT7],[DEBIT8],[DEBIT9],[DEBIT10],[DEBIT11],[DEBIT12],[DEBIT13],[DEBIT14],[DEBIT15])

1ST QTR ENERGY MONTH 13-24: FirstQtrEnerMth13to24([MNTH1],[AKWH11],[AKWH12],[AKWH13],[AKWH14],[AKWH15],[AKWH16],[AKWH17],[AKWH18],[AKWH19],[AKWH20],[AKWH21],[AKWH22],[AKWH23],[AKWH24],[NAKWH11],[NAKWH12],[NAKWH13],[NAKWH14],[NAKWH15],[NAKWH16],[NAKWH17],[NAKWH18],[NAKWH19],[NAKWH20],[NAKWH21],[NAKWH22],[NAKWH23],[NAKWH24])
2ND QTR ENERGY MONTH 13-24: SecondQtrEnerMth13to24([MNTH1],[AKWH8],[AKWH9],[AKWH10],[AKWH11],[AKWH12],[AKWH13],[AKWH14],[AKWH15],[AKWH16],[AKWH17],[AKWH18],[AKWH19],[AKWH20],[AKWH21],[NAKWH8],[NAKWH9],[NAKWH10],[NAKWH11],[NAKWH12],[NAKWH13],[NAKWH14],[NAKWH15],[NAKWH16],[NAKWH17],[NAKWH18],[NAKWH19],[NAKWH20],[NAKWH21])
3RD QTR ENERGY MONTH 13-24: ThirdQtrEnerMth13to24([MNTH1],[AKWH5],[AKWH6],[AKWH7],[AKWH8],[AKWH9],[AKWH10],[AKWH11],[AKWH12],[AKWH13],[AKWH14],[AKWH15],[AKWH16],[AKWH17],[AKWH18],[NAKWH5],[NAKWH6],[NAKWH7],[NAKWH8],[NAKWH9],[NAKWH10],[NAKWH11],[NAKWH12],[NAKWH13],[NAKWH14],[NAKWH15],[NAKWH16],[NAKWH17],[NAKWH18])
4TH QTR ENERGY MONTH 13-24: FourthQtrEnerMth13to24([MNTH1],[AKWH2],[AKWH3],[AKWH4],[AKWH5],[AKWH6],[AKWH7],[AKWH8],[AKWH9],[AKWH10],[AKWH11],[AKWH12],[AKWH13],[AKWH14],[AKWH15],[NAKWH2],[NAKWH3],[NAKWH4],[NAKWH5],[NAKWH6],[NAKWH7],[NAKWH8],[NAKWH9],[NAKWH10],[NAKWH11],[NAKWH12],[NAKWH13],[NAKWH14],[NAKWH15])

 

[bg18461]

DALeffler, I will try out what you posted, but in the mean time, I just attached a mock sampling of my database, thanks for your help.

 

[bg18461]

Thanks for everyone's help, DALeffler's solution worked great for me, thanks for your time and effort in helping someone you don't even know, thanks again my friend, and thanks to all who helped.