Field argument too complex HELP needed to simplify

[seenfresh]

Greetings,

I am receiving a "Query too complex" error when I am attempting to run one of my queries and it is a result of a complex argument passed to a field in my query via vba. Below are two examples of my data. "Example 1" is a simple example of the original data that lies in one of my data tables. "Example 2" is how I would like to report this data via a query.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;color: #FF0000;;">EXAMPLE_1:</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Advertiser</td><td style="font-weight: bold;;">Start Date</td><td style="font-weight: bold;;">End Date</td><td style="font-weight: bold;;">Booked Revenue</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Ford</td><td style="text-align: right;;">1/1/2011</td><td style="text-align: right;;">3/15/2011</td><td style="text-align: right;;"> $ 10,000 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Toyota</td><td style="text-align: right;;">2/5/2011</td><td style="text-align: right;;">10/15/2011</td><td style="text-align: right;;"> $ 30,000 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Audi</td><td style="text-align: right;;">2/1/2011</td><td style="text-align: right;;">4/30/2011</td><td style="text-align: right;;"> $ 25,000 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;border-bottom: 1px solid black;color: #FF0000;;">EXAMPLE_2:</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #F2F2F2;;">Advertiser</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Start Date</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">End Date</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Booked Revenue</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">1/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">2/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">3/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">4/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">5/1/2011</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #F2F2F2;;">Total</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="border-top: 1px solid black;border-left: 1px solid black;;">Ford</td><td style="text-align: right;border-top: 1px solid black;;">1/1/2011</td><td style="text-align: right;border-top: 1px solid black;;">3/15/2011</td><td style="text-align: right;border-top: 1px solid black;;"> $ 10,000 </td><td style="text-align: right;border-top: 1px solid black;;"> 4,189.19 </td><td style="text-align: right;border-top: 1px solid black;;"> 3,783.78 </td><td style="text-align: right;border-top: 1px solid black;;"> 2,027.03 </td><td style="text-align: right;border-top: 1px solid black;;"> - </td><td style="text-align: right;border-top: 1px solid black;;"> - </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"> 10,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="border-left: 1px solid black;;">Toyota</td><td style="text-align: right;;">2/5/2011</td><td style="text-align: right;;">5/30/2011</td><td style="text-align: right;;"> $ 30,000 </td><td style="text-align: right;;"> - </td><td style="text-align: right;;"> 6,260.87 </td><td style="text-align: right;;"> 8,086.96 </td><td style="text-align: right;;"> 7,826.09 </td><td style="text-align: right;;"> 7,826.09 </td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;;"> 30,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="border-bottom: 1px solid black;border-left: 1px solid black;;">Audi</td><td style="text-align: right;border-bottom: 1px solid black;;">2/1/2011</td><td style="text-align: right;border-bottom: 1px solid black;;">4/30/2011</td><td style="text-align: right;border-bottom: 1px solid black;;"> $ 25,000 </td><td style="text-align: right;border-bottom: 1px solid black;;"> - </td><td style="text-align: right;border-bottom: 1px solid black;;"> 7,865.17 </td><td style="text-align: right;border-bottom: 1px solid black;;"> 8,707.87 </td><td style="text-align: right;border-bottom: 1px solid black;;"> 8,426.97 </td><td style="text-align: right;border-bottom: 1px solid black;;"> - </td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;"> 25,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">Total</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 4,189.19 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 17,909.82 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 18,821.85 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 16,253.05 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 7,826.09 </td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #DBEEF3;;"> 65,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;;">*** example of how we need to convert the data via a query into the above format… booked revenue broken out by month.  This is done by pro-rating based on days in month and length of campaign</td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

In "Example 2" ws cell E11 the following excel formula exists:

=IF(E$10="","",$D11*(IF(AND(DATE(YEAR($B11),MONTH($B11),DAY(1))=DATE(YEAR($C11),MONTH($C11),DAY(1)),MONTH(E$10)=MONTH($C11),YEAR(E$10)=YEAR($C11)),1,IF(OR($B11>DATE(YEAR(E$10),MONTH(E$10)+1,0),$C11<E$10),0,IF(AND(YEAR(E$10)=YEAR($C11),MONTH(E$10)=MONTH($C11)),DAY($C11),(DAY(EOMONTH(E$10,0))-DAY(MAX(E$10,$B11)))+1)/($C11-$B11+1)))))

This formula is used to determine if the order start date and end date applies to the given month (header) and if so pro-rate the total cost of the order to the month based on the number of days.

I have build an Access Query that dynamically builds out a table similar to "Example 2" above based on "Example 1" data table, however I need help simplifying my formula noted above... it seems to be too complex, it works in Excel but too complex for Access to handle. Below is the same code for one of my data fields but configured for VB and SQL which is passed to each field in my query:

1/1/2008: [Total_Net_Revenue]*IIf((Month([Start_Date])=Month([End_Date])) And (Year([Start_Date])=Year([End_Date])),1,IIf(([Start_Date]>DateSerial(Year(#1/1/2008#),Month(#1/1/2008#),0)) Or ([End_Date]<#1/1/2008#),0,IIf((Year(#1/1/2008#)=Year([End_Date])) And (Month(#1/1/2008#)=Month([End_Date])),Day([End_Date]),(Day(DateSerial(Year(#1/1/2008#),Month(#1/1/2008#),0))-Day(IIf(#1/1/2008#>[Start_Date],#1/1/2008#,[Start_Date]))+1)/([End_Date]-[Start_Date]+1))))

Does anyone have any suggestions regarding how to simplify this formula/code or other methods of obtaining my desired results evident in "Example 2"?

Thanks in advance for your help... I've hit a road block LOL

 

[seenfresh]

Thank you gentleman...

I will experiment with both approaches and message back soon!

Your help is awesome!

 

[seenfresh]

Gentleman,

It took me a little while to accomplish my task and it looks like I managed to do it all using Access.

In short, based on the original data table, I build another data table to mock how I needed the data to be laid-out/structured and performed the calculations to pro rate the $ using vba. Everything was accomplish through vba. I figured this was the best option and allowed me to brush up on my vba skills.

I haven't spent much time condensing or reviewing my code, but here what I put together to accomplish this task: Hope it is not too confusing. Basically two command buttons used. One to create a table (by month)"YrMos" of the earliest and latest dates I desire to be used as a basis for constructing my table fields. The other button goes through each record in the original data source and creates a mock table building out additional fields based on each month in the "YrMos" table and perform the calculations to see if any $ should be pro rated to the particular month, cycling each record and each date field at a time. Surprisingly it only takes roughly 1 minute of code run-time.

Option Compare Database

Private Sub Command0_Click()

Dim StartYear As Integer
Dim EndYear As Integer

StartYear = Year(Me.MinList1.Value)
EndYear = Year(Me.MaxList1.Value)

On Error GoTo Step1:
Step1:
DoCmd.DeleteObject acTable, "YrMos"
GoTo Step2:

Step2:
Dim Y As Integer, M As Integer

    DoCmd.SetWarnings False
    DoCmd.RunSQL "CREATE TABLE YrMos " & _
                 "(MoStart DATE CONSTRAINT MoStartIndex PRIMARY KEY, " & _
                 " MoEnd Date CONSTRAINT MoEndIndex UNIQUE, " & _
                 " Yr Integer, Mo Integer, DaysInMo Integer, " & _
                 " CONSTRAINT YrMoIndex UNIQUE (Yr, Mo))"
    For Y = StartYear To EndYear
        For M = 1 To 12
            DoCmd.RunSQL "INSERT INTO YrMos (MoStart, MoEnd, Yr, Mo, DaysInMo) " & _
                         "VALUES (#" & DateSerial(Y, M, 1) & "#, #" & DateSerial(Y, M + 1, 0) & "#, " & Y & ", " & M & ", " & Day(DateSerial(Y, M + 1, 0)) & ")"
        Next M
    Next Y
    DoCmd.SetWarnings True
End Sub

Private Sub Command1_Click()
'=================POPULATING THE SITE QUERY======================================================================'
myQuery = "[Booked_Sales_Prep v1]"
myTable = "YrMos"
myTable2 = "Booked Sales Data Table"
Dim strSQL5 As String
Dim strSQL6 As String
Dim qdf As DAO.QueryDef
Dim qdf1 As DAO.TableDef
    Dim varItem As Variant
    Dim str1 As String
    Dim str2 As String
    
Set qdf2 = CurrentDb.TableDefs(myTable2)


Dim rsLive As Recordset
Dim rsLive2 As Recordset
Dim rcd As Recordset
Dim fld As Field
Dim fld1 As DAO.Field
Dim tbl1 As TableDef
Dim meDate As Variant, sDate, eDate
Dim mivalue As Currency
Dim fival As Double
Dim mirate As Double, nwval
Dim myString As String
Dim Db As Database

Set Db = CurrentDb()
Set rsLive = Db.OpenRecordset(myTable, dbOpenDynaset)

myString = ""

Do While Not rsLive.EOF
   
        meDate = rsLive![MoStart]
        
        myString = myString & ",0" & " AS [" & meDate & "]"
            
    rsLive.MoveNext
Loop


rsLive.Close
Set rsLive = Nothing

st1 = "SELECT [Booked Sales Data Table].[Import Date], [Booked Sales Data Table].OID, [Booked Sales Data Table].LID, [Booked Sales Data Table].Agency, [Booked Sales Data Table].Advertiser, [Booked Sales Data Table].Campaign, [Booked Sales Data Table].Placement_Name, [Booked Sales Data Table].Start_Date, [Booked Sales Data Table].End_Date, DateDiff('d',[Start_Date],[End_Date]) AS Length_Days, [Booked Sales Data Table].Gross_Value, [Booked Sales Data Table].Total_Net_Revenue"
st2 = myString
st4 = " INTO [Adj Booked Sales Table]"
st3 = " FROM [Booked Sales Data Table];"

strSQL5 = st1 & st2 & st4 & st3

On Error GoTo err_handler
 
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL5
DoCmd.SetWarnings True
 
err_handler:

If Err.Number <> 0 Then
    MsgBox Err.Description, vbExclamation, "Error Number: " & Err.Number
    
    Exit Sub
End If
'======Export Data to Excel Application CODE HERE for SQL string Testing=============================================
'Dim appExcel As Excel.Application
'Dim wbk As Excel.Workbook
'Dim strfilename As String
'Dim wks As Excel.Worksheet
'strfilename = "book1"
 'Set appExcel = New Excel.Application
 'appExcel.Visible = True
'If Dir(strfilename) <> "" Then
'Set wbk = appExcel.Workbooks.Open(strfilename)
'Else
'Set wbk = appExcel.Workbooks.Add
'End If
'Set wks = wbk.Worksheets("sheet1")

        'With wbk.Worksheets("sheet1")
            '.Range("A1").Value = strSQL5
            
        ' End With
            
'===============================================================================================

    Set tbl1 = Db.TableDefs("Adj Booked Sales Table")
         Set fld1 = tbl1.CreateField("Grand Total", dbDouble)
        tbl1.Fields.Append fld1
        
    Set tbl1 = Nothing
    Set fld1 = Nothing


Set rsLive2 = Db.OpenRecordset("Adj Booked Sales Table", dbOpenDynaset)



Do While Not rsLive2.EOF

  fival = 0
        
    For Each fld In rsLive2.Fields
    
 
            If fld.Name Like "*/*" Then
            
        sDate = rsLive2![Start_Date]
        eDate = rsLive2![End_Date]
        mivalue = rsLive2![Total_Net_Revenue]
        mirate = mivalue / ((eDate - sDate) + 1)
        miDate = DateValue(Format(fld.Name, "mm/dd/yy"))
        
                If DateSerial(Year(miDate), Month(miDate) + 1, 0) >= DateSerial(Year(sDate), Month(sDate) + 1, 0) And DateSerial(Year(miDate), Month(miDate) + 1, 0) <= DateSerial(Year(eDate), Month(eDate) + 1, 0) Then
                
                        If DateSerial(Year(sDate), Month(sDate), 1) = miDate Then
                        
                            If DateSerial(Year(sDate), Month(sDate), 1) = DateSerial(Year(eDate), Month(eDate), 1) Then
                            
                            nwval = mivalue
                            
                            fival = fival + nwval
                            
                            GoTo Step1:
                            
                            End If
                        
                             nwval = DateSerial(Year(miDate), Month(miDate) + 1, 0) - DateSerial(Year(sDate), Month(sDate), Day(sDate)) + 1
                                nwval = nwval * mirate
                                
                                fival = fival + nwval
                                
                                GoTo Step1:
                             ElseIf DateSerial(Year(eDate), Month(eDate), 1) = miDate Then
                                        nwval = Day(DateSerial(Year(eDate), Month(eDate), Day(eDate)))
                                    nwval = nwval * mirate
                                    
                                    fival = fival + nwval
                                    
                                    GoTo Step1:
                                  Else
                                       nwval = Day(DateSerial(Year(miDate), Month(miDate) + 1, 0)) * mirate
                                       
                                       fival = fival + nwval
                         End If
                                
Step1:
               rsLive2.Edit
                    rsLive2(fld.Name).Value = nwval
                rsLive2.Update
                
                End If
                
        End If
        
         If fld.Name = "Grand Total" Then
            rsLive2.Edit
                    rsLive2("Grand Total").Value = fival
                rsLive2.Update
                
      End If
        
        
        Next
                
   rsLive2.MoveNext
         fival = 0
Loop


rsLive2.Close
Set rsLive2 = Nothing
Set Db = Nothing
Set fld = Nothing
Set tbl1 = Nothing
Set fld1 = Nothing


End Sub