Email Merge in Word

Hi Susan,

Thanks for getting back & glad it's now sorted.

Peter Jamieson

http://tips.pjmsn.me.uk

Susan May wrote:
Peter - I was copying and pasting from your comments into sql. And the error
messages I was receving didn't look like the text you had told me to put in
each column. Then I figured out that when I cut and pasted your programming,
the line carriage return () was also being copied into the sql. Once I
deleted these, it worked perfectly. I felt so stupid, but learned a great
lesson from you yesterday. Thanks for hanging in there for me. Without my
Microsoft Tech questions, I don't know what I would do. You guys/gals have
helped me tremendously with different issues as I get no support from my IT
department here.

Thanks so much again.

Susan

"Peter Jamieson" wrote:

Did you manage to work out from that what the character(s) causing the
problem was/were?

Peter Jamieson

http://tips.pjmsn.me.uk

Susan May wrote:
That worked Peter. Thanks so much for your help!

"Peter Jamieson" wrote:

three columns, but when I ran the query I am stil coming up with
records that
have blank email address.

OK, but at least it ran :-)

Can you do me a favour and look at the SQL pane for that query, and
copy/paste the code in here? I'd just like to compare it with what we
tried before.

Let's try the other query now - I have modified it a bit:

The SQL version:
SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & ' ' & [DD2
LIST].[LastName] AS MergedName, Len(Trim(Email1Address)) AS Expr1,
Asc(Mid(Trim(Email1Address),1,1)) AS Expr2
FROM [DD2 LIST]
WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2
LIST].Email1Address) ''

The query designer version:

Column 1
--------
Field: Email1Address
Table: DD2 LIST
Show: checked
Criteria: Is Not Null

Column 2
--------
Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName]
Show: checked

Column 3
--------
Field: Expr1: Len(Trim(Email1Address))
Show: checked

Column 4
--------
Field: Expr2: Asc(Mid(Trim(Email1Address),1,1))
Show: checked

Column 2
--------
Field: Trim([DD2 LIST].[Email1Address])
Show: checked
Criteria: ''

(All the quotes in there are single quotes, but you can probably use
double quotes if you prefer).

With any luck, this should list the same records that you had before,
but tell us something about what is in Email1Address

BTW I may not be able to post back until tomorrow (UK time) now...


Peter Jamieson

http://tips.pjmsn.me.uk

Susan May wrote:
Yes, field name is DD2 List. I typed exactly what you told me to do in the
three columns, but when I ran the query I am stil coming up with records that
have blank email address. What's wrong with this damn program?

"Peter Jamieson" wrote:

This is so
frustrating

Yes. I have a feeling there is something different about the way our
Access systems are set up but as a different approach, perhaps it's
worth trying to copy the three columns that I see /in the visual designer/:

Column 1
--------
Field: Email1Address
Table: DD2 LIST
Show: checked
Criteria: Is Not Null


Column 2
--------
Field: MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName]
Show: checked


Column 3
--------
Field: Trim([DD2 LIST].[Email1Address])
Show: not checked
Criteria: ""

(FWIW I do not think it makes any difference here but I tend to use
single quotes rather than double quotes to surround strings in Access
queries, i.e.

Column 1
--------
Field: Email1Address
Table: DD2 LIST
Show: checked
Criteria: Is Not Null


Column 2
--------
Field: MergedName: [DD2 LIST].[FirstName] & ' ' & [DD2 LIST].[LastName]
Show: checked


Column 3
--------
Field: Trim([DD2 LIST].[Email1Address])
Show: not checked
Criteria: ''

The table is called "DD2 LIST"? (not, e.g. "DD2_LIST" ?)


Peter Jamieson

http://tips.pjmsn.me.uk

Susan May wrote:
Peter, it gives me DD2 - Enter Parameter Value and I click ok, and the blank
emails are still in there, but when I looked in the design view, under merged
name, this is what it shows.

MergedName: [DD2 LIST].[FirstName] & " " & [DD2

I tried to finish the string and it tells me the expression you entered
contains invalid syntax. You may have entered an operand without an operator

MergedName: [DD2 LIST].[FirstName] & " " & [DD2 LIST].[LastName]
LIST].[LastName]

This above is what I have now and the query will not run. This is so
frustrating - you'd think this should be so simple in Access and all you
should have to do is say is not null under Email1Address, but it's not.

Help!

"Peter Jamieson" wrote:

Hi Susan,

1. In that SQL pane, can you first try replacing what you have with

SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2
LIST].[LastName] AS MergedName
FROM [DD2 LIST]
WHERE [DD2 LIST].Email1Address IS NOT NULL AND trim([DD2
LIST].Email1Address) ""

which is the query I think you should be running, so
a. try executing that
b. if you want, see how that "translates" to the settings in the
visual designer (at this point you may find it easier to work in the SQL
pane)

2. If that does not function, try

SELECT [DD2 LIST].Email1Address, [DD2 LIST].[FirstName] & " " & [DD2
LIST].[LastName] AS MergedName, Len(Trim("Email1Address")),
Asc(Mid(Trim("Email1Address"),1,1))
FROM [DD2 LIST]

which may give some further info.


Peter Jamieson

http://tips.pjmsn.me.uk

Susan May wrote:
Here Peter:

SELECT [DD2 LIST].Email1Address, [FirstName] & " " & [LastName] AS MergedName
FROM [DD2 LIST]
WHERE ((([DD2 LIST].Email1Address)=Len(Trim("Email1Address")) Or ([DD2
LIST].Email1Address)=Asc(Mid(Trim("Email1Address"),1,1) )));


"Peter Jamieson" wrote:

Susan,

What am I doing wrong?

I don't know - not necessarily anything.

NB I was intending these test queries to be executed entirely within
Access - is that what you are doing?

Can you please
a. look at the SQL for your query (if you haven't done this before,
try right-clicking in the title bar of the query designer and selecting
the SQL option) and copy/paste a copy here?
b. tell us exactly what the error message says?

Peter Jamieson

http://tips.pjmsn.me.uk

Susan May wrote:
Peter when I do this WHERE mystring is not null AND trim(mystring) '' I
get an error message about the string 2048 characters so it doesn't work.

I do this - len(trim(Email1Address))
asc(mid(trim(Email1Address),1,1)) and no records appear.
What am I doing wrong?

Susan

"Peter Jamieson" wrote:

1. FWIW you can probably reduce the criteria I suggested to

WHERE mystring is not null AND trim(mystring) ''

since the other condition should be taken care of by the trim() condition.

2. I think the next thing to do is check what's actually in that field
in some of the records that should be eliminated. (I tend to work
directly on the SQL, but if you are more familiar with the graphical
designer I'd try to stick with that).

3. e.g. add a few columns to your query such as
len(trim(Email1Address))
asc(mid(trim(Email1Address),1,1))

If the address looks blank but len(trim(Email1Address)) is 0 then the
field probably contains invisible non-space characters. The asc()
function should tell us what the first character is. You can use
asc(mid(trim(Email1Address),2,1))
etc. to look at the other characters.

Peter Jamieson

http://tips.pjmsn.me.uk

Susan May wrote:
Peter: I tried this WHERE mystring is not null AND mystring '' AND
trim(mystring) '' with mystring name as Email1Address, and it created 3
different columns with the criteria "is not null"; "; and
Trim(Emai1lAddress) with criteria ", and I'm still getting blank emails.
What did I do wrong?


"Peter Jamieson" wrote:

Also, when I set up my query, I selected the criteria for the email
address
field "is not null", and there are still some blank records with no
emails.

Different software packages can treat "null", "a string set to ''", and
"a string containing white space differently, and may also treat
variable-length and fixed-length data differently in this respect.

As far as Access is concerned, in some cases if you enter "white space",
Access leaves the field value as "null". However, if you set a text
value to '' in code, it isn't regarded as null but will otherwise look
no different to the user in many cases.

So generally speaking, you have to test a string for both null and
blank. to detect that in the SQL in Access you need something like

WHERE mystring is not null AND mystring ''

If the string could contain one or more spaces, you would probably need
to add another condition, e.g.

WHERE mystring is not null AND mystring '' AND trim(mystring) ''

but if mystring could contain other types of "whitespace" such as tabs,
non-breaking spaces, I think you would need to ask an Access person. You
might be able to use LIKE with a pattern to do it.


Peter Jamieson

http://tips.pjmsn.me.uk