Query - MID function & Mail Merge

[Phil C.]

Hi,

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.

 

[John Nurick]

Hi Phil,

You're right. If you use calculated fields in a query to format or
extract stuff or assemble it into strings, and then export the query,
the calculated fields are treated as Text data type - with a maximum
length of 255 characters.

The offical work-round is to change the query so it appends its data to
a temporary table (with Memo fields where needed) and then to export the
data directly from the table.

Hi,

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.

 

[Phil C.]

Thanks so much, John. Your advice worked like a charm!

Hi Phil,

You're right. If you use calculated fields in a query to format or
extract stuff or assemble it into strings, and then export the query,
the calculated fields are treated as Text data type - with a maximum
length of 255 characters.

The offical work-round is to change the query so it appends its data to
a temporary table (with Memo fields where needed) and then to export the
data directly from the table.

Hi,

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.