Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    adding numbers (Office XP)

    Hi,

    Is there an easy way to add a set of numbers to data with a field? I have a field called TN that contains an auto id (primary key) - I need for processing reasons to add "999" to the left of the number and then send the data off for processing. On return, I need to strip the "999" off the record.

    Is there an easy way to do this, I have been playing with update queries - i have done this using concatenate in excel which works Ok but adds an extra step to the process.

    Thanks....Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: adding numbers (Office XP)

    You can't modify an AutoNumber field in any way. Instead, create a query based on the table, and add an extra calculated column that prepends "999" to TN. For example, if you want 9 digit "numbers":

    NewTN: "999" & Format([TN],"000000")

    If you really need to modify the TN field itself, you must change its type from AutoNumber to Number (Long Integer) or to Text. You can then perform an update query. But you will lose the automatic assignment of a value for new records.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: adding numbers (Office XP)

    As Hans said, you can't modify an autonumber. Even if this wasn't an autonumber, I'm somewhat troubled by what you said. That is, "... to add "999" to the left of the number and then send the data off for processing. On return, I need to strip the "999" off the record."

    What do you mean by "send it off for processing". And if this table has records in child tables, modifying the PK should cascade down to these records. This just doesn't seem like a good practice. If necessary, create a temp table and use a query to fill it, adding the prefix at that time.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: adding numbers (Office XP)

    Thanks for that, that will work nicely - Mark is exactly right in what he says, that is what I currently do adding and deleting the prefix manually. I need to do this as some records need to be sent away again for processsing and the payment server (I am processing credit card numbers) rejects the record ID if it has been previously sent - even if the record was rejected for some reason - usually incorrectly keyed card number. Thanks again for your help.

    Regards....Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •