Scott, you have found another example of string truncation in ACCESS.
Thanks for identifying this issue.
The issue that you're experiencing is caused by the use of a calculated
expression in a query that concatenates various text and memo data type
fields from a table into one long string. Then you use a UNION ALL query
to combine various subqueries, many of which include variations on this
calculated expression. In this situation, the resulting concatenated
string is truncated to 255 characters, even though you're not using any
GROUP BY statements or other situations known to cause truncation.
However, because the UNION ALL query does not include a standalone memo
data type field in the same position as the calculated field in any of the
subqueries, Jet apparently is defaulting to treat the calculated
expression as a Text data type, thereby limiting the concatentated strings
to 255 characters -- what surprised many of us MVPs is that Jet is doing
this even though you're using a Memo data type field as one of the fields
being concatenated in the calculated expressions.
The workaround for this issue has been identified by Sylvain Lafontaine
(MVP - Technologies Virtual-PC), who kindly provided the workaround to me
for testing and verification. I have confirmed the workaround and have
sent you a database (by private email) that shows the solution.
Sylvain's workaround involves adding one more subquery to the UNION ALL
query, where this subquery uses a Memo field as the correlated field in
the output where the other subqueries have the calculated field, and then
using a WHERE clause that will always be FALSE in its test so that no
additional records are introduced into the query's output.
For example, suppose this were your original SQL statement:
SELECT TextField1 AS F1,
TextField2 & TextField3 & MemoField4 AS F2
FROM Table1
UNION ALL
SELECT TextField11 AS F11,
TextField12 & TextField13 & MemoField14 AS F12
FROM Table11;
The modification that would prevent truncation of the "F2" and "F12"
fields (which show in the output records as field F2) would be this:
SELECT TextField1 AS F1,
MemoField4 AS F2
FROM Table1
WHERE 1 = 0
UNION ALL
SELECT TextField1 AS F1,
TextField2 & TextField3 & MemoField4 AS F2
FROM Table1
UNION ALL
SELECT TextField11 AS F11,
TextField12 & TextField13 & MemoField14 AS F12
FROM Table11;
The first query above will show truncated string in F2 field that is
output; the second query will not show truncation.
--
Ken Snell
<MS ACCESS MVP>
Ken Snell (MVP) said:
I'm out of ideas at the moment. Would you be willing to email me a copy
of the database (zipped in a file), with instructions for which query to
look at, and let me see the data and setup firsthand? That may yield a
solution.
You can find an email address for me at this site:
www.accessmvp.com/KDSnell
--
Ken Snell
<MS ACCESS MVP>
"Scott Whetsell, A.S. - WVSP"
The truncation occurs if I just run the query, without calling it from a
report. I tried making a new database and created the basic fields
without
formatting, and it is still truncating on the query. Is there perhaps
another way I should be doing this?
:
Any grouping can cause the truncation, regardless of which field is
being
grouped. To see if that is the problem, make a copy of the report,
delete
the grouping by date in the report, and run that new copy of the
report. Do
you still see truncation?
--
Ken Snell
<MS ACCESS MVP>
"Scott Whetsell, A.S. - WVSP"
The only grouping on the report is the date field.
:
Just a thought -- is the report's Sorting & Grouping property doing
any
grouping on a field or calculated field that contains the memo
field's
values? If yes, that may be the source of the truncation.
--
Ken Snell
<MS ACCESS MVP>
message
You're using this comparison in each of the queries:
<>Null
This will not give you a proper result. You must use either
Is Not Null
or
IsNull(FieldName) = False
I doubt that this has any affect on the truncation -- I still
don't see
anything in the query that should be causing that -- but wanted to
point
out how this should be changed.
I'm going to be out of town for the next four days, and won't be
able
to
reply until I return; sorry.
--
Ken Snell
<MS ACCESS MVP>
"Scott Whetsell, A.S. - WVSP"
My original SQL statement is:
==============
SELECT
[DateRecv] As fldDate,
Format([TimeRecv],"Short Time") As fldTime,
"PS" As fldFrom,
"KQB568" As fldTo,
UCase([EventCode] & Chr(13) & Chr(10) & "LOCATION: " &
[Location] &
Chr(13)
& Chr(10) & ("COMPL: " + [ComplName] & (" -- " + [ComplPhone])
&
Chr(13) &
Chr(10)) & (" " + [ComplLocation] & Chr(13) & Chr(10)) &
("9-1-1
Transfer From: " + [911From] & Chr(13) & Chr(10)) & ("Call
Details:
" +
[CallDesc] & Chr(13) & Chr(10)) & ("PRIMARY UNIT: " +
[OfcrPri] &
Chr(13)
& Chr(10)) & ("ASSISTING UNITS: " + [AsstUnits] & Chr(13) &
Chr(10))
&
("NOTES: " + [Notes] & Chr(13) & Chr(10))) As fldParticulars,
"*" As fldCD,
[User] As fldSN,
Right([tbl_CFS.CCNo],6) As fldCCNo
FROM [qry_CFS]
WHERE ([Location] <>Null) AND ([DateRecv] = [Enter Date:])
UNION ALL
SELECT
[CE_Date] As fldDate,
Format([CE_Time],"Short Time") as fldTime,
[CE_Unit] as fldFrom,
"KQB568" as fldTo,
UCase([CE_Event]) as fldParticulars,
"DR" as fldCD,
[CE_User] as fldSN,
Right([CE_CCNo],6) as fldCCNo
FROM [tbl_CallEvents]
WHERE ([CE_Event] <> Null) AND ([CE_Date] = [Enter Date:])
UNION ALL
SELECT
[S19_TSD] As fldDate,
Format([S19_TSI],"Short Time") As fldTime,
ParseText([S19_TSU],0," -- ") As fldFrom,
"KQB568" As fldTo,
UCase("SIG19 - TRAFFIC STOP" & Chr(13) & Chr(10) & "LOCATION: "
&
[S19_TSL] & Chr(13) & Chr(10) & [S19_LIC] & " " & [S19_VCO] & " "
&
[S19_VMA]
& " " & [S19_VMO]) As fldParticulars,
"DR" As fldCD,
[S19_User] As fldSN,
Right([S19_CCNo],6) As fldCCNo
FROM [tbl_Sig19]
WHERE ([S19_TSL] <> Null) AND ([S19_TSD] = [Enter Date:])
UNION ALL SELECT
[DL_Date] As fldDate,
[DL_Time] As fldTime,
"KQB568" As fldFrom,
"KQB568" As fldTo,
UCase([DL_Notes]) as fldParticulars,
"*" As fldCD,
[DL_User] as fldSN,
Null as fldCCNo
FROM [tbl_DispatchLog]
WHERE([DL_Notes] <> Null) AND ([DL_Date] = [Enter Date:]);
=============
Date and time fields are formatted as date/time.
Notes fields are formated as memo.
All others are text fields of varying legnths.
:
To help us, post the entire SQL statement of the UNION query
that is
truncating the output.
--
Ken Snell
<MS ACCESS MVP>
"Scott Whetsell, A.S. - WVSP"
The query is being used to fill a report, nothing else. The
only
calculation
is simply the merging of the fields into the fldParticulars
column.
Again, I
understand that it is not supposed to truncate without using
certain
criterion, however it does. The only memo field in the code
below
is
the
[Notes] field. If I use a simple select query with the code
exactly
as
below
it outputs the field completely. However when I change it to
a
union
query
as:
SQL CODE BELOW
UNION ALL
SQL CODE BELOW
Without changing anything in the code, it truncates to 255
characters,
and
the documenter identifies the size as 255, instead of 0 as in
the
simple
select query. I have went through my tables and verified that
all
formating
has been removed from the fields, but I get the same result.
:
Are you using this UNION ALL query for an export or to create
a
new
table?
If yes, the presence of any function in a calculated field
will
cause
truncation.
--
Ken Snell
<MS ACCESS MVP>
"Scott Whetsell, A.S. - WVSP"
message
I am using the UNION ALL command as part of my code. It may
not
be
right,
but I always use UNION ALL for my union queries.
:
Are you using a UNION query, or a UNION ALL query?
SELECT *
FROM Tablename
UNION
SELECT *
FROM T_Tablename;
The above will truncate any character strings longer than
255
characters
because Jet must identify and discard duplicate records.
SELECT *
FROM Tablename
UNION ALL
SELECT *
FROM T_Tablename;
The above will not truncate any character strings longer
than
255
characters
because Jet does not need to identify and discard
duplicate
records.
--
Ken Snell
<MS ACCESS MVP>
"Scott Whetsell, A.S. - WVSP"
message
I am using the following code in a select query and it
returns
the
complete
data without problem, however when I place it in a union
query,
it
truncates
the fldParticulars to 255 characters. Documenter
identifies
the
select
query
of having a field size of 0, but the union has a field
size
of
255.
======= SELECT QUERY CODE ========
SELECT tbl_CFS.DateRecv AS fldDate,
tbl_CFS.TimeRecv AS fldTime,
"PS" AS fldFrom,
"KQB568" AS fldTo,
"*" AS fldCD,
tbl_CFS.User AS fldSN,
tbl_CFS.CCNo AS fldCCNo,
Trim([EventCode] & Chr(13) & Chr(10) & "LOCATION: " &
[Location] &
Chr(13)
& Chr(10) & ("COMPL: "+[ComplName] & (" --
"+[ComplPhone])
&
Chr(13)
&
Chr(10)) & ("9-1-1 TRANSFER FROM: "+[911From] & Chr(13)
&
Chr(10))
&
("CALL
DETAILS: "+[CallDesc] & Chr(13) & Chr(10)) & ("PRIMARY
UNIT:
"+[OfcrPri]
& Chr(13) & Chr(10)) & ("ASSISTING UNITS: "+[AsstUnits]
&
Chr(13) &
Chr(10))
& ("NOTES: "+[Notes])) AS fldParticulars
FROM tbl_CFS
========== END CODE ==========
That code was placed in a new blank query, no grouping
or
sorting
applied.
Any suggestions?
