Union All query truncating

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in 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.

"Ken Snell (MVP)" wrote:

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"
wrote in message
news 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?

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


"Ken Snell (MVP)" wrote in 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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in 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.

"Ken Snell (MVP)" wrote:

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"
wrote in message
news 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?

Thank you, I will make that change. Hope you have a safe trip.

"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in 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.

"Ken Snell (MVP)" wrote:

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"
wrote in message
news 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?

The only grouping on the report is the date field.

"Ken Snell (MVP)" wrote:

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


"Ken Snell (MVP)" wrote in 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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in 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.

"Ken Snell (MVP)" wrote:

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"
wrote in message
news 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?

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"
wrote in message
...
The only grouping on the report is the date field.

"Ken Snell (MVP)" wrote:

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


"Ken Snell (MVP)" wrote in 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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in 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.

"Ken Snell (MVP)" wrote:

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"
wrote in
message
news 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?

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?




"Ken Snell (MVP)" wrote:

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"
wrote in message
...
The only grouping on the report is the date field.

"Ken Snell (MVP)" wrote:

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


"Ken Snell (MVP)" wrote in 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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in 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.

"Ken Snell (MVP)" wrote:

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"
wrote in
message
news 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?

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"
wrote in message
...
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?




"Ken Snell (MVP)" wrote:

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"
wrote in message
...
The only grouping on the report is the date field.

"Ken Snell (MVP)" wrote:

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


"Ken Snell (MVP)" wrote in 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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in
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.

"Ken Snell (MVP)" wrote:

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"
wrote in
message
news 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?

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)" wrote in message
...
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"
wrote in message
...
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?




"Ken Snell (MVP)" wrote:

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"
wrote in message
...
The only grouping on the report is the date field.

"Ken Snell (MVP)" wrote:

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


"Ken Snell (MVP)" wrote in 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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in
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.

"Ken Snell (MVP)" wrote:

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"
wrote in
message
news 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?

This issue now is documented on Allen Browne's site:
http://allenbrowne.com/ser-63.html

--

Ken Snell
MS ACCESS MVP


"Ken Snell (MVP)" wrote in message
...
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)" wrote in message
...
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"
wrote in message
...
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?




"Ken Snell (MVP)" wrote:

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"
wrote in message
...
The only grouping on the report is the date field.

"Ken Snell (MVP)" wrote:

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


"Ken Snell (MVP)" wrote in
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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in message
...
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.



"Ken Snell (MVP)" wrote:

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"
wrote in
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.

"Ken Snell (MVP)" wrote:

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"
wrote in
message
news 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?