converting a Memo Field

[^Halibut^]

is there any way to convert a memo field to a text string without loosing
the data? i have to convert a database that has memo fields with lots of
text data. But, if i view it i can only see the first line of text and then
i get a bunch of NULL characters. Any ideas on how to convert this to text
without loosing the data?

Thanks

 

[Chris Mills]

A text field is limited to 255 chars, which is usually why one chooses to use
a memo field, which allows 64k.

If your data is longer than 255, then converting to text field will inevitably
truncate it.

It may just be that the data contains CR/LF (line control characters). Control
characters might appear as little square boxes or something, but the first
255chars should be there (perhaps scrolled off down the screen).

If the text has been formatted (for instance with Word), it may well contain
"strange" control characters. In this case, it is designed to be viewed with
Word!

Generally, I would not like to convert from memo to text. Why do you want to
do that? (except there are some search or index limitations with memo fields)

Have you expanded the text field to see if there's more than one line?
Chris

 

[^Halibut^]

Hi Chris,

It's a database that contains some critical data. There are "little
squares" in there. i have not been able to get it viewable. i need to
convert it to text, so, i can then import it into a SQL database via the
Access upgrade wizard. i've tried it already and it doesn't convert the
table with the memo fields.

Any suggestions would be helpful.

thanks
Joe

 

[Chris Mills]

"little squares" (or similar) are a symptom of some sort of formatting.

If you think it can fit within 255chars, and you want "plain text", then you
have to convert it to plain text "somehow". (so far, so good!)

I do not know of any standard Access function to do that. But Access text
fields can store the full range of 8-bit Ascii (if not Unicode). You can write
a routine using things like Left$, Mid$, Right$, Chr$, and possibly other
things, to break-down a string and re-constitute it. If you don't know how to
do that, or don't have the time etc, then find someone who does or give up.
There is no standard method in Access or anything else, I'm afraid.

(If I was asked to convert "little squares", I would just say "it's uneconomic
to do so!")
(If the customer insisted, I would have to decide whether it is better
economically to just do every record manually, or write test and debug some
purpose-built routine)
Chris

 

[Andi Mayer]

Hi Chris,

It's a database that contains some critical data. There are "little
squares" in there. i have not been able to get it viewable. i need to
convert it to text, so, i can then import it into a SQL database via the
Access upgrade wizard. i've tried it already and it doesn't convert the
table with the memo fields.

Any suggestions would be helpful.

ASC(Mid("theMemoText",5,1)) will give you the ASCII code for Position
5

Check your Squares, if you are lucky they will be 10 and 13, means
vbCrLf (carriage return and line Feed)

if you don't need them then use:

replace("theMemoText",chr(10) &chr(13),"")

if you want them divided, to write this into a seperate table to join
them you could use a function like:

sub divideTheMemoField (input as string)
dim ary() as string
dim I as long
ary=split(input,chr(10)&chr(13))
for I=0 to ubound(ary)
.....write ary(i) to a new table+primary Key from original in a field
next I
end sub

 

[^Halibut^]

Thanks for all the help! wish me luck

Hi Chris,

It's a database that contains some critical data. There are "little
squares" in there. i have not been able to get it viewable. i need to
convert it to text, so, i can then import it into a SQL database via the
Access upgrade wizard. i've tried it already and it doesn't convert the
table with the memo fields.

Any suggestions would be helpful.

thanks
Joe

 

[david epsom dot com dot au]

Access upgrade wizard. i've tried it already and it doesn't convert

the table with the memo fields.

Which may mean that there is something wrong with the table.

Do you have a table which includes some corrupted data? If
so, you need to restore the corrupted data, table, and database
from a backup copy.

(david)

 

[MICHAEL MILLER]

"little squares" (or similar) are a symptom of some sort of formatting.

If you think it can fit within 255chars, and you want "plain text", then you
have to convert it to plain text "somehow". (so far, so good!)

I do not know of any standard Access function to do that. But Access text
fields can store the full range of 8-bit Ascii (if not Unicode). You can write
a routine using things like Left$, Mid$, Right$, Chr$, and possibly other
things, to break-down a string and re-constitute it. If you don't know how to
do that, or don't have the time etc, then find someone who does or give up.
There is no standard method in Access or anything else, I'm afraid.

(If I was asked to convert "little squares", I would just say "it's uneconomic
to do so!")
(If the customer insisted, I would have to decide whether it is better
economically to just do every record manually, or write test and debug some
purpose-built routine)
Chris

 

[ss]

dsadsasdas