HI,
I'm having trouble with my SQL code truncating memo fields in my union query. Here is the code: - SELECT Projects.[11-14_Number] AS Expr1
-
, Input2012 AS 2012Input
-
, [Federal_9-12] AS Famt
-
, [Copy of 11-14 TIP Status Project List Report for all years Query].[Local_9-12] AS Lamt
-
, [Project Funding].Project_Funding AS F
-
, [Project Sponsor].Project_Sponsor AS S
-
, Projects.ProjectName as Name
-
, [FirstOfProject_Status_9-12] AS Status
-
FROM [Copy of 11-14 TIP Status Project List Report for all years Query]
-
WHERE Input2012=yes
-
UNION ALL SELECT Projects.[11-14_Number] AS Expr1
-
, Input2011 AS 2011Input
-
, [Copy of 11-14 TIP Status Project List Report for all years Query].[9-12_Federal_2011] AS Famt
-
, [9-12_Local_2011] AS Lamt
-
, [Project Funding].Project_Funding AS F
-
, [Project Sponsor].Project_Sponsor AS S
-
, Projects.ProjectName as Name
-
, [FirstOfProject_Status_9_11] AS Status
-
FROM [Copy of 11-14 TIP Status Project List Report for all years Query]
-
WHERE Input2011=yes;
The project status field gets truncated.
1 2058 zmbd 5,501
Expert Mod 4TB
Moved your thread to the Access forum... SQL and Access-SQL can be very different beasties :)
You really should try to use shorter names for queries, tables, fields, etc...
You should not use any of the reserved words such as "Name" for a field name, nor should you use any of the other non-alphanumerics as they can and will cause you issues: Access 2007 reserved words and symbols
You have not indicated which field is(are) your memo field(s).
You have not indicated which version of Access you are using.
You have not indicated what it is that you are trying to do with the query; however, the following may give you the reason: Allen Browne - Truncation of Memo fields
In Access tables, Text fields are limited to 255 characters, but Memo fields can handle 64,000 characters (about 8 pages of single-spaced text) - even more programmatically. So why do memo fields sometimes get cut off? Queries
Access truncates the memo if you ask it to process the data based on the memo: aggregating, de-duplicating, formatting, and so on.
Here are the most common causes, and how to avoid them:
(...) UNION query: - A UNION query combines values from different tables, and de-duplicates them. This means a comparing the memo field, resulting in truncation.
- In SQL View, replace UNION with UNION ALL.
(...)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Paradigm |
last post by:
I am using Access 2K as a front end to a MYSQL database.
I am trying to run a Union query on the MYSQL database. The query is (much
simplified)
SELECT as ID from faxdata UNION
SELECT as ID ...
|
by: s_gregory |
last post by:
The mdb is considerable size 70 +- mb. A complex union query was
working well, but when an additional union select... was added into
the query, selecting identical fields from a different source,...
|
by: Richard Coutts |
last post by:
I have two select queries, "qryJobSpecsAlwaysList" and
"qryJobSpecsBySchedCat," that list some fields, including a Memo field
called "Description" that can contain text values that can be 500...
|
by: marco |
last post by:
Dear List,
as it seems, MS SQL as used in Access does not allow a select INTO
within a UNION query. Also, it seems that a UNION query can not be used
as a subquery.
Maybe my (simplified)...
|
by: BillCo |
last post by:
I'm having a problem with a union query, two simple queries joined with
a union statement. It's created in code based on parameters. Users were
noticing some inconsistant data and when I analysed...
|
by: SAKTHIVEL |
last post by:
Hi,
Im developing applications using Visual Basic and MS-Access 2003. Union query solves my specific problems. But I unable to call this query from visual basic form like other queries.
The query...
|
by: ebasshead |
last post by:
Hi Everyone
I have four queries that Ive joined in a union query and want to add a date criteria ei between and including date A and date B. Ive tried a few things but getting errors. Can someone...
|
by: sierra7 |
last post by:
It seems Access 2010 is associating an 'input mask' or field type with a combo box when a form is opened, even though there is no Format setting on the control.
I have a form which has been...
|
by: bknabl |
last post by:
I'm very new to creating union queries. I understand the purpose but not how to properly implement it for my purpose. So far I've created to perfectly fine crosstab queries. One shows input on a date...
|
by: doma23 |
last post by:
Hi, my problem is next:
I have existing table like this:
01/01/2010 02/01/2010 03/01/2010
code1 val11 val12 val13
code2 val21 val22 ...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |