Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    48

    Unanswered: Concatinated fields from table 1 to single field in table 2

    Hi,

    I have posted this problem before, but now I am further along in finding a solution. I want to concatenate 3 text fields from a table and put the resultant string in a single text field in another table. This is what I have so far:
    '--------------------------------------------------------------------------
    Option Compare Database

    Public Function concat_fields()

    Dim rst As DAO.Recordset
    Dim db As DAO.Database
    Dim Hold_Fields As String

    Set db = CurrentDb
    Hold_Field = ""
    Set rst = db.OpenRecordset("Select tblworkingstock.Type FROM tblworkingstock INNER JOIN [Complete Parts List] ON tblworkingstock.ManufactPartNum = " & _
    "[Complete Parts List].[Vendor #2] WHERE tblworkingstock.ManufactPartNum = [Complete Parts List].[Vendor #2]")

    If rst!Type <> "" Then
    Hold_Field = rst!Type
    End If

    Set rst = db.OpenRecordset("Select tblworkingstock.Value1 FROM tblworkingstock INNER JOIN [Complete Parts List] ON tblworkingstock.ManufactPartNum = " & _
    "[Complete Parts List].[Vendor #2] WHERE tblworkingstock.ManufactPartNum = [Complete Parts List].[Vendor #2]")

    If rst!Value1 = "" Then
    Hold_Field = Hold_Field
    ElseIf Hold_Field = "" And rst!Value1 <> "" Then
    Hold_Field = rst!Value1
    ElseIf Hold_Field <> "" And rst!Value1 <> "" Then
    Hold_Field = Hold_Field & ", " & rst!Value1
    End If


    Set rst = db.OpenRecordset("Select tblworkingstock.Value2 FROM tblworkingstock INNER JOIN [Complete Parts List] ON tblworkingstock.ManufactPartNum = " & _
    "[Complete Parts List].[Vendor #2] WHERE tblworkingstock.ManufactPartNum = [Complete Parts List].[Vendor #2]")

    If rst!Value2 = "" Then
    Hold_Field = Hold_Field
    ElseIf Hold_Field = "" And rst!Value2 <> "" Then
    Hold_Field = rst!Value2
    ElseIf Hold_Field <> "" And rst!Value2 <> "" Then
    Hold_Field = Hold_Field & ", " & rst!Value2
    End If
    DoCmd****nSQL ("INSERT INTO [Complete Parts List](Description) SELECT Hold_Field.Value")

    End Function
    '--------------------------------------------------------------------------
    There are at least 2 problems with this code; first the DoCmd****nSQL line at the end is incorrect. I am trying to put the string Hold_Field into the field into the Description field of tabel Complete Parts List. What happens is that I am prompted to enter a value for Hold_Field. Hold_Field does have a correct value.

    The second problem is that I think I will have to make a loop in order to scan all of the records in table tblworkingstock and enter Hold_Field into the appropriate Complete Parts List Description field. To do this I would want to start at the first record and loop until the end of file. I haven't yet figured out a way to do this. As you can see from the code, I am new to using DAO stuff so there is probably a more efficient way to do even the parts that do work.

    I will appreciate any help on this.

    Thanks,

    Charles
    Last edited by cmelias; 06-24-09 at 10:52. Reason: typo

  2. #2
    Join Date
    Jun 2009
    Posts
    48

    Correction

    For some reason DoCmd***nSQL posts as DoCmd***nSQL even though I tried to edit it.

    Charles
    Last edited by cmelias; 06-24-09 at 10:58. Reason: DoCmd****nSQL does not post properly

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I take it you can't just design a simple query (append or update query) and use an expression to concatenate the fields together (ie. AllFields: [Field1] + " " + [Field2] + " " + [Field3] ....)

    or maybe its...
    AllFields: [Field1] & " " & [Field2] & " " & [Field3]
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    The stars are the lazy site coders who made a blanket replacement of DOT-R-U to *** to stop Russian site linking instead of just stopping DOT-R-U in URLs only.

    It definitely seems like you are going about this the very very hard way.

    One thing that jumps out at me here is this:

    "INSERT INTO [Complete Parts List](Description) SELECT Hold_Field.Value"

    should be more like

    "INSERT INTO [Complete Parts List] (Description) SELECT """ & Hold_Value & """ as Expr1"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2009
    Posts
    48

    concat

    All,

    Thanks for the replies.

    PK Stormy,

    I tried a couple of versions of that method and it doesn't work.

    StarTrekker,

    When I try the query as you suggested, I am prompted to enter a macro name. However, when I leave out 'as Expr1' it does make an entry into [Complete Parts List].Description. It makes the entry into a new record which is not my intent.

    Charles

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by cmelias
    StarTrekker,

    When I try the query as you suggested, I am prompted to enter a macro name. However, when I leave out 'as Expr1' it does make an entry into [Complete Parts List].Description. It makes the entry into a new record which is not my intent.
    Forgive me, I never work with macros beyond what I have to. I hate them.

    VBA all the way!

    Anyway, so leave "as Expr1" out of it.

    If you don't want a new record then INSERT INTO is wrong. Instead you need to change it to (guessing) an UPDATE query?

    If you need to edit a record, instead you could use something like this:

    Code:
    Set rst = db.OpenRecordset("SQLToGetTheRecordToModify")
    rst.movefirst
    rst.edit
    rst!Description= Hold_Value
    rst.update
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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