Truncated text in Union Query

G

Guest

I have been searching thru the postings for a solution and cannot believe
there isn't one out there. I understand that Union Queries will truncate
text in a memo field, but I am still hoping for a solution. I have a
database defined by Projects. Each Project may contain restrictions from two
different sources and therefore reside in two tables. I need to be able to
join these restrcitions and the best way I knew how was to write my SQL
statement like this:

SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsCS]
UNION SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsDeed]
ORDER BY [ProjID], [Index]

Is there another way to write this statement so that the results will not be
truncated? Thanks for any help!
 
G

Guest

Doug,

Sorry I should have mentioned that I tried UNION ALL SELECT and got nowhere.

Debbi

Douglas J. Steele said:
Try using UNION ALL instead of just UNION.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



DBAM said:
I have been searching thru the postings for a solution and cannot believe
there isn't one out there. I understand that Union Queries will truncate
text in a memo field, but I am still hoping for a solution. I have a
database defined by Projects. Each Project may contain restrictions from
two
different sources and therefore reside in two tables. I need to be able
to
join these restrcitions and the best way I knew how was to write my SQL
statement like this:

SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsCS]
UNION SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsDeed]
ORDER BY [ProjID], [Index]

Is there another way to write this statement so that the results will not
be
truncated? Thanks for any help!
Click to expand...
Click to expand...
 
V

Van T. Dinh

Post the SQL of the 2 Source Queries also.

--
HTH
Van T. Dinh
MVP (Access)




DBAM said:
Doug,

Sorry I should have mentioned that I tried UNION ALL SELECT and got nowhere.

Debbi

Douglas J. Steele said:
Try using UNION ALL instead of just UNION.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



DBAM said:
I have been searching thru the postings for a solution and cannot believe
there isn't one out there. I understand that Union Queries will truncate
text in a memo field, but I am still hoping for a solution. I have a
database defined by Projects. Each Project may contain restrictions from
two
different sources and therefore reside in two tables. I need to be able
to
join these restrcitions and the best way I knew how was to write my SQL
statement like this:

SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsCS]
UNION SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsDeed]
ORDER BY [ProjID], [Index]

Is there another way to write this statement so that the results will not
be
truncated? Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thank you for trying to help with my troubles!

The first query calls up any Restrictions from one location (the Deeds
table) and is written as follows:

SELECT tblProjects.PROJID, tblDeeds.DEEDID, [BOOK] & "/" & [PAGE] AS
[INDEX], tblDeeds.RESTRCOPY, tblDeeds.RESTRICTIONS
FROM tblProjects INNER JOIN tblDeeds ON tblProjects.MRID = tblDeeds.MRID
WHERE (((tblDeeds.RESTRCOPY)=-1));

In this query, the field named RESTRICTIONS is the memo field.

The second query calls up any Restrictions from a second location (the
Indexes table) and is written as follows:

SELECT tblProjects.PROJID, tblDeeds.DEEDID, tblIndexes.INDEX,
tblIndexes.RESTREAS, tblIndexes.NOTES AS RESTRICTIONS
FROM (tblProjects INNER JOIN tblDeeds ON tblProjects.MRID = tblDeeds.MRID)
INNER JOIN tblIndexes ON tblDeeds.DEEDID = tblIndexes.DEEDID
WHERE (((tblIndexes.RESTREAS)=-1));

In this query, the NOTES field is the memo field, but displayed as
RESTRICTIONS as well.

I certainly appreciate any assistance to this troubleshooting. Thank you so
much!

Debbi




Van T. Dinh said:
Post the SQL of the 2 Source Queries also.

--
HTH
Van T. Dinh
MVP (Access)




DBAM said:
Doug,

Sorry I should have mentioned that I tried UNION ALL SELECT and got nowhere.

Debbi

Douglas J. Steele said:
Try using UNION ALL instead of just UNION.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have been searching thru the postings for a solution and cannot believe
there isn't one out there. I understand that Union Queries will truncate
text in a memo field, but I am still hoping for a solution. I have a
database defined by Projects. Each Project may contain restrictions from
two
different sources and therefore reside in two tables. I need to be able
to
join these restrcitions and the best way I knew how was to write my SQL
statement like this:

SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsCS]
UNION SELECT [ProjID], [Index], [Restrictions]
FROM [qryRestrictionsDeed]
ORDER BY [ProjID], [Index]

Is there another way to write this statement so that the results will not
be
truncated? Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
V

Van T. Dinh

The alias in the second part of the UNION ALL SQL is not necessary. The
column will take the name used in the first part.

However, this should not be the cause of truncation. I tested with aliases
and inner join and the memo fields are still not truncated.

My feeling is that we are using different versions of JET. I am using
AccessXP but the JET is JET500.dll (came with Windows XP) version
5.1.2600.0.

Suggest you update / patch your OS and Access with all available service
patches / service releases available from Microsoft Web site.

Failing that, the alternative is to set up the Union Query without the Memo
Fields in the selection lists and retrieve the Memo Field values separately
by code when you need them.
 
G

Guest

Hi again!

We are using the same version of JET (JET500.dll) - Did find one update and
installed it to no avail. I appreciate all you've done and must beg for one
more favor.

I would need these memo fields to appear on a report. The sub-report is
based on this union query that is stumping me.

Being a complete newbie writing code, I have no idea how to retrieve the
memo fields by code. Might you be able to give me some insight/direction? I
would sure appreciate it! Thank you!

Debbi

Van T. Dinh said:
The alias in the second part of the UNION ALL SQL is not necessary. The
column will take the name used in the first part.

However, this should not be the cause of truncation. I tested with aliases
and inner join and the memo fields are still not truncated.

My feeling is that we are using different versions of JET. I am using
AccessXP but the JET is JET500.dll (came with Windows XP) version
5.1.2600.0.

Suggest you update / patch your OS and Access with all available service
patches / service releases available from Microsoft Web site.

Failing that, the alternative is to set up the Union Query without the Memo
Fields in the selection lists and retrieve the Memo Field values separately
by code when you need them.

--
HTH
Van T. Dinh
MVP (Access)



DBAM said:
Thank you for trying to help with my troubles!

The first query calls up any Restrictions from one location (the Deeds
table) and is written as follows:

SELECT tblProjects.PROJID, tblDeeds.DEEDID, [BOOK] & "/" & [PAGE] AS
[INDEX], tblDeeds.RESTRCOPY, tblDeeds.RESTRICTIONS
FROM tblProjects INNER JOIN tblDeeds ON tblProjects.MRID = tblDeeds.MRID
WHERE (((tblDeeds.RESTRCOPY)=-1));

In this query, the field named RESTRICTIONS is the memo field.

The second query calls up any Restrictions from a second location (the
Indexes table) and is written as follows:

SELECT tblProjects.PROJID, tblDeeds.DEEDID, tblIndexes.INDEX,
tblIndexes.RESTREAS, tblIndexes.NOTES AS RESTRICTIONS
FROM (tblProjects INNER JOIN tblDeeds ON tblProjects.MRID = tblDeeds.MRID)
INNER JOIN tblIndexes ON tblDeeds.DEEDID = tblIndexes.DEEDID
WHERE (((tblIndexes.RESTREAS)=-1));

In this query, the NOTES field is the memo field, but displayed as
RESTRICTIONS as well.

I certainly appreciate any assistance to this troubleshooting. Thank you so
much!

Debbi
Click to expand...
Click to expand...
 
V

Van T. Dinh

Have you tested the Query by itself, i.e. not through the Report /
SubReport?

The Report / SubReport may be the problem and not the Query.

If the database file is small, compact it then WinZip it and post it to a
Web site where others can download and post the link here and other
respondents & I will have a look at the database. Unfortunately, I got a
few commitments at present and won't be able to look into your database
quickly.
 
G

Guest

Both original queries run great, it's just the Union Query that truncates.
Thanks for looking into this for me. I'll keep plugging along...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UNION query results in memo column being truncated 2
UNION query truncating MEMO column ! 10
Union query 5
SUM in a UNION query 2
Argument Error in Union Query 9
Losing Records Between Union Query and Select Query 3
Repost: Union query truncating field 1
Union Query 6

Top