none
Stuff a Character into a String Access 2007

    Question

  • How do I stuff a character into a string? In other words I have a string that looks like 2345. I want to insert a dash between the first two characters 23-45.

    Any help would be greatly appreciated.

    Sunday, June 23, 2013 11:00 AM

Answers

  • In the SQL statement you should ensure that the values being updated are at least 3 characters in length, e.g.

    UPDATE valid_numbers
    SET house_number = LEFT(house_number, 2) & " - " & MID(house_number, 3)
    WHERE LEN(house_number) >= 3;

    There seems little point in doing this externally by returning a reference to the database object as in your code.  Just execute the above 'update query' within the database in question.

    Ken Sheridan, Stafford, England

    Sunday, June 23, 2013 1:21 PM
  • There's a mistake in your code. It should be as follows:

    sq = "update valid_numbers set house_number = Left(house_number, 2) & ""-"" & Mid(house_number, 3);"

    To make the query work in any situation (no matter house_number length):

    sq = _

    "UPDATE valid_numbers SET valid_numbers.house_number = Switch(valid_numbers.house_number Is Null, Null, Len(valid_numbers.house_number)<3, valid_numbers.house_number, True,Left(valid_numbers.house_number,2) & '-' & Mid(valid_numbers.house_number,3));"

    P.S. Use apostrophes instead of parenthesis in SQL-strings. ;-)


    Vladimir Cvajniga


    Sunday, June 23, 2013 2:58 PM

All replies

  • Air code:

    strMyString = "2345"
    strMyString = Left(strMyString, 2) & "-" & Mid(strMyString, 3)
    MsgBox strMyString


    Vladimir Cvajniga

    Sunday, June 23, 2013 11:37 AM
  • I tried this code, but it doesn't seem to work:

    Sub Stuffx()

        Dim dbs As Database, rst As Recordset

        ' Modify this line to include the path to Northwind
        ' on your computer.
        Set dbs = OpenDatabase("C:\Users\GodFather\Documents\Century 21\Cole Directory\ColeDirectory.accdb")

        ' Stuff a dash in the house number field
        dbs.Execute "update valid_numbers set house_number = Left(house_number, 2) & " - " & Mid(house_number, 3);"

        
        dbs.Close

    End Sub

    Sunday, June 23, 2013 12:48 PM
  • In the SQL statement you should ensure that the values being updated are at least 3 characters in length, e.g.

    UPDATE valid_numbers
    SET house_number = LEFT(house_number, 2) & " - " & MID(house_number, 3)
    WHERE LEN(house_number) >= 3;

    There seems little point in doing this externally by returning a reference to the database object as in your code.  Just execute the above 'update query' within the database in question.

    Ken Sheridan, Stafford, England

    Sunday, June 23, 2013 1:21 PM
  • There's a mistake in your code. It should be as follows:

    sq = "update valid_numbers set house_number = Left(house_number, 2) & ""-"" & Mid(house_number, 3);"

    To make the query work in any situation (no matter house_number length):

    sq = _

    "UPDATE valid_numbers SET valid_numbers.house_number = Switch(valid_numbers.house_number Is Null, Null, Len(valid_numbers.house_number)<3, valid_numbers.house_number, True,Left(valid_numbers.house_number,2) & '-' & Mid(valid_numbers.house_number,3));"

    P.S. Use apostrophes instead of parenthesis in SQL-strings. ;-)


    Vladimir Cvajniga


    Sunday, June 23, 2013 2:58 PM