truncate field on import? **

[ExcessAccess]

I'm trying to import a field to one table, from another. But, I only need
the first three charactes of the field. Is there a way that I can import
this field, but only the first 3 characters of it. I guess if not, I could
just always import the data as it is, then in table design just make the
field size 3 characters. But maybe there's a better way.

Jarrod

 

[Ken Snell [MVP]]

Import the data to a temporary table, then use an append query to copy the
data *in your desired format* to the permanent table. In that query , you
can use a calculated field that truncates the field's text string to just
the first three characters.

 

[Mike Labosh]

I'm trying to import a field to one table, from another. But, I only need

the first three charactes of the field. Is there a way that I can import
this field, but only the first 3 characters of it. I guess if not, I
could
just always import the data as it is, then in table design just make the
field size 3 characters. But maybe there's a better way.

1. Make a select query on the source table that specifies all the columns
2. For the column in question, have your select query use an expression like
this:
Left$([ColumnToTruncate], 3)
3. Import from the select query to your other table.

 

[Ken Snell [MVP]]

Thanks, Mike... I see I missed his reference to wanting to get data from an
ACCESS table... was thinking importing of text or EXCEL.

--

Ken Snell
<MS ACCESS MVP>

I'm trying to import a field to one table, from another. But, I only need
the first three charactes of the field. Is there a way that I can import
this field, but only the first 3 characters of it. I guess if not, I
could
just always import the data as it is, then in table design just make the
field size 3 characters. But maybe there's a better way.

1. Make a select query on the source table that specifies all the columns
2. For the column in question, have your select query use an expression like
this:
Left$([ColumnToTruncate], 3)
3. Import from the select query to your other table.

--
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will."

 

[ExcessAccess]

Okay, I made the select query, and for the the criteria on the particular
field, I entered:

Left$([PageNum],3)

When I try to view the dynaset I get "Data type mismatch in criteria
expression."

I know about setting data types when making tables, but I don't know why a
data type would be a problem here. Ideas?

Jarrod

I'm trying to import a field to one table, from another. But, I only need
the first three charactes of the field. Is there a way that I can import
this field, but only the first 3 characters of it. I guess if not, I
could
just always import the data as it is, then in table design just make the
field size 3 characters. But maybe there's a better way.

1. Make a select query on the source table that specifies all the columns
2. For the column in question, have your select query use an expression like
this:
Left$([ColumnToTruncate], 3)
3. Import from the select query to your other table.

--
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will."

 

[ExcessAccess]

I see how expression works, thanks much. But, I didn't mention that the
source table is actually a MAS90 table, I just import the table from MAS90
into Access and then clean the data. (The MAS90 tables aren't linked to my
database, so I probably can't query them...?).

But, this does works fine as an update query (as Ken proposed)!

Jarrod

I'm trying to import a field to one table, from another. But, I only need
the first three charactes of the field. Is there a way that I can import
this field, but only the first 3 characters of it. I guess if not, I
could
just always import the data as it is, then in table design just make the
field size 3 characters. But maybe there's a better way.

1. Make a select query on the source table that specifies all the columns
2. For the column in question, have your select query use an expression like
this:
Left$([ColumnToTruncate], 3)
3. Import from the select query to your other table.

--
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will."

 

[ExcessAccess]

I removed the $ and it seems to work now, thanks.

Jarrod

Okay, I made the select query, and for the the criteria on the particular
field, I entered:

Left$([PageNum],3)

When I try to view the dynaset I get "Data type mismatch in criteria
expression."

I know about setting data types when making tables, but I don't know why a
data type would be a problem here. Ideas?

Jarrod

I'm trying to import a field to one table, from another. But, I only need
the first three charactes of the field. Is there a way that I can import
this field, but only the first 3 characters of it. I guess if not, I
could
just always import the data as it is, then in table design just make the
field size 3 characters. But maybe there's a better way.

1. Make a select query on the source table that specifies all the columns
2. For the column in question, have your select query use an expression like
this:
Left$([ColumnToTruncate], 3)
3. Import from the select query to your other table.

--
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will."