Convert a memeo field to a text field

[Len]

I would like to know how to convert a memo field into a text field (in a
table) using an update query. I cannot use an append query because, when I do
this, data is lost.
Thanks

 

[Douglas J. Steele]

I'm not sure I understand what you're trying to do.

If you're saying that only the first 255 characters are being written to
your Memo field, changing to a Text field won't make a difference, since
Text fields can hold a maximum of 255 characters. If this is your problem,
are you using a function on the text value?

 

[John Spencer]

Text fields can hold a maximum of 255 characters. Memo fields can hold 32K
and programatically can be forced to hold up to 2 gig.

So converting a memo field to a text field is always going to lose any
characters after the 255 limit.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Len]

No. Let me explain.
I have data from a company database that I link to and download into a
table. Some of the fields from the DB are memo fields. I only need the first
50-100 characters in the memo fields (and most of the data in these memo
fields are less with a few exceptions).

Later in my program, I have to perform a 'grouping' to eliminate some
duplication. I cannot group the data since some of the fields are memo fields
and you cannot group memo fields.

So, I first created a table with the respective memo fields set as text
fields. I created an append query to pull the data from the system db and
append it to this table (each time I would first flush the table of old data
using a delete query). Unfortunately, whenever a memo field contained more
than 255 characters, the data would not append.

I contacted IT to see if they could change the memo fields to text fields,
but the answer was no. They suggested that I run a make table and go in
manually and change the fields to text. I don't want to do this since others
use this program and I don't want to update it for them. I created a macro to
let them push a button and the program runs all of the updates. If I had to
do the manual change, it would cause me tons of time and work (ie.: 3-4 times
a day and sometimes 7 days a week).

So...I thought that there may be a way to do an update and convert the memo
fields to text fields.

 

[Len]

My initial question was not clear. Let me explain.
I have data from a company database that I link to and download into a
table. Some of the fields from the DB are memo fields. I only need the first
50-100 characters in the memo fields (and most of the data in these memo
fields are less with a few exceptions).

Later in my program, I have to perform a 'grouping' to eliminate some
duplication. I cannot group the data since some of the fields are memo fields
and you cannot group memo fields.

So, I first created a table with the respective memo fields set as text
fields. I created an append query to pull the data from the system db and
append it to this table (each time I would first flush the table of old data
using a delete query). Unfortunately, whenever a memo field contained more
than 255 characters, the data would not append.

I contacted IT to see if they could change the memo fields to text fields,
but the answer was no. They suggested that I run a make table and go in
manually and change the fields to text. I don't want to do this since others
use this program and I don't want to update it for them. I created a macro to
let them push a button and the program runs all of the updates. If I had to
do the manual change, it would cause me tons of time and work (ie.: 3-4 times
a day and sometimes 7 days a week).

So...I thought that there may be a way to do an update and convert the memo
fields to text fields.

 

[Douglas J. Steele]

UPDATE MyTable
SET MyTextField = Left([MyMemoField], 50)

On the other hand, you could just create a query along the lines of:

SELECT Field1, Field2, Left(MyMemoField, 50) AS ATextField
FROM MyTable

and then use that query wherever you would otherwise have used the table.

 

[John W. Vinson]

So...I thought that there may be a way to do an update and convert the memo
fields to text fields.

No "update" is needed; you can solve the problem at the outset, in your
download.

To do so, *link* to the corporate table rather than importing it; then run an
Append query. In the Append query use

FirstBit: Left([memofield], 50)

or 255 or anything less as a calculated field, to append only that much text
into the Text field in your local table.