469,921 Members | 2,215 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,921 developers. It's quick & easy.

Query - Mid Function & Mail Merge


I'm using Access 2000.

I have a Select Query that uses the MID function to separate the actual text
of articles from the title of the articles. The articles are enterd into
the underlying table (in a memo field) in html format, as one big block of
text. The memo field is called [ArticleBody] (I named it before I realized
that I needed to separate title from text).

The title of each article is enclosed in <h1> tags, which is what allows me
to determine each title's length and then separate the title and text into
their own query fields. Thus, when I do a mail merge, I can merge the title
into its own table and the text into its own table.

The problem is, when I perform the mail merge, the query field that contains
the article text (via the MID function) - [AuthorBody] - is truncated to
255 characters.

If I substitute the query field - [AuthorBody] - with the query field for
the memo field in the underlying table - [ArticleBody] - then the mail merge
works fine and all characters are included in the merge.

So it seems that a query field that directly references the underlying memo
field - [ArticleBody] - is not truncated in a mail merge. But any query
field that indirectly references [ArticleBody], via an expression, is
truncated to 255 characters.

Here's the query itself:

SELECT Articles.ArticleID, Articles.ArticleBody, Mid([ArticleBody],[Title
Length]+10,15000) AS AuthorBody, Mid([ArticleBody],5,[Title Length]) AS
Title, InStr([ArticleBody],"/") AS [Length to /], ([Length to /]-6) AS
[Title Length], InStr([ArticleBody],"<br><br>") AS [Length to br], [Length
to br]+8 AS BodyTrim, InStr([Caption],"<br>") AS [Caption Length to br],
Mid([ArticleBody],[BodyTrim],200) AS Caption, IIf([Caption Length to
br]=0,Left([Caption],200),Left([Caption],[Caption Length to br]-1)) AS
[Caption No Tags], Articles.URL_Prefix, [Title] AS [Title Echo],
replace([Title Echo]," ",",") AS [Title to meta]
FROM Articles
ORDER BY Articles.ArticleID;

As you can see, I'm not using a GROUP BY clause which seems to be the source
of some truncation problems.

I've searched high and low for an answer to this problem and can find no
work around. Any help is greatly appreciated
Feb 8 '06 #1
0 2536

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Bishman | last post: by
1 post views Thread by =?Utf-8?B?QmFkaXM=?= | last post: by
2 posts views Thread by zazu | last post: by
3 posts views Thread by KimberlyM | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.