concatenate a numeric field

[Vic33]

I need to reduce a field size and its content. Presently the field is
8integers (with existing data). EG of data, 25679098. I need to get to an
end result, leaving the last five digits, ie 79098, into a new field. Is
there a simple way of doing this? Any help appreciated!

 

[Jeanette Cunningham]

Hi Vic33,
use an update query with the right function.
First make a copy of the table as backup.
Create a query with the field from your table that is to be updated.
In the update to row type
Right([TableName].[FieldName],5)

Replace TableName and FieldName with your names.

Run the query.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

I need to reduce a field size and its content. Presently the field is
8integers (with existing data). EG of data, 25679098. I need to get to an
end result, leaving the last five digits, ie 79098, into a new field. Is
there a simple way of doing this? Any help appreciated!

This has nothing to do with "concatenation" and reducing it in that way won't
help: you still need a Long Int (4 bytes, not 8) to store numbers larger than
65535. Why do you feel that you need to reduce the field size? What is the
datatype now? How big is the database after compaction? Do you have a
DEMONSTRATED performance problem with the data at its current size?

If you want to (permanently and irrevokably) discard the first digits and keep
the last five, run an Update query updating the field to

[fieldname] MOD 100000

Make a backup first, and compact the database afterward.

 

[Vic33]

thanks, this worked a treat! It was needed to link fields from diff tables.
(other table was already at 5integers.).

Hi Vic33,
use an update query with the right function.
First make a copy of the table as backup.
Create a query with the field from your table that is to be updated.
In the update to row type
Right([TableName].[FieldName],5)

Replace TableName and FieldName with your names.

Run the query.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I need to reduce a field size and its content. Presently the field is
8integers (with existing data). EG of data, 25679098. I need to get to
an
end result, leaving the last five digits, ie 79098, into a new field. Is
there a simple way of doing this? Any help appreciated!

 

[Vic33]

answer from Jeanette did the trick. needed to reduce field size to
match/link to field with data in another table (which was already at
5integers). They are PO numbers coming from diff software packages, set up
using diff integer sizes along time ago....thanks for your post.
Regds

I need to reduce a field size and its content. Presently the field is
8integers (with existing data). EG of data, 25679098. I need to get to an
end result, leaving the last five digits, ie 79098, into a new field. Is
there a simple way of doing this? Any help appreciated!

This has nothing to do with "concatenation" and reducing it in that way won't
help: you still need a Long Int (4 bytes, not 8) to store numbers larger than
65535. Why do you feel that you need to reduce the field size? What is the
datatype now? How big is the database after compaction? Do you have a
DEMONSTRATED performance problem with the data at its current size?

If you want to (permanently and irrevokably) discard the first digits and keep
the last five, run an Update query updating the field to

[fieldname] MOD 100000

Make a backup first, and compact the database afterward.

 

[John W. Vinson]

answer from Jeanette did the trick. needed to reduce field size to
match/link to field with data in another table (which was already at
5integers). They are PO numbers coming from diff software packages, set up
using diff integer sizes along time ago....thanks for your post.

If they are identifiers, and you won't be doing any calculations with them,
then use a Text field rather than any sort of Number field (and of course
Jeanette's substringing code will be ideal).