Force data type from Text to Memo in a simple Make Table Query

I'm concatenating fields of various data types that upon completion sometimes 
reaches around 500 characters.  Not huge, but larger than the Text limitation 
to which is what Access 2007 of course converts this.  How can I force the 
data type to be Memo while I'm in the query so the resulting table displays 
all the data without any truncation.
-- 
TIA
Eric S
UPRR
1
Utf
4/12/2010 9:18:07 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
2489 Views

Similar Articles

[PageSpeed] 9

I don't believe you can.

I think you'll have to create the table first, and then append to it.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Eric" <ehstumpf@up.com.(nospamplease)> wrote in message 
news:73585305-E3B6-466B-AE76-93268C353B12@microsoft.com...
> I'm concatenating fields of various data types that upon completion 
> sometimes
> reaches around 500 characters.  Not huge, but larger than the Text 
> limitation
> to which is what Access 2007 of course converts this.  How can I force the
> data type to be Memo while I'm in the query so the resulting table 
> displays
> all the data without any truncation.
> -- 
> TIA
> Eric S
> UPRR 


0
Douglas
4/12/2010 9:36:10 PM
Reply:

Similar Artilces:

How can I run a spreasheet over range of input data ?
I have set up a work sheet that takes input data (a number) from a cell, goes through all kinds of calculations based on other cells, and finally puts out a results in a output cell. Using this sheet, how can I sweep the input data over a range (min,max] and record the output data over the sweep? Thanks for any hint. -- Bernd ...

Collections Query Problem
When I try to run a new collections query I now get the message "Unhandled script exeception: Sql Server Could not find Stored procedure xxx.dbo.cnpCollectProQuery" Does anyone know how I can check or install this stored procedure ...

Autosignature not displaying text
I have created a advanced signature within outlook, and saved this as a web page within the signatures file. When I apply this to 1 computer the signature details are picked up. If I recreate the details on another computer and save in the same way, it does not display the signature. However if I go back in and edit the signature the details appear..................... ...

Splitting Text in a Cell
Using Excel XP, I have a cell that has full names of people (First & Last Names). Can I split these two text somehow recognizing the space in between them? Thank you. Brig, have a look at data, text to columns, post back if you need any help with it -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "Brig" <golteb@netzero.net> wrote in message news:ed9422dXEH...

MS Access Query Case Locking
Hi there, I have recently been writing my own database's for my company and have founds these posts to be very helpfull for the problems that i have hit. Unfortunatly i have hit one problem that i can not find the solution for. I have written a query so that when i click a button it runs and asks me for the part number of a field. It works fine other than the case. The tables are from a ODBC databse from another peice of software. All the information has been written in uppercase and my databse will only show results if the caps lock is on when inputting the search criteria. I can not cha...

Simple But Stumped
Maybe it should be "Simple AND Stumped" Attempting a very simple VLOOKUP formula and can't get the result in the cell with the formula. =VLOOKUP(B4,testrange,2,FALSE). The Fx or Function Arguments wizard shows me the correct answer but it does not appear in the cell. Only the formula appears there. I have used VLOOKUPS successfully before, but stumped here. Any hints or solutions would be appreciated. Regards Is the cell formatted as text? Also, check Tools>Options>View that the Formulas box is not checked. -- HTH RP (remove nothere from the email address if mail...

How do I convert monthly data to quarterly data?
I would like to convert high frequency (such as monthly) time series data to lower frequency (such as quarterly) data. I'm assuming that you have a column of dates (daily) that you also want to be able summarize for quarterly purposes in a Pivot Table or some other format. Probably the best way I have been able to come up with a solution in my experience is this: 1. Create a helper row that gives you the month with the following formula (this assumes that you have the daily date in one of the columns of existing data): =text(A2,"mmmm") If A1 is the cell at the top of the ...

How do I count unique items in a pivottable data field.
I have a pivottable and I am trying to setup a data field that will count unique values. ie. Listing customers as the row and setting up a field to count the number of sales people that sold to the customer. The base data has multiple rows identifying the customer, sale id, $ and sales person. If I have 100 rows with sales person A, B & C each selling multiple orders to several customers I want the field counter to tell me if 1,2 or 3 sales people sold to each customer. Not the most elegant answer, but I'd probably create my pivot with Customer in the row field, sales rep...

filtering: sometimes data below blank lines is &isnt available?
When filtering: some of my sheets have blank lines inserted & i am still able to filter the data below the blank lines. Sometimes I can not. Why? Depends on if you had all the data selected when you activated the AutoFilter, or if you just had a single cell selected (in which case, XL "helps" and picks the complete set of data up till the first blank line). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Allison" wrote: > When filtering: some of my sheets have blank lines inserted & i am still able ...

How can I make cell data automatically shift up when deleting
EXAMPLE: A B C 1) Dog Bone Leash 2) Cat Catnip Litter 3) Bird Seed Newspaper 4) Cow Grass Manuer 5) Duck Fish Filter 6) Mouse Cheese Hanta Virus If I were to delete the information in row 1, I want everything in row two to shift up automatically. Or... If I were to delete "dog," I would want everything in Column A, and only Column A, to shift up, therefore removing any blank cells in the table. How would I do this? One way .. Assuming this table is in Sheet1, cols A to C, data from...

How to count last column of data
Using excel VBa, I can count last row of data using lastrow = Worksheets("Spare2").Cells(Rows.count, 1).End(xlUp).row But how to use it for column? lastcol=Worksheets("Spare2").cells(rows.count,1).end(xltoleft).column HTH -- AP "crapit" <biggercrap@yahoo.com> a �crit dans le message de news: eaEVzG9hGHA.4368@TK2MSFTNGP03.phx.gbl... > Using excel VBa, I can count last row of data using lastrow = > Worksheets("Spare2").Cells(Rows.count, 1).End(xlUp).row > But how to use it for column? > Worksheets("Spare2").Cells(1,...

text page format on new email
When I start a new email, my curser drops down the text page about half way (10 to 15 lines). It is almost like it is set up for a letter head (I don't have one). People recieving my emails receive them in this same format. It only happens on my new emails. All forwards responses, etc are normal. Try this Go to start -- run -- outlook /safe and check if this helps ... if yes i can suggest further steps... <r.a.hoover@adelphia.net> wrote in message news:1154099151.651968.171660@b28g2000cwb.googlegroups.com... > When I start a new email, my curser drops down the text page abou...

Change Event can't reference .text property
Hello, I have a procedure to set the RecordSource of a form in the AfterUpdate event of a ComboBox. Using the Change event of the came Combobox I want the AfterUpdate event to run when the user hits the delete key to clear the ComboBox value. Here is the method. I moved the code to a button hoping I could resolve this. Private Sub TruckFilter_Change() If TempVars("aUpdate") = False And [TruckFilter].Text = "" Then [TruckFilter] = Null Call RunFilterCode_Click End If TempVars("aUpdate") = False 'This variable is set to true in the AfterUpdate ev...

Pivot Tables Resizing Columns
I am working with pivot tables. When I introduce another variable into the pivot table or run a show pages to create individual reports based on a field, it seems to change all the fonts and resize all of the columns and the like. Is there any way to lock this down so that the columns and formats are the same for all pages in the workbook Any help would be most appreciated. Rightclick the table & choose "Table Options" from the pop-up menu. Uncheck "AutoFormat Table". HTH, Andy Thanks Andy, I will give it a try ...

Data>Filter>Auto Filter in excel 2007?
I have Excel 2003, but oversee a large group that has Excel 2007 (just got it). I can't find simple instructions on how to Auto Filter. Please help On the Data tab you can turn AutoFilter on and off Or use Alt dff to Toggle See also http://www.rondebruin.nl/0307commands.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "TIJ" <TIJ@discussions.microsoft.com> wrote in message news:DCAD1A38-EC91-4CAC-B090-8355BEED1148@microsoft.com... >I have Excel 2003, but oversee a large group that has Excel 2007 (just got it). > > I can't find simple ins...

VLookup and text strings
Hello, I am using the vlookup function. I have values that have three to nine text characters followed by numbers. Examples are COM-123, COM-124, ABCD-12, ABCD-13, etc,. The number of letters may be 2-9. I want to modfiy my lookup formula so that COM-123 returns the value COM. Or ABCD-13, returns the value ABCD. Any help appreciated. That is not what VLOOKUP is used for. See http://spreadsheets.about.com/od/excelfunctions/ss/90320vlookup.htm This returns the letters: =LEFT(A1,FIND("-",A1)-1) This returns the number: =--MID(A1,FIND("-",A1)+1,99) best wish...

vlookup and Access Database Table
Can vlookup be used to get a result from an Access Database Table? Thanks. The vlookup function itself is an Excel function and as such can only retrieve data from an excel sheet. However you may be able to use the import external data menu option in the Data menu to retrieve your data from the access database, pull it into your excel workbook and then use the vlookup function. I never tried this myself, but maybe the idea can be applied. -- Jeroen van Nieuwkerk "finster26" wrote: > Can vlookup be used to get a result from an Access Database Table? > > Thanks....

Publisher, why can't I see background and text on screen but can .
In Publisher when making posters etc I cant see the coloured background while typing yet I can see it in Print Preview. Have tried all the help facilities but haven't got anywhere. ...

How to Transfer data from Excel to SQL SERVER 2000
hi! i want to transfer data from MS Excel Sheets to SQL Server. is there any way. Thanks Ahmad Jalil Qarshi The simplest way is to use the DTS Import Wizard. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinpub.com .. "Ahmad Jalil Qarshi" <ahmaddearNO@SPAMhotmail.com> wrote in message news:ec7wd8abFHA.3712@TK2MSFTNGP09.phx.gbl... hi! i want to transfer data from MS Excel Sheets to SQL Server. is there any way. Thanks Ahmad Jalil Qa...

Averaging Time from mulitple tables
im currently making a database that logs time for how long people are waiting i want to make a query that averages all that infomation i have 1 table that logs when people arrive and another one when people leave i also need to be able to use it when there is more data added to it. i know nearly nothing about queries in databases so can you type in plain english, thanks in advance. You will not be able to do this unless you have a way to tie your tables together. Presumably you have a table of clients, with a ClientID primary key. Or perhaps you just assign a ticket number when the person...

Create a linked table
I need to create a 2nd table that will link to my cust table. The 2nd table should contain info on freight companys and the cust account numbers to be used for shipping. How do I format the 2nd table? -- Lyndy Hello Lyndy, I think what you want are the following tables: TblCustomer CustomerID CustomerAccountNumber <customer fields> TblFreightCompany FreightCompanyID <freight company fields> TblFreightCompanyCustomerShipping FreightCompanyCustomerShippingID FreightCompanyID CustomerID In TblFreightCompanyCustomerShipping for each freight company you...

Data Form Deletions
Annoying for years, but perhaps the is an alternative I haven't discovered; When finding records using the Data Form based on Criteria, the recordset is restricted to only those which match the Criteria. However, if you delete those found records one at a time (using the Form's delete button), once you delete the last _matching_ record the next records in the list appear in the form -- even though they _do not_ match the criteria. I realize it is a matter of user recognition to notice that, but I can't begin to quantify the number of users who have gon on to delete those add...

Problem with query
Hello, I am trying to create a pivot table of sorts with the following query: Select o.Rep, count(o1.Order_Type) as [Quotes], sum(o1.subtotal) as [Value], count(o2.Order_Type) as [Orders], sum(o2.subtotal) as [Value], count(o3.Order_Type) as [Invoices], sum(o3.subtotal) as [Value] from OEOrdHead o left join OEOrdHead as o1 on o1.Rep = o.Rep and o1.Entered_Date = o.Entered_Date and o1.Order_Type = 'Q' left join OEOrdHead as o2 on o2.Rep = o.Rep and o2.Entered_Date = o.Entered_Date and o2.Order_Type = 'O' left join OEOrdHead as o3 on o3.Rep = o.Rep and o3.Entered_Date = ...

How do I merge two cells without deleting data from the cell?
I'm highlighting two cells in the same row, hitting format cells, alignment, merge cells and I'm getting the error message, "The selection contains multiple data values. Merging into one cell will keep the upper-left most data only." I want to be able to make the two seperate cells one without deleting anything and without having to cut and paste. You can't, you can concatenate 2 cells into one by using a formula and ampersand =A1&" "&B1 however stay away from merging cells, always cause more problems than what it's worth and layout wise ...

Cut and paste specific data
`Hi Have been working on a problem which I think I can now simplify Cell A1 contains the following: 15 Smith Street Blacktown How can I cut the last item (Suburb) from the cell and paste it into B1? Many thanks in advance Stephen Try this in B1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Father John&quo...