Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    combine fields (office 2003)

    Is it possible to combine two fields in a table and then query the result? I have three fields - Firstname, Middlename and lastname. The VB6 form has only two text boxes Firstname and Lastname. I would like to combine Firstname & ' ' & Lastname, call it First so the form can be loaded from Access so that First is loaded into the Firstname textbox and Lastname loaded to Lastname Textbox.

    The combination can be done in a query but the problem for me is how to address the query instead of the fields.
    Thanks

    Kim

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

    Re: combine fields (office 2003)

    I'm sorry, I find your question confusing. Why would you want to load First (=the concatenation of Firstname and Lastname) into the Firstname text box, and Lastname into the Lastname text box?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine fields (office 2003)

    Ooops. That should read Firstname & ' ' & Lastname.
    Thanks

    Kim

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

    Re: combine fields (office 2003)

    Sorry, that appears to be the same as before. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine fields (office 2003)

    Seems that my keyboard is not responding. This time Firstname & ' ' & Middlename
    Thanks

    Kim

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

    Re: combine fields (office 2003)

    You can set the RecordSource property of your Data control to a SQL statement, so it shouldn't be a probelem to define the First field in this SQL statement, or to refer to a stored query that contains the First field.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine fields (office 2003)

    Hans,

    Thanks for the reply. This appears to be dealing with a bound control? If so, How to deal with unbound?
    Thanks

    Kim

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

    Re: combine fields (office 2003)

    How do you populate unbound text boxes now?

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine fields (office 2003)

    sSql = "SELECT Firstname, Middlename, Lastname FROM Members WHERE Lastname = '" & sLastname & "'"
    rs.Open sSql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

    txtlastname = rs.Fields("Lastname").Value
    txtFirstname = rs.Fields("Firstname").Value

    rs.close
    cn.close
    Thanks

    Kim

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

    Re: combine fields (office 2003)

    Does the following work?
    <code>
    sSql = "SELECT Firstname & " " & Middlename AS First, Lastname FROM Members WHERE Lastname = '" & sLastname & "'"
    rs.Open sSql, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

    txtLastname = rs.Fields("Lastname").Value
    txtFirstname = rs.Fields("First").Value

    rs.Close
    cn.Close</code>

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine fields (office 2003)

    Hans,

    Thanks for the reply. Yes it will work. Unfortunately, I have not described the situation very accurately. Let me try again.

    There are two tables, table1 and table2. Data from table2 is to be exported to table1. Table1 can not (should not) be changed. Table1 has two fields, firstname and lastname. Table2 has three fields, firstname, middlename and lastname. Table2 fields can be manipulated to export only two fields to table1 by combining the data in fields firstname and middlename.

    Is it possible to concatenate firstname and middlename of table2, rename the field as firstname and then export all the data as just two fields, firstname and lastname?
    Thanks

    Kim

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combine fields (office 2003)

    You can do the concatenation in the APPEND query to Table2 or in an UPDATE query to Table1 without renaming any fields. How are you presently exporting (or planning to export) the data to Table2?
    Charlotte

  13. #13
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine fields (office 2003)

    Once again I need to clarify. the two tables are in separate .mdb's table2 in db2 will export the concatenated fields, firstname & ' ' & middlename, lastname to table1 db1.
    Thanks

    Kim

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combine fields (office 2003)

    Explain exactly what you need to do and how you intend to do it, including all those niggling details. As I understand it, you want to append reformatted data from a table in one database to a table in another database where the table structures don't quite match. Must this be an export from one database rather than an import from the other, or is that just the way you're approaching it?
    Charlotte

  15. #15
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: combine fields (office 2003)

    There are 2 db's with 2 tables. db1 has 2 fields, db2 has 3 fields. fields for db1 are firstname, lastname. fields for table2 are firsntame, middlename, lastname. I would like to import data from db2 into db1 using all data from all fields in this structure - From db2 firstname & ' ' & middlename to firstname in db1, lastname from db2 to lastname to db1
    Thanks

    Kim

Page 1 of 2 12 LastLast

Posting Permissions

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