Using mail merge concept to generate SMS (1 Viewer)

thardyjackson

Registered User.
Local time
Today, 08:07
Joined
May 27, 2013
Messages
45
I want to generate a file of customized SMS messages for my customers. There are 10 types of messages "your balance is xxx" "thanks for your payment of xxx" "we have not received a payment since xx/xx/xx"

I've tried creating an "SMS" table with 10 generic messages in 10 records... record 1 is [Full Name] & "thank you for your payment of " & [PaymentAmt] and record 2 is "Your balance is" & [Balance]. A separate query linked to my payments table picks the correct record from the SMS table but everything is treated like text.

Instead of generating SMS messages like:
Mr. Johnson thank you for your payment of $100.

my query generates this for all customers:
[Full Name] & "thank you for your payment of " & [PaymentAmt]

How can I get the parameters to be treated as parameters rather than text string?

Cheers.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:07
Joined
Feb 19, 2002
Messages
42,985
You have to concatenate the string in the query.

Select "Your balance is " & AcctBal & "." As Msg, AcctID, FirstName, LastName, ....
From YourTable;
 

thardyjackson

Registered User.
Local time
Today, 08:07
Joined
May 27, 2013
Messages
45
Pat. I prefer that the concatenation occurs in a separate table rather than the query. It will make tweaking the message content easier. I've tried using an eval function but it's not working for me. I always get a #ERROR. Here's some more info:

tbl_SMS_templates (lists 10 SMS templates)
important field names are "templateId" and "smsTemplate" and values under the latter are:
Record 1: "Your balance is " & [balance]
Record 2: [Full Name] & "thank you for your payment of " & [PaymentAmt]
Record 3: etc.

qry_SMS_generation (generates a list of customized SMS messages)
important fields are:

customExpression: DLookUp("[smsTemplate]","tbl_sms_templates","[templateId]= '" & [templateIdToUse] & "'") <-- this pulls the relevant template from tbl_SMS_templates like "Your balance is " & [balance]

customSMS: Eval([customExpression]) <-- i was hoping this would display the custom text message but I am getting #ERROR

Thoughts?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:07
Joined
Feb 19, 2002
Messages
42,985
Eval doesn't have any way of knowing how to find [balance] or [first name], etc. You are going to have to use queries to find the data and do the substitution yourself.
 

Users who are viewing this thread

Top Bottom