How can I convert text field to memo field in a query

[efandango]

I have a one table query with a user defined memo field.

Address:

how can I convert this field from a text field to a memo so that the output
can handle more than 255 characters?

 

[Jeff Boyce]

If the underlying field in the table is "text", your SELECT query isn't
going to change that.

If you need the underlying field in the table to be "memo" instead of
"text", you have to change that in the table definition.

By the way, why do you believe you need more than 255 characters to hold an
[Address]? What kind of address? Please provide an example so folks here
can offer alternate approaches, if appropriate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lord Kelvan]

i agree with jeff even here in nz with the longest place name in the
world

Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu

is only 85 characters

if anything you should try to avoid using memo fields and they make
doing some queries where you summarise data very difficult as you
cannot do group by on a memo field

Regards
Kelvan

 

[Lord Kelvan]

as a note for thoes interested the above means

The summit of the hill, where Tamatea, who is known as the land eater,
slid down, climbed up and swallowed mountains, played on his nose
flute to his loved one

Regards
Kelvan

 

[efandango]

Jeff,

the field is a user defined text field in the Query Editor. The reason for
the +255 length is because I am writing out an XML tagged field which has a
lot of tags, such that it takes the field data over 255 chars. I can't change
the field definition in the table because it doesn't exist there, only in the
query. I have seen posts on here where people have converted from memo to
text fields on-the-fly, so figured that there may be a way to do it the other
way round.

If the underlying field in the table is "text", your SELECT query isn't
going to change that.

If you need the underlying field in the table to be "memo" instead of
"text", you have to change that in the table definition.

By the way, why do you believe you need more than 255 characters to hold an
[Address]? What kind of address? Please provide an example so folks here
can offer alternate approaches, if appropriate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a one table query with a user defined memo field.

Address:

how can I convert this field from a text field to a memo so that the
output
can handle more than 255 characters?

 

[Jeff Boyce]

Take a look at the "C" (convert) functions (like CInt-convert to integer,
CCurr-convert to currency, ...) to see if there's a convert-to-memo.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

the field is a user defined text field in the Query Editor. The reason for
the +255 length is because I am writing out an XML tagged field which has
a
lot of tags, such that it takes the field data over 255 chars. I can't
change
the field definition in the table because it doesn't exist there, only in
the
query. I have seen posts on here where people have converted from memo to
text fields on-the-fly, so figured that there may be a way to do it the
other
way round.

If the underlying field in the table is "text", your SELECT query isn't
going to change that.

If you need the underlying field in the table to be "memo" instead of
"text", you have to change that in the table definition.

By the way, why do you believe you need more than 255 characters to hold
an
[Address]? What kind of address? Please provide an example so folks
here
can offer alternate approaches, if appropriate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a one table query with a user defined memo field.

Address:

how can I convert this field from a text field to a memo so that the
output
can handle more than 255 characters?

 

[Bob Barrows [MVP]]

I have a one table query with a user defined memo field.

Address:

how can I convert this field from a text field to a memo so that the
output can handle more than 255 characters?

Could you show an example where the output is limited to 255 characters?
An expression should not be limited to 255 characters. For example:

SELECT len([Expr1]) from (
SELECT String(300,"a") AS Expr1
FROM [TableContainingOneRecord])

returns 300 for me. I'm not clear why any conversion is necessary,
unless you are storing the data or displaying it in some control that is
limited to 255 characters.

 

[Lord Kelvan]

the only time i have problems with memos and queries is when i try to
summarise data as memos cannot be grouped by

Regards
Kelvan

 

[efandango]

Bon,

this is the string I am trying to output:

KML_Address: "<Placemark><name>" & [Venue] & " (List" & [List_No] & ")" & "
</name><address>" & [Address] & ", " & [Postcode] & "</address><description>"
& " LOL:" & [LBy1] & "<br>" & " LOR:" & [LBy2] & "</br><br>" & " LOL:" &
[LBy3] & "</br><br> LOR:" & [LBy4] & "</br>" & "</description></Placemark>"

but when I open up the text in wordpad, near to the end (in varying
lengths), I get a sequence of characters like this: ☺☺☺☺☺????☺☺☺????

I'm a bit green at this, so perhaps, it is not max chars issue, but I don't
know how to resolve it.

I have a one table query with a user defined memo field.

Address:

how can I convert this field from a text field to a memo so that the
output can handle more than 255 characters?

Could you show an example where the output is limited to 255 characters?
An expression should not be limited to 255 characters. For example:

SELECT len([Expr1]) from (
SELECT String(300,"a") AS Expr1
FROM [TableContainingOneRecord])

returns 300 for me. I'm not clear why any conversion is necessary,
unless you are storing the data or displaying it in some control that is
limited to 255 characters.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

I am presuming that it displays correctly when you open the query in Access?
If so, could you show us a typical result that displays correctly in Access
but incorrectly in Wordpad (have you tried Notepad instead?)?

Bon,

this is the string I am trying to output:

KML_Address: "<Placemark><name>" & [Venue] & " (List" & [List_No] &
")" & " </name><address>" & [Address] & ", " & [Postcode] &
"</address><description>" & " LOL:" & [LBy1] & "<br>" & " LOR:" &
[LBy2] & "</br><br>" & " LOL:" & [LBy3] & "</br><br> LOR:" & [LBy4] &
"</br>" & "</description></Placemark>"

but when I open up the text in wordpad, near to the end (in varying
lengths), I get a sequence of characters like this: ????????????????

I'm a bit green at this, so perhaps, it is not max chars issue, but I
don't know how to resolve it.

I have a one table query with a user defined memo field.

Address:

how can I convert this field from a text field to a memo so that the
output can handle more than 255 characters?

Could you show an example where the output is limited to 255
characters?
An expression should not be limited to 255 characters. For example:

SELECT len([Expr1]) from (
SELECT String(300,"a") AS Expr1
FROM [TableContainingOneRecord])

returns 300 for me. I'm not clear why any conversion is necessary,
unless you are storing the data or displaying it in some control
that is limited to 255 characters.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get
a quicker response by posting to the newsgroup.

 

[Lord Kelvan]

there are most likly some special characters noted by your tags or
soemting for instance that smily face is char(1) which for god knows
what reason is the visual repesentation of start of heading

which is somethign to do with transmission control

SOH
Start of Heading. Used to indicate the start of a heading which may
contain address or routing information.

http://www.interfacebus.com/ASCII_Table.html

Regards
Kelvan