Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-27-2013, 02:56 AM   #1
thardyjackson
Newly Registered User
 
Join Date: May 2013
Location: Nairobi, Kenya
Posts: 45
Thanks: 23
Thanked 0 Times in 0 Posts
thardyjackson is on a distinguished road
Using mail merge concept to generate SMS

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.

thardyjackson is offline   Reply With Quote
Old 05-27-2013, 10:14 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,501
Thanks: 13
Thanked 1,442 Times in 1,374 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Using mail merge concept to generate SMS

You have to concatenate the string in the query.

Select "Your balance is " & AcctBal & "." As Msg, AcctID, FirstName, LastName, ....
From YourTable;
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 05-28-2013, 02:50 AM   #3
thardyjackson
Newly Registered User
 
Join Date: May 2013
Location: Nairobi, Kenya
Posts: 45
Thanks: 23
Thanked 0 Times in 0 Posts
thardyjackson is on a distinguished road
Re: Using mail merge concept to generate SMS

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?

thardyjackson is offline   Reply With Quote
Old 05-28-2013, 11:32 AM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,501
Thanks: 13
Thanked 1,442 Times in 1,374 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Using mail merge concept to generate SMS

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
concatenation , mail merge , parameters , sms

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically Generate and Send an E-mail LadyDi Modules & VBA 14 05-29-2014 05:44 AM
E-mail/ mail merge/ automation. Almost there...?! mattkorguk Modules & VBA 9 05-29-2008 11:27 PM
Database Password stopping mail merge and catalog merge???!??! sgtblitz General 1 04-17-2008 10:46 AM
Generate E-mail Evenflow General 1 04-26-2005 09:55 AM
Mail Merge E-mail body iazcac Forms 0 01-11-2005 10:35 AM




All times are GMT -8. The time now is 06:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World