Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Combining two fields in Access

    I want to combine two fields in an access database, I was trying this VB code:



    Code:
    Field1_Field2: [Field1]&"-"&[Field2]
    But it's not working it will show.....

    Compile Error:
    Expected:Expression

    and is highlighting the "&" after [Field1]

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    What result did you get?

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Compile Error:
    Expected:Expression

    and is highlighting the "&" after [Field1]

  4. #4
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Are these fields from the same table?

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Yes. They are both on the same table.

  6. #6
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Can you post the SQL?

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    I'm not sure what you are asking me to post?

  8. #8
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Sorry. When in query design, select "View > SQL View". copy the text of the query, and post it.

  9. #9
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    ah, learn something new every day!

    SELECT Sheet1.Field1, Sheet1.Field2
    Field1_Field2: [Field1]&"-"&[Field2]
    FROM Sheet1;

  10. #10
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Try this:

    Code:
    SELECT [Field1] & "-" & [Field2] AS Field1_Field2
    FROM Sheet1;

  11. #11
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    That worked perfectly! Thanks! My next ? is, how could I then convert this data to a table, so I could place it on my form?

  12. #12
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    First, save the query.

    Then select the query as the "record source" for your form.

  13. #13
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    What if I need to add, data from my original table to this new table. I.E. add data from table1 to the new CombinedField1_Field2 form?

  14. #14
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Is [table1] the same as [Sheet1]?

  15. #15
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Yes, table1 and sheet1 are the same. Sorry I should have stuck with the same naming convention.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 10-14-2010, 12:22 PM
  2. Combining multiple Excel records into Access
    By Jadey78 in forum Import/Export Data
    Replies: 0
    Last Post: 05-04-2010, 07:51 PM
  3. Combining fields
    By cotri in forum Forms
    Replies: 2
    Last Post: 01-18-2010, 12:06 PM
  4. combining fields into a single list
    By rich in forum Queries
    Replies: 4
    Last Post: 02-23-2009, 06:41 AM
  5. Replies: 1
    Last Post: 06-03-2006, 05:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums