Query output truncates at 255 characters

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I know it's supposed to do that, but is there any way
around the 255 character limit for a field that
concatenates several text fields and a date field with
some extra text thrown in?

I'm assembling a "sentence" of html to attach to the front
of an html document that becomes an email later, and it
works beautifully as long as the "sentence" doesn't exceed
255 characters. Unfortunately, it often does, and it's
only going to get worse as the users figure out a billion
other uses they can put this to... The field goes like
this:

NoteText: "Thank you for your inquiry" & (IIf([Inquiry
Short] Is Null,Null," about " & [Inquiry Short])) & " on "
& [DateOfInquiry] & " to the Helpline. </p> Name: " &
[Fullname] & "<br> Organization: " & [Organization]
& "<br> Date of Inquiry: " & [DateOfInquiry] & "</p>"

and the query is just a simple select query based on
another query that picks out who needs to be sent to for
each type of caller they have. I'm actually doing this
from a bit of code, not the query grid, but I switched it
over to play with... I know how to do it with a temporary
table, but if there were a way to do it in a regular
select query that would be perfect. Any ideas?
 
The query truncates the text if it contains DISTINCT or GROUP BY or
something that involves comparing the values on each row.

To avoid this, use First in the Group By row of your Totals query, or drop
the Unique Values property of the query.

(Another cause is if the text box on your form/report has something in the
Format property, but that sounds unlikely in your scenario.)
 
Uh.. no it's not grouped or DISTINCT, just a plain old
SELECT query with nothing else special about it. I've
tested it on another table, and just selecting a text
field or concatenating text fields returns text (255),
selecting a memo field returns a memo-sized field, and
concatenating text and memo (as in Truncate1:[Text1]&
[Memo2]) returns a text field (255). I looked at the
properties and unique values and unique records are both
set to "no". There is no form or report involved at all,
just text. It does the same thing whether I look at the
query directly, send it to a text file, or (as it's
intended to do) write it to a new email message. All
truncated. I think what you're thinking of is that a memo
field sent through a select query with DISTINCT (unique
values = yes) or GROUP BY will truncate, where it wouldn't
with a plain SELECT, but in this case it's an issue of it
not ever having been a memo field to begin with. I'm
assembling it from a few text and date fields, and some
extra text to string them together, and what I'd really
like is for it to just be able to run it all together as a
single field, memo size. But it defaults to text (255).
That's the problem. It would be really nice if there were
a switch to add to a field name that would set the size --
you do that when you CREATE TABLE.

I'm working around it by running an update query first, to
populate a memo field in one of the tables with the entire
string, and then sending it through the SELECT query --
that way it retains the memo size. I just don't like
running two steps (and maintaining duplicate data) when
logically one shouldn't have to. It's just irritating,
that's all.

Thanks anyway.

Heather

-----Original Message-----
The query truncates the text if it contains DISTINCT or GROUP BY or
something that involves comparing the values on each row.

To avoid this, use First in the Group By row of your Totals query, or drop
the Unique Values property of the query.

(Another cause is if the text box on your form/report has something in the
Format property, but that sounds unlikely in your scenario.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I know it's supposed to do that, but is there any way
around the 255 character limit for a field that
concatenates several text fields and a date field with
some extra text thrown in?

I'm assembling a "sentence" of html to attach to the front
of an html document that becomes an email later, and it
works beautifully as long as the "sentence" doesn't exceed
255 characters. Unfortunately, it often does, and it's
only going to get worse as the users figure out a billion
other uses they can put this to... The field goes like
this:

NoteText: "Thank you for your inquiry" & (IIf([Inquiry
Short] Is Null,Null," about " & [Inquiry Short])) & " on "
& [DateOfInquiry] & " to the Helpline. </p> Name: " &
[Fullname] & "<br> Organization: " & [Organization]
& "<br> Date of Inquiry: " & [DateOfInquiry] & "</p>"

and the query is just a simple select query based on
another query that picks out who needs to be sent to for
each type of caller they have. I'm actually doing this
from a bit of code, not the query grid, but I switched it
over to play with... I know how to do it with a temporary
table, but if there were a way to do it in a regular
select query that would be perfect. Any ideas?
Click to expand...


.
Click to expand...
 
When you run Make Table query, Access creates a Text field, I presume?
That's probably the point at which the text is truncated.

Would it be possible to create the table with the structure you desire
first, so you get a Memo field? Then use an Append query to add the date
instead of a Make Table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Heather said:
Uh.. no it's not grouped or DISTINCT, just a plain old
SELECT query with nothing else special about it. I've
tested it on another table, and just selecting a text
field or concatenating text fields returns text (255),
selecting a memo field returns a memo-sized field, and
concatenating text and memo (as in Truncate1:[Text1]&
[Memo2]) returns a text field (255). I looked at the
properties and unique values and unique records are both
set to "no". There is no form or report involved at all,
just text. It does the same thing whether I look at the
query directly, send it to a text file, or (as it's
intended to do) write it to a new email message. All
truncated. I think what you're thinking of is that a memo
field sent through a select query with DISTINCT (unique
values = yes) or GROUP BY will truncate, where it wouldn't
with a plain SELECT, but in this case it's an issue of it
not ever having been a memo field to begin with. I'm
assembling it from a few text and date fields, and some
extra text to string them together, and what I'd really
like is for it to just be able to run it all together as a
single field, memo size. But it defaults to text (255).
That's the problem. It would be really nice if there were
a switch to add to a field name that would set the size --
you do that when you CREATE TABLE.

I'm working around it by running an update query first, to
populate a memo field in one of the tables with the entire
string, and then sending it through the SELECT query --
that way it retains the memo size. I just don't like
running two steps (and maintaining duplicate data) when
logically one shouldn't have to. It's just irritating,
that's all.

Thanks anyway.

Heather

-----Original Message-----
The query truncates the text if it contains DISTINCT or GROUP BY or
something that involves comparing the values on each row.

To avoid this, use First in the Group By row of your Totals query, or drop
the Unique Values property of the query.

(Another cause is if the text box on your form/report has something in the
Format property, but that sounds unlikely in your scenario.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I know it's supposed to do that, but is there any way
around the 255 character limit for a field that
concatenates several text fields and a date field with
some extra text thrown in?

I'm assembling a "sentence" of html to attach to the front
of an html document that becomes an email later, and it
works beautifully as long as the "sentence" doesn't exceed
255 characters. Unfortunately, it often does, and it's
only going to get worse as the users figure out a billion
other uses they can put this to... The field goes like
this:

NoteText: "Thank you for your inquiry" & (IIf([Inquiry
Short] Is Null,Null," about " & [Inquiry Short])) & " on "
& [DateOfInquiry] & " to the Helpline. </p> Name: " &
[Fullname] & "<br> Organization: " & [Organization]
& "<br> Date of Inquiry: " & [DateOfInquiry] & "</p>"

and the query is just a simple select query based on
another query that picks out who needs to be sent to for
each type of caller they have. I'm actually doing this
from a bit of code, not the query grid, but I switched it
over to play with... I know how to do it with a temporary
table, but if there were a way to do it in a regular
select query that would be perfect. Any ideas?
Click to expand...


.
Click to expand...
Click to expand...
 
Also if any type of formatting is performed on the memo data Access wil
truncate the data to 255 characters.

RD
 
If I did it as a Make Table then yes it would be a 255-
character text field, just like it is with the select
query -- if you "create" a table (code, not query grid)
you can make it anything you want, and yes, populate it
after the fact with whatever you want. My point is that I
don't want to make a table, or create a table or do
anything of the sort. I just want to select and have it
return everything. That would be ideal. Anything else
creates redundancies, or, my favorite, temporary tables.
ugh. Apparently it's not an option, but thanks anyway.

-----Original Message-----
When you run Make Table query, Access creates a Text field, I presume?
That's probably the point at which the text is truncated.

Would it be possible to create the table with the structure you desire
first, so you get a Memo field? Then use an Append query to add the date
instead of a Make Table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Uh.. no it's not grouped or DISTINCT, just a plain old
SELECT query with nothing else special about it. I've
tested it on another table, and just selecting a text
field or concatenating text fields returns text (255),
selecting a memo field returns a memo-sized field, and
concatenating text and memo (as in Truncate1:[Text1]&
[Memo2]) returns a text field (255). I looked at the
properties and unique values and unique records are both
set to "no". There is no form or report involved at all,
just text. It does the same thing whether I look at the
query directly, send it to a text file, or (as it's
intended to do) write it to a new email message. All
truncated. I think what you're thinking of is that a memo
field sent through a select query with DISTINCT (unique
values = yes) or GROUP BY will truncate, where it wouldn't
with a plain SELECT, but in this case it's an issue of it
not ever having been a memo field to begin with. I'm
assembling it from a few text and date fields, and some
extra text to string them together, and what I'd really
like is for it to just be able to run it all together as a
single field, memo size. But it defaults to text (255).
That's the problem. It would be really nice if there were
a switch to add to a field name that would set the size --
you do that when you CREATE TABLE.

I'm working around it by running an update query first, to
populate a memo field in one of the tables with the entire
string, and then sending it through the SELECT query --
that way it retains the memo size. I just don't like
running two steps (and maintaining duplicate data) when
logically one shouldn't have to. It's just irritating,
that's all.

Thanks anyway.

Heather

-----Original Message-----
The query truncates the text if it contains DISTINCT or GROUP BY or
something that involves comparing the values on each row.

To avoid this, use First in the Group By row of your Totals query, or drop
the Unique Values property of the query.

(Another cause is if the text box on your form/report
Click to expand...
has
something in the
Format property, but that sounds unlikely in your scenario.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Heather" <[email protected]> wrote
Click to expand...
in
message
I know it's supposed to do that, but is there any way
around the 255 character limit for a field that
concatenates several text fields and a date field with
some extra text thrown in?

I'm assembling a "sentence" of html to attach to the front
of an html document that becomes an email later, and it
works beautifully as long as the "sentence" doesn't exceed
255 characters. Unfortunately, it often does, and it's
only going to get worse as the users figure out a billion
other uses they can put this to... The field goes like
this:

NoteText: "Thank you for your inquiry" & (IIf ([Inquiry
Short] Is Null,Null," about " & [Inquiry Short])) & " on "
& [DateOfInquiry] & " to the Helpline. </p> Name: " &
[Fullname] & "<br> Organization: " & [Organization]
& "<br> Date of Inquiry: " & [DateOfInquiry] & "</p>"

and the query is just a simple select query based on
another query that picks out who needs to be sent to for
each type of caller they have. I'm actually doing this
from a bit of code, not the query grid, but I
Click to expand...
switched
it
over to play with... I know how to do it with a temporary
table, but if there were a way to do it in a regular
select query that would be perfect. Any ideas?


.
Click to expand...
Click to expand...


.
Click to expand...
 
There is no formatting.

The problem isn't that it started memo and got cut off,
but that it started as several concatenated text and date
fields, and Access apparently isn't clever enough to
realize that when you concatenate 8 text fields, each with
lengths set to 50 characters, that you have the potential
to return a field with more than 255 characters and
perhaps it should adjust accordingly. It would be nice,
but there you go.
-----Original Message-----
Also if any type of formatting is performed on the memo data Access will
truncate the data to 255 characters.

RDH



------------------------------------------------
Click to expand...
 
I'm not seeing that behavior.

Created a calculated field consisting of:
String(250, "A") & String(250,"B") & [Surname]
and it returns the while enchilada.
 
Heather, I see lots of replies trying to help you.

May I suggest that you post the text of both the SQL queries involved. PERHAPS
someone can then spot the source of the problem. I think that you may be
running into the 255 character limit that Access has on putting something into a
query cell.
 
Back
Top