how to convert a text(255) filed to text(100)

[Convert_Cast]

I would like to know whether there is a built-in function to convert a text
field (fieldsize =255) to another text field (fieldsize = 100) in the Access
Query.

For example, in the following example,

create table foo
(
long_text text(255)
)

How can I write a query in Access to select record from foo table and
convert the long_text filed to text(100). SQL Server has convert() and Cast()
function to achive that. Is there any equivalent functions available in
Access.

 

[Duane Hookom]

Use
Select Left([FieldName],100) as L100FieldName
From...

 

[Rick B]

Just pull the first 100 characters...

Left([long_text],100)

 

[Convert_Cast]

Thank you for your replies. Looks like I didn't state my question clearly at
the beginning.

The query I am working on is a Make-table query so the actuall syntax is as
followed:

SELECT long_text AS short_text
INTO new_foo
FROM foo

The data type of short_text field in new_foo table need to be text(100).
Using left() function will not change the data type of the short_text.

Currently, I am using the following query to change the data type of the
short_text field.

ALTER TABLE new_foo
ALTER COLUMN short_text TEXT(100)

I was wondering whether there is a built-in function in Access, so I could
complete this task in the first query.

Thanks again.

Just pull the first 100 characters...

Left([long_text],100)

I would like to know whether there is a built-in function to convert a text
field (fieldsize =255) to another text field (fieldsize = 100) in the Access
Query.

For example, in the following example,

create table foo
(
long_text text(255)
)

How can I write a query in Access to select record from foo table and
convert the long_text filed to text(100). SQL Server has convert() and Cast()
function to achive that. Is there any equivalent functions available in
Access.

 

[Chris2]

Thank you for your replies. Looks like I didn't state my question clearly at
the beginning.

The query I am working on is a Make-table query so the actuall syntax is as
followed:

SELECT long_text AS short_text
INTO new_foo
FROM foo

The data type of short_text field in new_foo table need to be text(100).
Using left() function will not change the data type of the short_text.

Currently, I am using the following query to change the data type of the
short_text field.

ALTER TABLE new_foo
ALTER COLUMN short_text TEXT(100)

I was wondering whether there is a built-in function in Access, so I could
complete this task in the first query.

Well, at least in A2K, the CStr type conversion function (from VB),
doesn't have an arguement allowing the definition of the output field
size. My speculation is that it's probably because in VB most string
data types have 255 characters.

I checked in JET 4.0's help, and found, under "SELECT...INTO
STATEMENT", "When you create the table, the fields in the new table
inherit the data type and field size of each field in the query's
underlying tables, but no other field or table properties are
transferred."


Sincerely,

Chris O.

 

[John Vinson]

I would like to know whether there is a built-in function to convert a text
field (fieldsize =255) to another text field (fieldsize = 100) in the Access
Query.

For example, in the following example,

create table foo
(
long_text text(255)
)

How can I write a query in Access to select record from foo table and
convert the long_text filed to text(100). SQL Server has convert() and Cast()
function to achive that. Is there any equivalent functions available in
Access.

Just to amplify on Chris and Rick's comments, two things to note:

- A MakeTable query has no provision for specifying the size (or
format or most other properties) of a new field.
- It may not matter anyway; Access Text fields are actually VarChar
and do not store trailing blanks, so a 255-byte field containing 100
bytes takes up exactly the same space as a 100 byte field containing
those same 100 bytes.

If you need the field size restricted for later use, you'll need to
create the new table (in the UI, in code, or with a Create Table DDL
query) and run an Append query to populate it, rather than using a
MakeTable.

John W. Vinson[MVP]