Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    358

    How to combine two fields into one?

    Hello,

    I need to merge a multi-choice prefix field to my ID field and have both fields combined into one field called "FullID" in my table.

    The ID field is an auto number, and the prefix choice will come from a drop-down list lookup field in the same table e.g. AA, BB, CC. So the combined FullID will be AA001, AA002, BB003 etc etc.

    What would be the best way of doing this? Im thinking a query to merge the two fields, then another query to append into the FullID field, all executed from a macro?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    Typically you wouldn't save a field like that, as it can be calculated from existing fields. You'd just display it by concatenating the two together:

    PrefixField & AutoNumberField

    If you really needed to have the field, you could do the same thing in a simple UPDATE query (the above would be the Update To). Of course you wouldn't want to keep doing that (which is why the field shouldn't exist), so you'd do it on the form where users added records.
    Paul

  3. #3
    Join Date
    Aug 2004
    Posts
    358
    Quote Originally Posted by pbaldy View Post
    Typically you wouldn't save a field like that, as it can be calculated from existing fields. You'd just display it by concatenating the two together:

    PrefixField & AutoNumberField

    If you really needed to have the field, you could do the same thing in a simple UPDATE query (the above would be the Update To). Of course you wouldn't want to keep doing that (which is why the field shouldn't exist), so you'd do it on the form where users added records.
    Hello, I know I can concatenate the two fields by using a query ( [PrefixField]&" "&[AutoNumberField] ) but I do need to have it saved in a field in the table after the query has concatenated the two fields.

    So what would be the best way to save it into a field? The only way I have made it work so far is to have 2 tables (a temp table, and an ammended table). Then use a query to concatenate, then use a query to append into the append table, then use a query to delete from the temp table, all run via a macro! It works, but its very long winded, so surely there is a simpler option.

    I havent been able to make an update query work yet. I get the message "operation must use an updateable query".
    Last edited by moss2076; 07-24-10 at 06:12.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,793
    Presuming the two fields are populated this should work:

    UPDATE TableName
    SET JointField = PrefixField & AutoNumberField

    But what you would want to do is do it on the form where user's enter data, rather than be constantly updating the table.
    Paul

  5. #5
    Join Date
    Aug 2004
    Posts
    358
    Yup that has sorted it, many thanks

    I found it strange that Access wasnt able to do it when I selected an update query from the Query Type button, but your code has got it working!

Posting Permissions

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