Union Query Truncates Memo Field????
Jan 4, 2005
I created a Union Query for several linked Excel tables. Certain fields in the Excel table exceed 256 characters and Access (rightfully so) assigns these fields as "Memo". I have create a report based upon the Union Query; however, it will truncate the "Memo" fields to 255 (or 256 characters).
As a side note, if I create a report based upon a simple query using only one of the linked tables, it does not truncate the field.
Any suggestions on what maybe causing this truncation issue?
View Replies
ADVERTISEMENT
Aug 18, 2003
I ran into errors recently when I tried running a large UNION query (about 6,900 news articles) in a desktop Access database, that will eventually be migrated to SQL Server. I've got two tables, each having the same typed fields. I joined then through the following query:
SELECT ID,Name,Body FROM Table1 UNION SELECT ID,Name,Body FROM Table2;
The problem was that in the resultant table containing the conjoined records, one of the fields (Body, a MEMO field) copies only the first 250 characters or so, truncating the rest of the data.
I was thinking this might have something to do with telling Access how to type the data when copying it over. Is there a way to explicitly tell a query the data type of each field to be used so that the data can be copied over properly?
Thanks!
View 3 Replies
View Related
Nov 25, 2013
Two Solutions to address moving an Access Memo field into Excel when string has > 255 characters. All my 'reports' use Excel VBA (Access Reports are not used). The Excel reports can have 40,000 records. Speed to create the report can be an issue.
Describing 2 Solutions below to address moving Access memo fields with > 255 characters into Excel.After running this code
Code:
720 ObjXL.DisplayAlerts = False
ObjXL.Columns("X:X").Select
ObjXL.Selection.NumberFormat = "@" ' set column to Text
730 ObjXL.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsNutsAndBolts
The Comments column are limited to 255 characters. So, the CopyFromRecordset (recordsetvariable) creates the 255 character limitation.
The reason? The 255 character limit is because CopyFromRecordset sutomatically uses the Characters property of the Range object. The 255 limit would not be there if the Cell Value property is used to assign the string to that cell.
Dim sRx as String ' String Prescription
sRx = "String with > 255 characters ... you fill in the rest ...."
Cells(1, 1).Value = sRx ' Cell's Value property assignment can be very large
Solution 1:
The record set is still in memory. By using a loop, a cursor can start with record 1 (memo column) and assign that value to the Excel row / column using the .value as shown above. Basically, this moves one memo field at a time, record by record. e.g. Read First recordset in Access, copy to variable, assign value to Excel row/column Then move next on each Access and Excel.
Solution 2:
An Access Memo filed [RxNotes] can have up to 750 characters. Cut it apart into three new fields that end up out in the very right Excel columns AA, AB, AC.
Note1=Mid([RxNotes],1,250)
Note2=Mid([RxNotes],251,250)
Note3=Mid([RxNotes],501,250)
Then using Excel Object - Concat the cells back cell by cell...
X2=CONCATENATE(AA2,AB2,AC2))
Then delete the columns AA, AB, AC to hide the evidence..Neither solution is all that elequent. Read about this and by golly, it made a difference
ConcatComments = "'" & CommentString
Before using the CopyFromRecordset be sure to add a single quote in front of the large string.
Turns out the interface between Access and Excel look for this to prepare Excel immediately for the string to be a string, not something else. Some of my strings had weird print characters that kind of looked like Japenese characters. It seemed random, it always happened if the string was 255 or more characters (ramdonly, not always). The single quote doesn't show up in Excel, but got rid of all the noise.
View 5 Replies
View Related
Jan 10, 2005
When I export a report to Excel, a memmo field is truncated to 256 characters. If I export the query behind the report, the memmo field is exported correctly. Is there a way to export an Access 2003 report to Excel and maintain all of the data and report formating in memmo fields?
View 1 Replies
View Related
Oct 24, 2013
I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.
Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000
[code]...
View 1 Replies
View Related
Mar 8, 2006
Hi,
I have a query that I build using VBA based on some user input. One of the fields I pull out is a hyperlink to particular files on our local network, so the user can click the link and open the relevant file. My problem is that if the SELECT statement contains one (or more) UNIONs the hyperlinks no longer work. The query returns all the fields but the hyperlink field is just text of the form "display_text#link_address#".
I am using MS Access 2000. The UNION statements are required so I can search for multiple keywords in various fields within the table.
An example of the SQL query I generate is:
SELECT DocRef, DocTitle, DocAbstract, DocLink, DocAuthor, DocDate FROM TechDocs
WHERE DocType IN ('TechRep', 'Misc')
AND DocAbstract LIKE ('*drug*')
UNION
SELECT DocRef, DocTitle, DocAbstract, DocLink, DocAuthor, DocDate FROM TechDocs
WHERE DocType IN ('TechRep', 'Misc')
AND DocAbstract LIKE ('*release*');
If I do the query a different way (when I am searching for phrases, not keywords) the hyperlinks work fine. As you can imagine this is very frustrating! I have read that Access 2000 has some problems with UNIONs where it has to order the individual SELECT results before the union or something, but I can't work it out. :confused:
Any help is much appreciated.
View 1 Replies
View Related
Feb 16, 2007
I have a table called LLD Import Table with the following fields in it.
FileNumber, LandDescription, Qtr1, Sec1, Qtr2, Sec2, Qtr3, Sec3, Qtr4, Sec4, Qtr5, Sec5
Data Example:
123456, T43 R2 W5M, S, 6
123457, T43 R1 W5M, SE, 18, SW, 17
123456, T43 R1 W5M, E, 19, E, 30, SW, 29, E, 31, NE, 18
What I have done so far is to create a Union query to create a new record with the file and land description repeating for each row where there is quarter and section data with the following code:
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec1 AS Section, [LLD Import Table].Qtr1 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec1) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec2 AS Section, [LLD Import Table].Qtr2 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec2) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec3 AS Section, [LLD Import Table].Qtr3 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec3) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec4 AS Section, [LLD Import Table].Qtr4 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec4) Is Not Null));
UNION
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec5 AS Section, [LLD Import Table].Qtr5 AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec5) Is Not Null));
To give this result:
FileNumber, LandDescription, Quarter, Section
123456, T 43 R 1 W5M, NE, 18
123456, T 43 R 1 W5M, E, 19
123456, T 43 R 1 W5M, SW, 29
123456, T 43 R 1 W5M, E, 30
123456, T 43 R 1 W5M, E, 31
123456, T 43 R 2 W5M, S, 6
123457, T 43 R 1 W5M, SW, 17
123457, T 43 R 1 W5M, SE, 18
However the number of Quarters and Sections under a file changes, so next time I my table could have fields up to Qtr20 and Sec20 What I’d like to do is to create a function that will automatically change the # behind the field names (to replace the * in the example below) so that I don’t have to rewrite the Union Query each time. I’ve seen some code examples that can change the value, but don’t quite understand them enough to write one.
SELECT [LLD Import Table].[FileNumber], [LLD Import Table].[LandDescription], [LLD Import Table].Sec* AS Section, [LLD Import Table].Qtr* AS Quarter
FROM [LLD Import Table]
WHERE ((([LLD Import Table].Sec*) Is Not Null));
Your help would be GREATLY appreciated!
View 1 Replies
View Related
Jun 21, 2007
Hi everyone,
Please would someone be able to help me?
I have created a union query however, one of the columns, has not picked up the same format as it has in the tables. As in the tables it has this format
'00000'.
Please woud you be able to advise me how I can change the format on one of the 'columns' in my union query. As one column is 'numbers' and the other is 'text'. I need to change the number column so the format is '00000'.
Thank you in advance for your help.
Nats
View 5 Replies
View Related
Dec 11, 2013
Is there a way to hide a field in a union query? I need to keep the field in the SQL statement because I need to order by it. The field is "Rank," but I don't want it showing up.
View 3 Replies
View Related
Mar 18, 2014
The statement below is a snippet from a union query and is repeated 6 times within the SQL Statement for various reasons, I have to manually change this every month, again is there i tiny bit of code I can insert to replace the following so that it automatically runs the previous months data.
WHERE ArrivalDateTime >= 'February 1 2014'
View 5 Replies
View Related
May 31, 2005
Greetings All -
Let me say first I am a novice Access user, just trying to teach myself a few things to make my job easier. So take it easy on me with any feedback.
This is my situation. I am using a web based help desk ticketing software that has an Access DB. I have created a couple of queries and linked them together that seem to be working and giving me the data that I want with one exception.
I am tring to query a memo field, which is the description of a ticket, and the output is only a small portion of what is actually entered in to the table. Is there a way either in my query or report builder to make a change that will output all of the data from the description field?
A couple of things that I've tried on my own are to change the data type for that field from memo to text, however that that puts limits on the amount of data that can be entered in and causes the help desk software to function incorrectly. I've also turned on Can Grow and Can Shrink for the text box for the output in my form.
Any help you can offer would be greatly appreciated.
Thanks.
Jason
View 1 Replies
View Related
Dec 8, 2007
Hello,
I have Custormers table.
Name : Text
Address : Text
BDate : Date
Notes : Memo
Sometimes, I need to query Memo field.
I have a CForm which has a field named Search.
I want to query all Notes fields in Customers table via CForm!Search field.
Should I make a query like this? I use IN but I doesn't work.
SELECT Customers.Notes, Customers.Name, Customers.BDate
FROM Customers
WHERE (((([Forms!CForm!Search]) In (Customers.Notes)));
Thank you for your help,
Osman
View 1 Replies
View Related
Jul 20, 2006
The code pasted below creates a union query for a set of tables (J000171, J000174, J000178 etc) and stores the results of the query in a table called temp.
The first piece of code queries the ‘status’ field of a table rjobs for those records with a ‘status’ field of “Live”. Another field within this rjobs table, ‘JobID’, happens to be the name of a table where additional information relating to that job record is held eg. J000178 All of the tables selected in the query on rjobs are then included in the union query.
The second piece of code stores this information in a table called temp
I would like to be able to do 2 things with this;
1.add an additional field to the union query which holds the JobID field value from rjobs (or alternatively the table name from which the data originates eg J000178 etc as that is the same as the JobID file din rjobs)
2.create an option to clear the info in the temp table. Currently additional info is appended, so whenever the query is refreshed new data is simply added to old data. I would like to be able to clear that data where possible.
The union query is run from the on click of a command button on a simple form. Perhaps an additional button could be used to clear the records from the table temp.
Any ideas greatly appreciated.
Here is the existing code …
Option Compare Database
Option Explicit
Private Sub Command0_Click()
Dim db As Database
Dim rsRjobs As Recordset
Dim rsRapps As Recordset
Dim LengthofUnionSQL As Long
Dim sql As String
Dim UnionSQL As String
Set db = CurrentDb
Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot)
Do While Not rsRjobs.EOF
UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, from " & rsRjobs!jobID & " Union "
rsRjobs.MoveNext
Loop
'following two lines are to remove the trailing word Union from the string unionsql
LengthofUnionSQL = Len(UnionSQL)
UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7)
' Now variable Unionsql will hold the value something like
' Select ObjectID, SearchNo, DateSearched, Consultant from J000145
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146
' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147
MsgBox UnionSQL
Set db = CurrentDb
Dim rsUnionquery As Recordset
Dim rstemp As Recordset
Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges)
Set rsUnionquery = db.OpenRecordset(UnionSQL)
Do While Not rsUnionquery.EOF
rstemp.AddNew
rstemp!ObjectID = rsUnionquery!ObjectID
rstemp!SearchNo = rsUnionquery!SearchNo
rstemp!DateSearched = rsUnionquery!DateSearched
rstemp!Consultant = rsUnionquery!Consultant
rstemp!jobID = rsUnionquery!jobID
rstemp.Update
rsUnionquery.MoveNext
Loop
End Sub
View 2 Replies
View Related
Mar 14, 2007
I am using a 'memo' field in a table because the field size needs to be more than the maximum for a 'text' field (255 characters).
Although the text appears in full in the table, when I run a query to select certain records from that table the text in the memo field is truncated to 255 characters.
Anyone know how to correct this?
View 1 Replies
View Related
Aug 24, 2007
Hi all,
I have been using this command in VB to export Access tables to Excel. Everything works fine except when the table contains a large Character/Text field- it gets truncated to around 200 chars. Is this a known limitation or I am doing something wrong? DoCmd.TransferSpreadsheet command works correctly but there are some limitations to this command.
Can please somebody help
Thanks
Claude
View 4 Replies
View Related
Aug 24, 2005
Hi all,
I had table with following data
Table
f1 f2 f3
1 10 aa......
1 11 aaa...
2 10 bb...
2 11 bb.......
f3 is memo field
I had to retrieve data by grouping records based on f1 value
so i gave groupby in totals section to f3 field also.
I am getting the values correctly, but memo field is truncating.
Its only displaying first half arround 236 charecters only.
If i query directly without performing any group by
then i am getting entire data for the memo field.
please any one give the solution.
waiting for your help.
Thanks
View 1 Replies
View Related
Jul 31, 2007
Hello,
I asking to see if it is possible to run a query on a memo field. For an example I want to pull all records where the memo field contains the word "Test".
Is this possible?
Thanks!
Fen
View 1 Replies
View Related
Oct 7, 2007
I have a field [InternalComments] which is a memo field. Lots of data per customer
Can I make an update query to add data to the existing data without overriding the data currenty there?
Thanks
View 14 Replies
View Related
Feb 26, 2015
I have a table that contains a memo that is delimited by line breaks. For each of these breaks i need a new record in the query results that i can then use in a Labelling application.
My current query looks something like:
ID | Product | Pack Size
1 | item a | 1x1000,1x1050
2 | item b | 1x1000,20x25
3 | item a | 1x1000
(Where the , is a new line)
Whereas my ideal output is:
ID | Product | PackSize
1 | Item a | 1x1000
1 | Item a | 1x1050
2 | Item b | 1x1000
2 | Item b | 20x25
etc.
I think im supposed to use the Split() Function though i dont have a clue wherw to start.
View 11 Replies
View Related
Apr 5, 2013
I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.
I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:
SELECT TableA.*
FROM TableA
Union
SELECT TableB.*
FROM TableB;
(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )
View 8 Replies
View Related
Mar 24, 2008
Running 2007
I never ran into this problem before.
I have a qry, with showing totals (group by). One of the fields is called comments, which is a memo field. I am only seeing partial comments.
When I redesign the query and take out the "totals", I see the entire comments.
Is there a fix to this?
View 4 Replies
View Related
Aug 27, 2013
I can select memo field (configured as RichText) as rendered html:
select somehtmlfield from sometable
And have rendered html output:
"normal bold"
Or select it as html string
select Cstr(somehtmlfield) from sometable
And get
"<div>normal <b>bold</b></div>
Is there any way to produce rendered html from string?
I would like to:
select SomeConvertingFunc("<div>something <b>bold</b></div>") from sometable
And have it rendered to "something bold".
View 1 Replies
View Related
Sep 5, 2014
I'm trying to concatenate two text fields into a memo field using an expression in a select query. My problem is that the text fields together end up more than 255 characters, so I need the resulting field to be a memo instead. I can't change the underlying text fields to memo fields because this is a large database used by others who need those fields to be text.
View 3 Replies
View Related
May 16, 2013
Table shows memo text fine..
but when i run a query its shows the memo field with text like: 였A and æ„Š` and è¬ï¿½
View 3 Replies
View Related
May 14, 2013
I have got a db and that has 10+ ref tables, and i need to show everything from those tables in one, and these tables includes Attachment field. I have tried union but did not work.
How can i achieve this?
View 4 Replies
View Related
Jun 13, 2014
I'm having an issue where when I attempt to export data from an Access database to an excel spreadsheet using VBA it truncates any field longer than 255 characters to the 255 limit. I'm using
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "tableName", "FilePath", True, ""
to export it (obviously with the table name and filepath filled in) but for some reason I cannot get it to export the entirety of the field to excel. I've been doing some digging on various forums around the internet and it seems as though it may be possible to split it into various excel cells then use automation to concatenate the cells. But considering this field I am trying to get not truncated can be up to 40,000 characters theoretically, it doesn't make sense to do it that way.Do you need more information from me? I'm somewhat new to both Access and VBA.
View 2 Replies
View Related
