Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Concatenate strings from different tables

Discussion in 'Information Technology' started by Glen T, Jul 28, 2006.

  1. Glen T

    Glen T
    Expand Collapse
    Guest

    I hope someone is able to help me with this. I have three related
    tables: Connections (tbl_CNX), Systems (tbl_SYS), and Components
    (tbl_CMP). These tables have each been set up with a description
    field. I have created a form that uses comboboxes to identify
    interfaces by connections between components in different systems.
    Everything is working great.

    My problem is this: I need to grab the descriptions from each table
    and concatenate the descriptions into one string. Here's an example:
    tbl_CNX.Conn_Type = VDC
    tbl_CNX.Conn_Description = "Voltage DC"
    tbl_SYS.ID = 1
    tbl_SYS.Description = "Engine Compartment"
    tbl_SYS.ID = 2
    tbl_SYS.Description = "Surveillance System"
    tbl_CMP.ID = A
    tbl_CMP.Description = "Generator"
    tbl_CMP.ID = B
    tbl_CMP.Description = "Power Converter"

    Let's say there is a VDC connection between System 1 Component A and
    System 2 Component B. The key on my form would look like this
    VAC.1A.2B and my desired description would read "Voltage DC connection
    from Engine Compartment Generator to Surveillance System Power
    Converter".

    I have tried to use SQL to accomplish this but have had difficulty
    since i am not very experienced in that language. I would prefer to be
    able to assign a string variable to the descriptions that match the
    type and ID's but I'm not sure how to go about this either. If I could
    get the descriptions into a series of string variables, I could easily
    accomplish what I need. Thanks for your time and any help you can
    provide.
     
  2. Glen T

    Glen T
    Expand Collapse
    Guest

    Still hoping for help. Just to further clarify, is there a way to
    assign a value to a string variable based in VBA logic? For example:

    Dim str_cnx as string
    Dim strSQL as string

    strSQL = "SELECT tbl_CNX.Conn_Description FROM tbl_CNX "
    strSQL = strSQL & "WHERE tbl_CNX.Conn_Type = FORM_CNX.conn_type"

    str_cnx = Docmd.RunSQL (strSQL)

    I know this doesn't work, but this gives a pretty good explanation of
    what I am trying to accomplish. Again, thanks for any help.
     
  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
    "Conn_Type = " & Forms!FORM_CNX!conn_type)

    This assumes that a) Conn_Type is a numeric field and b) form FORM_CNX is
    open when you run this.

    If Conn_Type is text, use:

    str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
    "Conn_Type = '" & Forms!FORM_CNX!conn_type & "'")

    or

    str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
    "Conn_Type = " Chr$(34) & Forms!FORM_CNX!conn_type & Chr$(34))



    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Glen T" <gwelsh@gdrs.com> wrote in message
    news:1152102352.196891.62080@j8g2000cwa.googlegroups.com...
    > Still hoping for help. Just to further clarify, is there a way to
    > assign a value to a string variable based in VBA logic? For example:
    >
    > Dim str_cnx as string
    > Dim strSQL as string
    >
    > strSQL = "SELECT tbl_CNX.Conn_Description FROM tbl_CNX "
    > strSQL = strSQL & "WHERE tbl_CNX.Conn_Type = FORM_CNX.conn_type"
    >
    > str_cnx = Docmd.RunSQL (strSQL)
    >
    > I know this doesn't work, but this gives a pretty good explanation of
    > what I am trying to accomplish. Again, thanks for any help.
    >
     
  4. Glen T

    Glen T
    Expand Collapse
    Guest

    Thank you Douglas


    Douglas J Steele wrote:
    > str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
    > "Conn_Type = " & Forms!FORM_CNX!conn_type)
    >
    > This assumes that a) Conn_Type is a numeric field and b) form FORM_CNX is
    > open when you run this.
    >
    > If Conn_Type is text, use:
    >
    > str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
    > "Conn_Type = '" & Forms!FORM_CNX!conn_type & "'")
    >
    > or
    >
    > str_cnx = DLookup("[Conn_Description]", "[tbl_CNX]", _
    > "Conn_Type = " Chr$(34) & Forms!FORM_CNX!conn_type & Chr$(34))
    >
    >
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Glen T" <gwelsh@gdrs.com> wrote in message
    > news:1152102352.196891.62080@j8g2000cwa.googlegroups.com...
    > > Still hoping for help. Just to further clarify, is there a way to
    > > assign a value to a string variable based in VBA logic? For example:
    > >
    > > Dim str_cnx as string
    > > Dim strSQL as string
    > >
    > > strSQL = "SELECT tbl_CNX.Conn_Description FROM tbl_CNX "
    > > strSQL = strSQL & "WHERE tbl_CNX.Conn_Type = FORM_CNX.conn_type"
    > >
    > > str_cnx = Docmd.RunSQL (strSQL)
    > >
    > > I know this doesn't work, but this gives a pretty good explanation of
    > > what I am trying to accomplish. Again, thanks for any help.
    > >
     

Share This Page