Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Concatenating probs

    I want to concatenate a variable with a field in a recordset:

    x = 1
    rs1!SUBSCODE" & x & "

    But Access keeps complaining that 'Expected: end of statement'. Anyone know how I can format this properly? I'm sure I have done this before, but I can't for the life remeber how its done.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You don't need any quotes for that.

    Cstr(rs1!SUBSCODE & x)
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I tried that here:

    Code:
    If (rs1!SUBSCODE & x) <> "PLA" Then curTotalExPlatinum = curTotalExPlatinum + (rs1!Amt & x)
    But it still doesn't work

  4. #4
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I tried this too:

    Code:
    If Cstr(rs1!SUBSCODE & x) <> "PLA" Then curTotalExPlatinum = curTotalExPlatinum + Cstr(rs1!Amt & x)
    But still no joy.

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Anyone? This one is really urgent!

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    What you're doing at the end of the statement is addition and not concatenation, right?
    So it should be

    Code:
    If Cstr(rs1!SUBSCODE & x) <> "PLA" Then 
    curTotalExPlatinum = curTotalExPlatinum + (rs1!Amt + x)
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Sure, but its this part that Access complains about:

    Code:
    If Cstr(rs1!SUBSCODE & x) <> "PLA"

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I was just thinking, it looks like X is a numeric variable, therefore
    rs1!SUBSCODE & x
    will never = "PLA"

    Is access giving you an error message?
    What does it say?
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    X is just a counter, so I don't ahve to specify every instance of SUBSCODE (it goes SUBSCODE1 to SUBSCODE10). Therefore, rs1!SUBSCODE & x should = rs1!SUBSCODE1 and so on... SUBSCODE1 is a text field. I don't want to add x to rs1!SUBSCODE, I want to concatonate rs1!SUBSCODE with the value of x.
    Last edited by bcass; 03-04-05 at 09:00.

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    AHA! Now that's much clearer.

    What you want is:

    rs1("subscode" & X)

    I really should've seen that in the first post. Not enough coffee yet.
    Inspiration Through Fermentation

  11. #11
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Phew!! Cheers, that worked!

  12. #12
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    mmmmmmmmmmmmmmmmm coffee!

  13. #13
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Sorry to drag this up again, but I am having a similar problem:

    Code:
    Dim ctl As Control
    
    Set frm = Forms!frmEmployees
    
    For Each ctl In frm.Controls
        With ctl
            Select Case .ControlType
                Case acTextBox
                    .ControlSource = rs(Mid(ctl.Name, 4))
            End Select
        End With
    Next ctl
    The bit I am having probs with is the rs(Mid(ctl.Name, 4)) bit. Basically, all my controls are named exactly the same in the table as they are on the form, except the form controls have the 'txt' prefix. I want to remove this prefix using the Mid function so I can easily set the recordsource for all controls on a form. I am using an ADO connection rather than linked tables.

    I also tried this: CStr(rs!Mid(ctl.Name, 4)) and this CStr(rs(Mid(ctl.Name, 4))) but still no luck.

  14. #14
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    MID requires 3 arguments:
    1- The string
    2 - The position to start from
    3 - The ending position

    You are only using 2.

    What you really want to use is
    RIGHT$(ctl.Name,4)

    That assumes that all of your text boxes have 7 character names,
    like txtJuly. If you have one calle txtMarch, your going to end up with "arch"
    Inspiration Through Fermentation

  15. #15
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    edit: Managed to get it working now, but only by setting the field values to the relevant rs fields. Is it possible to assign the fields RecordSource with each corresponding field in the rs?
    Last edited by bcass; 03-10-05 at 11:56.

Posting Permissions

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