"Query too complex" error from Concatenation(?) (1 Viewer)

thardyjackson

Registered User.
Local time
Today, 06:23
Joined
May 27, 2013
Messages
45
Things were going fine :banghead: ....

I'm building a query that creates customized SMS messages based on customer payment statistics.... "Your balance is $100 and please make a payment by Dec 1, 2013."

I have a field called [smsIdentifierToUse] that determines which SMS "template" to use for each customer payment. Values can be smsPmtComplete, smsPmtRcvdDefault, smsPmtRcvdPastDueDate, etc.. I couldn't find a way to have a true template for each [smsIdentifierToUse] value so I've done concatenation in a weird way... Every SMS may or may not have certain phrases like "You're past due" or "Thanks for your payment of .." I build each message by concatenating (or not) each phrase relevant to the particular [smsIdentifierToUse] value. Because the expression was so long I had to break up the expression into 4 parts [msgPart1], [msgPart2], etc that also get concatenated together.

Anyway ... the overall query:
* works quickly before any concatenation
* becomes extremely sluggish to view the results after I add the 4 [msgPart#] expressions to the query
* generates "query too complex" if I add 1 final concatenation expression [msgPart1] & [msgPart2] & [msgPart3] & [msgPart4] AS message,

I know Excel fairly well but am new to Access. Using Access 2007 and Windows 7.

Advice?

Here's the full SQL (red section is the crazy concatenation) I added line breaks for easier viewing:

Code:
SELECT DISTINCTROW tbl_customers.nbrCustAcct, tbl_payments.ID, tbl_payments.pmtCompletedInOut, tbl_payments.pmtDate, tbl_payments.nbrCustAcctUsed, 

DLookUp("[smsIdentifier]","tbl_lookupSms","[smsCategory] ='pmtReceipt'" & "And [mpesaAcct] ='" & [Expr1] & "'" & "And [nbrCustAcctValidity]='" & [nbrCustAcctValidity] & "'" & "And [customerStatus]='" & [statusCustomerAfterPmt] & "'") AS smsIdentifierToUse,

[COLOR="Red"][msgPart1] & [msgPart2] & [msgPart3] & [msgPart4] AS message, 

IIf([smsIdentifierToUse]="smsSuppress",Null,Null) & IIf([smsIdentifierToUse]="smsPmtComplete","Congratulations!  You have made your final Bandika na MoneyMaker payment.  Your  " & [productChosen] & " should be available within 1 week.  KickStart MoneyMaker will send you additional instructions soon.  Please call 0756 081911 if you have questions.",Null) & IIf([smsIdentifierToUse]="smsPmtNoAcctNbr","KickStart MoneyMaker received your M-PESA payment of Tsh " & [pmtCompletedInOut] & ".  Please reply to this SMS with your name and the name of your KickStart MoneyMaker sales representative.",Null) AS msgPart1, 

IIf([smsIdentifierToUse]="smsPmtBelowDeposit" Or [smsIdentifierToUse]="smsPmtRcvdDefault" Or [smsIdentifierToUse]="smsPmtRcvdDetailed" Or [smsIdentifierToUse]="smsPmtRcvdPastDueDate" Or [smsIdentifierToUse]="smsPmtRcvdNotOnTrack" Or [smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","Thank you for your payment of Tsh " & Format([pmtCompletedInOut],"#,##0;(#,##0);0") & " for your " & [productChosen] & ".  You have paid Tsh " & [PmtSumAfterPmt] & " in total.  ",Null) & IIf([smsIdentifierToUse]="smsPmtBelowDeposit","The minimum initial deposit is Tsh " & Format([depositMin],"#,##0;(#,##0);0") & ".  Please send an additional Tsh " & (1*[depositMin]-1*[PmtSumAfterPmt]) & ".  ",Null) & IIf([smsIdentifierToUse]="smsPmtRcvdNotOnTrack","Please continue to make periodic payments to reduce your balance.  ",Null) AS msgPart2, 

IIf([smsIdentifierToUse]="smsPmtRcvdDetailed" Or [smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","Your remaining balance of Tsh " & [balanceDueAfterPmt] & " is due " & [dateFinalPmtDue] & ".  ",Null) & IIf([smsIdentifierToUse]="smsPmtRcvdPastDueDate","Your remaining balance of Tsh " & [balanceDueAfterPmt] & " is now overdue.  Please send a payment of any amount to MoneyMaker-KS (business # 778778) using M-PESA Malipo and reference # " & [nbrCustAcct] & ".",Null) & IIf([smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","The business name and/or reference # was incorrect but we have applied the payment to your account.  ",Null) AS msgPart3,

IIf([smsIdentifierToUse]=[smsIdentifierToUse]="smsPmtBelowDeposit" Or [smsIdentifierToUse]="smsPmtRcvdDefault" Or [smsIdentifierToUse]="smsPmtRcvdDetailed" Or [smsIdentifierToUse]="smsPmtRcvdPastDueDate" Or [smsIdentifierToUse]="smsPmtRcvdNotOnTrack" Or [smsIdentifierToUse]="smsPmtWrongBusNbr" Or [smsIdentifierToUse]="smsPmtWrongRef" Or [smsIdentifierToUse]="smsPmtWrongRegBusNbr","Please send all payments to MoneyMaker-KS (business # 778778) using M-PESA Malipo and reference # " & [nbrCustAcct] & ".  ",Null) & IIf(IsNumeric([discountAvailable]),"  If you pay in full by " & [dateDiscEligibility] & " you will receive a Tsh " & [discountAvailable] & " discount.",Null) AS msgPart4, [/COLOR]

DLookUp("[dateDiscEligibility]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'") AS dateDiscEligibility, Format(DLookUp("[discountAvailable]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'"),"#,###") AS discountAvailable, 

"Correct" AS Expr1,  

Switch(IsNull([dateRegistration]),"Unknown account number",[statusRefund] Is Not Null,"Refund process",1*[PmtSumAfterPmt]>=1*[costTotalPreDiscount],"Payment complete",(([dateGracePeriodEnds] Is Not Null) And ([pmtDate]>[dateGracePeriodEnds])),"Past grace period",(([dateGracePeriodEnds] Is Not Null) And ([pmtDate]<=[dateGracePeriodEnds])),"In grace period",[pmtDate]>[dateFinalPmtDue_Revised],"Past due date",[PmtSumAfterPmt]<[depositMin],"Minimum not met",[balanceExpectedAfterPayment]>=[balanceDueAfterPmt],"On track",[balanceExpectedAfterPayment]<[balanceDueAfterPmt],"Not on track") AS statusCustomerAfterPmt, 

IIf([dateGracePeriodEnds] Is Not Null,[dateGracePeriodEnds],[dateFinalPmtDue]) AS dateFinalPmtDue_Revised, 

Format([costTotalPreDiscount]-[pmtSumAfterPmt],"#,##0;(#,##0);0") AS balanceDueAfterPmt, 

IIf((IsNumeric([price]) And IsNumeric([fee])),([price]*1+[fee]*1)) AS costTotalPreDiscount, 

Format(DSum("[pmtCompletedInOut]","tbl_payments","[pmtDate] <=" & Format([pmtDate],"\#yyyy-mm-dd hh:nn:ss\#") & "And [nbrCustAcctUsed]='" & [nbrCustAcctUsed] & "'"),"#,##0;(#,##0);0") AS pmtSumAfterPmt, 

DLookUp("[statusRefund]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'") AS statusRefund, 

IIf(IsNull(DLookUp("[ID]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcctUsed] & "'")),"Orphan payment",IIf(IsNull(DLookUp("[nbrCustAcctOverride]","tbl_payments","[nbrCustAcctUsed]= '" & [nbrCustAcctUsed] & "'")),"Valid account number used","Account number overriden")) AS nbrCustAcctValidity, 

DLookUp("[dateGracePeriodEnds]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS dateGracePeriodEnds, 

DLookUp("[custOverallStatusNow]","qry_customerStatus","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS custOverallStatusNow,

Format(DLookUp("[depositMin]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0);0") AS depositMin, 

Format(DLookUp("[price]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0);0") AS price, 

Format(DLookUp("[fee]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0);0") AS fee, 

DLookUp("[productChosen]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS productChosen, 

DLookUp("[dateFinalPmtDue]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS dateFinalPmtDue, 

DLookUp("[dateRegistration]","tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'") AS dateRegistration, 

IIf(IsNumeric([monthsElapsedTilPmt]),Format(DLookUp(Switch([monthsElapsedTilPmt]>6,0,[monthsElapsedTilPmt]=6,"[balExpectedAfterMo6]",[monthsElapsedTilPmt]=5,"[balExpectedAfterMo5]",[monthsElapsedTilPmt]=4,"[balExpectedAfterMo4]",[monthsElapsedTilPmt]=3,"[balExpectedAfterMo3]",[monthsElapsedTilPmt]=2,"[balExpectedAfterMo2]",[monthsElapsedTilPmt]<2,"[balExpectedAfterMo2]"),"tbl_customers","[nbrCustAcct]= '" & [nbrCustAcct] & "'"),"#,##0;(#,##0)")) AS balanceExpectedAfterPayment, 

IIf(IsNumeric([dateRegistration]),DateSerial(Year([dateRegistration]),Month([dateRegistration])+2,0)) AS tbd, 

IsNull([monthsElapsedTilPmt]) AS dddd, 

DateDiff("m",[dateRegistration],[pmtDate]) AS monthsElapsedTilPmt

FROM tbl_payments LEFT JOIN tbl_customers ON tbl_payments.nbrCustAcctUsed = tbl_customers.nbrCustAcct

WHERE (((tbl_payments.financialCategory)="PaymentOrRefund") AND ((tbl_payments.msgStatusPmt)<>"Sent" Or (tbl_payments.msgStatusPmt) Is Null))

GROUP BY tbl_customers.nbrCustAcct, tbl_payments.ID, tbl_payments.pmtCompletedInOut, tbl_payments.pmtDate, tbl_payments.nbrCustAcctUsed

HAVING (((tbl_payments.ID)>0))

ORDER BY tbl_customers.nbrCustAcct, tbl_payments.pmtDate, tbl_payments.ID;
 

plog

Banishment Pending
Local time
Today, 08:23
Joined
May 11, 2011
Messages
11,611
I agree with Access, you're query is too complex for my brain as well. I'm not wading through all that to tell you what to do specifically.

So, in general here's what I would do---create a function inside a Module. Have that do the logic for you. You would pass it all the values it needs and it would return a text string based on all that logic you currently have in your query. For example:

SMSmsg: getSMSmessage([Table1].[Field1], [Table1].[Field3], [Table2].[Field1])

Then your function would look like this:

Code:
Function getSMSmessage(V1, V2, V3)

ReturnValue="BlahBlahBlah"

'  Put logic here

getSMSmessage= ReturnValue

End Function
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:23
Joined
Feb 19, 2013
Messages
16,553
The chances are one or more of these values is null

[msgPart1] & [msgPart2] & [msgPart3] & [msgPart4]

try wrapping them in the nz function

nz([msgPart1]) & nz([msgPart2]) & nz([msgPart3]) & nz([msgPart4])
 

thardyjackson

Registered User.
Local time
Today, 06:23
Joined
May 27, 2013
Messages
45
CJ - yes, some are null. tried the nz function but got the same error.

plog - i've never done a module/function but i'll give it a try.

thanks
 

thardyjackson

Registered User.
Local time
Today, 06:23
Joined
May 27, 2013
Messages
45
plog ..

i've tried your recommendation but I keep getting the "Enter parameter value" box for both variables. thoughts? many thanks.

Code:
SQL:
MessageToSend([qry_smsPmtRcpt].[smsIdentifier], [qry_smsPmtRcpt].[nbrCustAcct]) AS myexperiment

Module:
Public Function MessageToSend(SMSID As Variant, NUMCA As Variant)
    MessageToSend = "Your account number is " & NUMCA & "your smsIdentifer is " & SMSID
End Function
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:23
Joined
Feb 19, 2013
Messages
16,553
Try

Code:
Public Function MessageToSend(SMSID As Variant, NUMCA As Variant)[COLOR=red] as string
[/COLOR]
 

thardyjackson

Registered User.
Local time
Today, 06:23
Joined
May 27, 2013
Messages
45
CJ -- adding 'as string' did not work. but, i did notice that the function works fine when i pass values from a table but if i pass values generated within this current query (qry_smsPmtRcpt) i am prompted for the "enter parameter value" -- am i only allowed to pass table values and not query calculations in custom functions? thanks for your help.
 

thardyjackson

Registered User.
Local time
Today, 06:23
Joined
May 27, 2013
Messages
45
i was able to get the custom function to work with query data as long as the function was used in a query separate from the query that was providing the arguments.

many thanks.
 

Users who are viewing this thread

Top Bottom