Query Error

[millhouse123]

I have two queries that are almost identical and I can't figure out why I get an error on one of them.

the working query

SELECT qry_final_CMS_incentives_by_line_item.*
FROM qry_final_CMS_incentives_by_line_item
WHERE (((IIf([TempVars]![varIncentiveTo]=[IncentiveTo],True,False))=True));

The non working query -
SELECT qry_final_CMS_incentives_by_month_totals.*
FROM qry_final_CMS_incentives_by_month_totals
WHERE (((IIf([TempVars]![varIncentiveTo]=[IncentiveTo],True,False))=True));


When I remove the WHERE clause from the second it works fine. The error I get is "This expression is typed incorrectly, or it is too complex to be evaluated."

Any advice would be very much appreciated.

 

[Joe4]

Note that all this:

[COLOR=#333333]WHERE (((IIf([TempVars]![varIncentiveTo]=[IncentiveTo],True,False))=True));[/COLOR]

can be simplified to just this:

[COLOR=#333333]WHERE ([TempVars]![varIncentiveTo]=[IncentiveTo]);[/COLOR]

By definition, if they are equal, it will return TRUE. If not, it will return FALSE.

So, where is this "[TempVars]![varIncentiveTo]" coming from?
Is "[IncentiveTo]" a field in your table?
Is it populated for every record? Are there are error or odd entries in this field?

 

[ranman256]

you will get the 'too complicated' it theres a lot going on in: qry_final_CMS_incentives_by_month_totals.
sometimes you have to combine the 2 queries to 'simplify'.

 

[millhouse123]

Thanks for the response.
the value is populated via VBA -

[IncetiveTo] is a field in my table - it only has 20 or so records and none look odd. The values in the query are being pulled from same source table as the query that works.

Public Function ExportCMSIncentiveEvents(sendEmail As Boolean)
    
    Dim SDRIncentiveQuery1 As String
    Dim newFilePath As String
    SDRIncentiveQuery1 = "qry_sys_CMS_incentives_by_line_item_for_email_export"
    
    Dim SDRIncentiveQuery2 As String
    SDRIncentiveQuery2 = "qry_sys_CMS_incentives_by_month_for_email_export"
   
    Dim path As String
    path = CurrentProject.path & "\ExportedIncentives"
    CreateFolder path
    Dim fileName As String
    
    [TempVars]![varIncentiveTo] = Null
    
    Dim db As Database
    Set db = CurrentDb
    Dim rs As Recordset
    
    sql = "Select * From qry_sys_email_CMS_recipient_list"


    Set rs = db.OpenRecordset(sql)
    
    Do While Not rs.EOF
        newFilePath = path & "\" & rs("SalesforceName")
        CreateFolder newFilePath
        fileName = newFilePath & "\" & Year(Now()) & Month(Now()) & Day(Now()) & "_incentive_line_items_" & Replace(rs("SalesforceName"), " ", "_") & ".xlsx"


        On Error Resume Next
        Kill fileName
        On Error GoTo 0
        
        fileName2 = newFilePath & "\" & Year(Now()) & Month(Now()) & Day(Now()) & "_incentive_month_totals_" & Replace(rs("SalesforceName"), " ", "_") & ".xlsx"
        On Error Resume Next
        Kill fileName2
        On Error GoTo 0
        
        [TempVars]![varIncentiveTo] = rs("SalesforceName").Value
        DoCmd.OutputTo acOutputQuery, SDRIncentiveQuery1, acFormatXLSX, fileName, False
        DoCmd.OutputTo acOutputQuery, SDRIncentiveQuery2, acFormatXLSX, fileName2, False
        [TempVars]![varIncentiveTo] = Null
        
        If sendEmail Then
            SendMessage rs("SalesforceName").Value, rs("Email Address"), "Latest incentives for " & rs("SalesforceName").Value, "See attachments. To continue participating in the incentive program, you must reply to the updated version of this email every week to verify accuracy.", fileName, fileName2
        End If
        
        rs.MoveNext
         
    Loop
   
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing


End Function

 

[millhouse123]

There is nothing crazy going on in the source queries as there are about 4 queries between the main source table and the final query we are troubleshooting. I will try to consolidate them into a single query or two. I inherited this db and am in the possess of moving to the cloud so that we can more easily manage and utilize full SQL. I just need to get this to work in the mean time.

SELECT qry_CMS_3_total_incentives_by_month.PlanMonth, qry_CMS_3_total_incentives_by_month.IncentiveTo, qry_CMS_3_total_incentives_by_month.[Potential Incentive], qry_CMS_3_total_incentives_by_month.[Qualifying Units], qry_CMS_3_total_incentives_by_month.Quota, qry_CMS_3_total_incentives_by_month.[Percent Performance], qry_CMS_3_total_incentives_by_month.[Incentive Earned]
FROM qry_CMS_3_total_incentives_by_month;

SELECT qry_CMS_2_lookup_relevant_transactions_and_potentials.PlanMonth, qry_CMS_2_lookup_relevant_transactions_and_potentials.IncentiveTo, Sum(CCur([Incentive Potential])) AS [Potential Incentive], Sum(qry_CMS_2_lookup_relevant_transactions_and_potentials.[Quota Units]) AS [Qualifying Units], FBN_Sales_Quotas.[Month CM Units Quota] AS Quota, FBN_Sales_Quotas.[Accelerator Maximum], Sum([Quota Units])/Avg([Month CM Units Quota]) AS PP, IIf([PP]>Avg([Accelerator Maximum]),Avg([Accelerator Maximum]),[PP]) AS MultBy, FormatPercent([MultBy]) AS [Percent Performance], CCur([MultBy]*Sum([Incentive Potential])) AS [Incentive Earned]
FROM qry_CMS_2_lookup_relevant_transactions_and_potentials INNER JOIN FBN_Sales_Quotas ON (qry_CMS_2_lookup_relevant_transactions_and_potentials.IncentiveTo = FBN_Sales_Quotas.[Employee Salesforce Name]) AND (qry_CMS_2_lookup_relevant_transactions_and_potentials.PlanMonth = FBN_Sales_Quotas.[EOM Plan Date])
GROUP BY qry_CMS_2_lookup_relevant_transactions_and_potentials.PlanMonth, qry_CMS_2_lookup_relevant_transactions_and_potentials.IncentiveTo, FBN_Sales_Quotas.[Month CM Units Quota], FBN_Sales_Quotas.[Accelerator Maximum]
ORDER BY qry_CMS_2_lookup_relevant_transactions_and_potentials.PlanMonth DESC;

SELECT qry_CMS_1_lookup_transaction_sequences.ID AS TransactionID, qry_CMS_1_lookup_transaction_sequences.EffectiveDate, qry_CMS_1_lookup_transaction_sequences.EnterpriseID, qry_CMS_1_lookup_transaction_sequences.Quantity, qry_CMS_1_lookup_transaction_sequences.Subtotal, qry_CMS_1_lookup_transaction_sequences.ProductID, FBN_CMS_Incentive_Plan_Detail.Sequence, FBN_CMS_Incentive_Plan_Detail.[Quota Units], Redshift_Salesforce_User.name AS IncentiveTo, qry_CMS_1_lookup_transaction_sequences.ExternalID, IIf([Multiplier]="Quantity",[Incentive]*[Quantity],IIf([Multiplier]="1",[Incentive],IIf([Multiplier]="Subtotal",Nz([Subtotal])*[Incentive],0))) AS [Incentive Potential], qry_CMS_1_lookup_transaction_sequences.Notes, qry_CMS_1_lookup_transaction_sequences.TransactionTypeID, (DateSerial(Year([EffectiveDate]),Month([EffectiveDate])+1,0)) AS PlanMonth
FROM (qry_CMS_1_lookup_transaction_sequences INNER JOIN FBN_CMS_Incentive_Plan_Detail ON (qry_CMS_1_lookup_transaction_sequences.TransactionTypeID = FBN_CMS_Incentive_Plan_Detail.TransactionTypeID) AND (qry_CMS_1_lookup_transaction_sequences.ProductID = FBN_CMS_Incentive_Plan_Detail.ProductID) AND (qry_CMS_1_lookup_transaction_sequences.Sequence = FBN_CMS_Incentive_Plan_Detail.Sequence)) INNER JOIN (Redshift_Salesforce_User INNER JOIN FBN_Sales_Roster AS roster ON Redshift_Salesforce_User.name = roster.[Salesforce Username]) ON qry_CMS_1_lookup_transaction_sequences.Closer = Redshift_Salesforce_User.sf_id
WHERE (((IIf([EffectiveDate]>=roster.[CMS Start Date] And (IsNull(roster.[CMS End Date]) Or [EffectiveDate]<=roster.[CMS End Date]),"CMS","None"))="CMS") And ((FBN_CMS_Incentive_Plan_Detail.[Starting At])<=[EffectiveDate]) And ((FBN_CMS_Incentive_Plan_Detail.[Ending At])>=[EffectiveDate]));

SELECT FBN_Transaction.*, IIf(IsNull([qry_lookup_first_time_marketing_contracts_by_type_and_entity].[MaxOfTransactionID]),"Recurring","First") AS SequenceBefore1130, IIf(IsNull([qry_lookup_first_time_marketing_contracts_by_type].[MaxOfTransactionID]),"Recurring","First") AS SequenceAfter1201, IIf([EffectiveDate]<=#11/30/2017#,[SequenceBefore1130],[SequenceAfter1201]) AS Sequence
FROM (FBN_Transaction LEFT JOIN qry_lookup_first_time_marketing_contracts_by_type_and_entity ON FBN_Transaction.ID = qry_lookup_first_time_marketing_contracts_by_type_and_entity.MaxOfTransactionID) LEFT JOIN qry_lookup_first_time_marketing_contracts_by_type ON FBN_Transaction.ID = qry_lookup_first_time_marketing_contracts_by_type.MaxOfTransactionID
WHERE (((FBN_Transaction.TransactionTypeID)=26 Or (FBN_Transaction.TransactionTypeID)=27));

 

[xenou]

You seem to have a lot of queries chained together. Can you provide the information on what the queries are named so we can work out which queries are calling which other queries...
I was about to assume that the first called the second called the third called the fourth, but then I found a fifth (?) so I give up.

Note that my first test would be to replace your where condition with a literal value to test the base query apart from the syntax issues:

SELECT qry_final_CMS_incentives_by_month_totals.*
FROM qry_final_CMS_incentives_by_month_totals
WHERE [B][COLOR="#FF0000"](((IIf([TempVars]![varIncentiveTo]=[IncentiveTo],True,False))=True))[/COLOR][/B];

But this leads me to wonder what that is really supposed to be. Seems like you get either:

SELECT qry_final_CMS_incentives_by_month_totals.*
FROM qry_final_CMS_incentives_by_month_totals
WHERE [B][COLOR="#FF0000"]True=True[/COLOR][/B];

or

SELECT qry_final_CMS_incentives_by_month_totals.*
FROM qry_final_CMS_incentives_by_month_totals
WHERE [B][COLOR="#FF0000"]False=True[/COLOR][/B];

The first does nothing and the second returns nothing



So possible Joe's suggested edit is really what you wanted.

In which case:

SELECT qry_final_CMS_incentives_by_month_totals.*
FROM qry_final_CMS_incentives_by_month_totals
WHERE [B][COLOR="#FF0000"][IncentiveTo]='Something'[/COLOR][/B];

if IncentiveTo is text, enclose in quotes as I did above. If it is numeric then don't use the quotes.

SELECT qry_final_CMS_incentives_by_month_totals.*
FROM qry_final_CMS_incentives_by_month_totals
WHERE [B][COLOR="#FF0000"][IncentiveTo]=100[/COLOR][/B];