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

 

[Douglas J Steele]

As soon as you use a Memo field in a GROUP BY, it gets truncated. There's no
way around that.

 

[Phil C.]

Hi,

Thanks for your response.

As I look at the query pasted below, I'm not using a "Group By" which I
think is why the query field that directly references the memo field (i.e
[ArticleBody]) in the "Articles" table is not truncated when I do a merge.
Only the [AuthorBody] query field, which references the [ArticleBody] field
via an expression, is truncated on merge.

Perhaps I don't fully understand what you mean by "Group By." My
understanding is that "Group By" is a function used to group certain fields
together on a MS Access report. I'm doing a mail merge in Word 2000.

If I insert the field [ArticleBody] into my Word document (that's the query
field that directly references the memo field in the "Articles" table), the
merge works fine and all characters are merged from that field. If I insert
the field [AuthorBody] into my Word document (that's the field that
indirectly references the [ArticleBody] field via an expression), only the
the first 255 characters are merged.

Forgive me if I'm being obtuse but I don't see how the "Group By" function
even enters into my situation.

Again, thanks for your response - any further help is greatly appreciated.

As soon as you use a Memo field in a GROUP BY, it gets truncated. There's no
way around that.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

 

[Douglas J. Steele]

Sorry, my typo.

I meant to say that sorting a memo field causes truncation as well, but
you're not sorting on the memo field itself, are you?

However, I believe any of the string functions (Left, Mid, etc.) will
truncate in a query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

Thanks for your response.

As I look at the query pasted below, I'm not using a "Group By" which I
think is why the query field that directly references the memo field (i.e
[ArticleBody]) in the "Articles" table is not truncated when I do a merge.
Only the [AuthorBody] query field, which references the [ArticleBody]
field
via an expression, is truncated on merge.

Perhaps I don't fully understand what you mean by "Group By." My
understanding is that "Group By" is a function used to group certain
fields
together on a MS Access report. I'm doing a mail merge in Word 2000.

If I insert the field [ArticleBody] into my Word document (that's the
query
field that directly references the memo field in the "Articles" table),
the
merge works fine and all characters are merged from that field. If I
insert
the field [AuthorBody] into my Word document (that's the field that
indirectly references the [ArticleBody] field via an expression), only the
the first 255 characters are merged.

Forgive me if I'm being obtuse but I don't see how the "Group By" function
even enters into my situation.

Again, thanks for your response - any further help is greatly appreciated.

As soon as you use a Memo field in a GROUP BY, it gets truncated. There's no
way around that.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.]

No, I'm not sorting on a memo field but, you are correct; it turns out that
any type of calculated field (Left, Mid, etc.) automatically truncates text
to 255 characters.

The work around, it turns out, is pretty simple:

I turned my Select Query into a "Make Table" Query and named the resulting
table "ArticleMerge." Then I erased all the records in the "ArticleMerge"
table and set the data type for the [AuthorBody] field to "memo."

Then I turned my "Make Table" Query into an Append Query and chose my new,
empty table ("ArticleMerge") as the destination. This created all new
records with the [AuthorBody] field containing all the characters not just
the first 255.

Now, instead of using the query as my mail merge source, I use the
"ArticleMerge" table.

Much thanks owed to "John Nurick" in the Ms Access "queries" forum for his
help.

Thanks for your timely responses, Douglas; I really appreciate it.

Sorry, my typo.

I meant to say that sorting a memo field causes truncation as well, but
you're not sorting on the memo field itself, are you?

However, I believe any of the string functions (Left, Mid, etc.) will
truncate in a query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

Thanks for your response.

As I look at the query pasted below, I'm not using a "Group By" which I
think is why the query field that directly references the memo field (i.e
[ArticleBody]) in the "Articles" table is not truncated when I do a merge.
Only the [AuthorBody] query field, which references the [ArticleBody]
field
via an expression, is truncated on merge.

Perhaps I don't fully understand what you mean by "Group By." My
understanding is that "Group By" is a function used to group certain
fields
together on a MS Access report. I'm doing a mail merge in Word 2000.

If I insert the field [ArticleBody] into my Word document (that's the
query
field that directly references the memo field in the "Articles" table),
the
merge works fine and all characters are merged from that field. If I
insert
the field [AuthorBody] into my Word document (that's the field that
indirectly references the [ArticleBody] field via an expression), only the
the first 255 characters are merged.

Forgive me if I'm being obtuse but I don't see how the "Group By" function
even enters into my situation.

Again, thanks for your response - any further help is greatly appreciated.

As soon as you use a Memo field in a GROUP BY, it gets truncated.

There's
no

way around that.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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