Changing self made prefix to autonumber ID

[Guest]

Attempting to create a prefix for the ID, I first set up an ID field with
Autonumber as the datatype. This gave a number for all of my contact
entries. I then saved the table, then changed the data type back to text.

The next thing I did was an update query putting the desired prefix on the
ID by entering DesiredPrefix&[ID] in the Update To row of the the query.
Now, I want to change the prefix to another prefix. For example, I want to
change Statenameone (this is the prefix) to Statename1LD. What I did
previously was put Statenameone&[ID] into the Update To row of the ID field.

Thanks.

 

[Michel Walsh]

Hi,


Would it not be easier to keep TWO fields, in the table, one for the prefix
applicable to the record, and the other field being the autonumber? Use the
concatenation of the two fields WHERE/WHEN it is required (for printing in a
report, as example, but in the table, keeping it in 2 fields seems
preferable, isn't it?

Vanderghast, Access MVP

 

[Guest]

Your suggestion is being considered. However, I have several sources in the
table right now and need to do queries on them. For example, in the ID
field, I have Contactsone1,Contactsone10,Contactsone100, etc. I need to keep
the prefix and the ID together because Contactsone represents the source of
where this group came from. What I would like to do is change Contactsone1
to Contacts1LD1, Contacts1LD2, etc., for the entire list of 6376 contacts. I
want the prefix to be Contacts1LD&[ID]. Please help. Thanks.

Hi,


Would it not be easier to keep TWO fields, in the table, one for the prefix
applicable to the record, and the other field being the autonumber? Use the
concatenation of the two fields WHERE/WHEN it is required (for printing in a
report, as example, but in the table, keeping it in 2 fields seems
preferable, isn't it?

Vanderghast, Access MVP

Attempting to create a prefix for the ID, I first set up an ID field with
Autonumber as the datatype. This gave a number for all of my contact
entries. I then saved the table, then changed the data type back to text.

The next thing I did was an update query putting the desired prefix on the
ID by entering DesiredPrefix&[ID] in the Update To row of the the query.
Now, I want to change the prefix to another prefix. For example, I want
to
change Statenameone (this is the prefix) to Statename1LD. What I did
previously was put Statenameone&[ID] into the Update To row of the ID
field.

Thanks.

 

[John Spencer]

It would really make more sense to store the Source in one field and the
number in a second field. If you are determined to make the change then you
could use the following as a model.

UPDATE YourTable
SET [ID] = "ContactsLD1" & Mid([ID],Len("Contacts1")+1)
WHERE [ID] Like "Contacts1*"

In the query grid
Field: ID
Criteria: LIKE "Contacts1*"
UPDATE TO: "ContactsLD1" & Mid([ID],Len("Contacts1")+1)

Of course if this is a one time change, you could just use find and replace
in the datasheet view (being careful in the sequence of the changes)

Your suggestion is being considered. However, I have several sources in
the
table right now and need to do queries on them. For example, in the ID
field, I have Contactsone1,Contactsone10,Contactsone100, etc. I need to
keep
the prefix and the ID together because Contactsone represents the source
of
where this group came from. What I would like to do is change
Contactsone1
to Contacts1LD1, Contacts1LD2, etc., for the entire list of 6376 contacts.
I
want the prefix to be Contacts1LD&[ID]. Please help. Thanks.

Hi,


Would it not be easier to keep TWO fields, in the table, one for the
prefix
applicable to the record, and the other field being the autonumber? Use
the
concatenation of the two fields WHERE/WHEN it is required (for printing
in a
report, as example, but in the table, keeping it in 2 fields seems
preferable, isn't it?

Vanderghast, Access MVP

Attempting to create a prefix for the ID, I first set up an ID field
with
Autonumber as the datatype. This gave a number for all of my contact
entries. I then saved the table, then changed the data type back to
text.

The next thing I did was an update query putting the desired prefix on
the
ID by entering DesiredPrefix&[ID] in the Update To row of the the
query.
Now, I want to change the prefix to another prefix. For example, I
want
to
change Statenameone (this is the prefix) to Statename1LD. What I did
previously was put Statenameone&[ID] into the Update To row of the ID
field.

Thanks.

 

[Guest]

Thanks for your suggestions and help. I tried Find and Replace when I first
embarked effort but failed to click Start of field so it did not work. After
your suggestion, I tried again and got the results I wanted. I have
considered your suggestion about the separate source field, but prefer the
concatenated prefix and auto number.

It would really make more sense to store the Source in one field and the
number in a second field. If you are determined to make the change then you
could use the following as a model.

UPDATE YourTable
SET [ID] = "ContactsLD1" & Mid([ID],Len("Contacts1")+1)
WHERE [ID] Like "Contacts1*"

In the query grid
Field: ID
Criteria: LIKE "Contacts1*"
UPDATE TO: "ContactsLD1" & Mid([ID],Len("Contacts1")+1)

Of course if this is a one time change, you could just use find and replace
in the datasheet view (being careful in the sequence of the changes)

Your suggestion is being considered. However, I have several sources in
the
table right now and need to do queries on them. For example, in the ID
field, I have Contactsone1,Contactsone10,Contactsone100, etc. I need to
keep
the prefix and the ID together because Contactsone represents the source
of
where this group came from. What I would like to do is change
Contactsone1
to Contacts1LD1, Contacts1LD2, etc., for the entire list of 6376 contacts.
I
want the prefix to be Contacts1LD&[ID]. Please help. Thanks.

Hi,


Would it not be easier to keep TWO fields, in the table, one for the
prefix
applicable to the record, and the other field being the autonumber? Use
the
concatenation of the two fields WHERE/WHEN it is required (for printing
in a
report, as example, but in the table, keeping it in 2 fields seems
preferable, isn't it?

Vanderghast, Access MVP

Attempting to create a prefix for the ID, I first set up an ID field
with
Autonumber as the datatype. This gave a number for all of my contact
entries. I then saved the table, then changed the data type back to
text.

The next thing I did was an update query putting the desired prefix on
the
ID by entering DesiredPrefix&[ID] in the Update To row of the the
query.
Now, I want to change the prefix to another prefix. For example, I
want
to
change Statenameone (this is the prefix) to Statename1LD. What I did
previously was put Statenameone&[ID] into the Update To row of the ID
field.

Thanks.