none
Making the last two Character(text) Capital without affecting the rest of the text. RRS feed

  • Question

  • Hello

    I am trying to make the last two right  text capital or upper case. I know this sounds simple and I have tried but when I use the Right Function, all other characters are deleted, that's because it returning the characters rather than making them upper case and leaving the in.

    That's were I am lost, how do I make the characters capital without affecting the rest using the Right Function.

    Thank you!

    Thursday, October 6, 2011 4:41 PM

Answers

  • Hi BL,

     

    To use this Function in any control you like, change it into this. Save it in a seperate Module in your Database.

     

    Public Function fixAbbreviation()
    
    Dim strNew As String
    Dim ctl As Control
    
    Set ctl = Screen.ActiveControl
    
    strNew = Left(ctl.value, Len(ctl.value) - 2) & UCase(Right(ctl.value, 2))
    
    ctl.value = strNew
    
    
    End Function
    
    -----------------

     

     

    Then in your AfterUpdate event of your TextBox you want to use this Function place this code:

     

    Private Sub ADR1_AfterUpdate()
    
    Call fixAbbreviation
    
    End Sub
    

     

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, October 6, 2011 5:57 PM
    Moderator

All replies

  • hi,
     
    w = Left(Range("A1"), Len(Range("A1")) - 2) & UCase(Right(Range("A1"), 2))
     --
    isabelle
     
    Le 2011-10-06 12:41, Boiling Lava a écrit :
    > Hello
    >
    > I am trying to make the last two right text capital or upper case. I know this sounds simple and I have tried but when I use the /*Right Function*/, all other characters are deleted, that's because it returning the characters rather than making them upper case and leaving the in.
    >
    > That's were I am lost, how do I make the characters capital without affecting the rest using the /*Right Function*/.
    >
    > Thank you!
    >
     
    Thursday, October 6, 2011 5:17 PM
  • Isabelle solution does the trick in Excel, when applying in Access, change a little, something like this:

    ' in SQL query
    strNew: Left([YourFieldName], Len([YourFieldName]) - 2) & UCase(Right([YourFieldName], 2))
    
    ' when in VBA on a Form
    Dim strNew As String
    
    strNew = Left(Me.YourFieldName, Len(Me.YourFieldName) - 2) & UCase(Right(Me.YourFieldName, 2))
    
    

    Hope that helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, October 6, 2011 5:27 PM
    Moderator
  • Isabelle solution does the trick in Excel, when applying in Access, change a little, something like this:

     

    ' in SQL query
    strNew: Left([YourFieldName], Len([YourFieldName]) - 2) & UCase(Right([YourFieldName], 2))
    
    ' when in VBA on a Form
    Dim strNew As String
    
    strNew = Left(Me.YourFieldName, Len(Me.YourFieldName) - 2) & UCase(Right(Me.YourFieldName, 2))
    
    

     

    Hope that helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Thanks, you are the best! I placed the entire line into a function and call the function from the control LostFocus but nothing happens.

    Public Function fixAbbreviation()
    Dim strNew As String
    strNew = Left(Me.ADR1, Len(Me.ADR1) - 2) & UCase(Right(Me.ADR1, 2))
    End Function
    
    
    Private Sub ADR1_LostFocus()
    
    fixAbbreviation
    
    End Sub


    Am I getting this right?

     

    Thanks again!

     

    BL


    Thursday, October 6, 2011 5:45 PM
  • Hi BL,

     

    To use this Function in any control you like, change it into this. Save it in a seperate Module in your Database.

     

    Public Function fixAbbreviation()
    
    Dim strNew As String
    Dim ctl As Control
    
    Set ctl = Screen.ActiveControl
    
    strNew = Left(ctl.value, Len(ctl.value) - 2) & UCase(Right(ctl.value, 2))
    
    ctl.value = strNew
    
    
    End Function
    
    -----------------

     

     

    Then in your AfterUpdate event of your TextBox you want to use this Function place this code:

     

    Private Sub ADR1_AfterUpdate()
    
    Call fixAbbreviation
    
    End Sub
    

     

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, October 6, 2011 5:57 PM
    Moderator
  • Hi BL,

     

    To use this Function in any control you like, change it into this. Save it in a seperate Module in your Database.

     

    Public Function fixAbbreviation()
    
    Dim strNew As String
    Dim ctl As Control
    
    Set ctl = Screen.ActiveControl
    
    strNew = Left(ctl.value, Len(ctl.value) - 2) & UCase(Right(ctl.value, 2))
    
    ctl.value = strNew
    
    
    End Function
    
    -----------------

     

     

    Then in your AfterUpdate event of your TextBox you want to use this Function place this code:

     

    Private Sub ADR1_AfterUpdate()
    
    Call fixAbbreviation
    
    End Sub
    

     

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Hello, this is working well, but I forgot to mentioned I only need to chage a specific string.

    Example: Lets say this was an adress: 7678 7th street nw

    I need to change nw to NW or ave to Ave, or nE to NE.

    Sorry for the confusion.

     

    Thanks a again.

     

    BL

    Thursday, October 6, 2011 6:17 PM
  • Hi BL,

    Hmmm, that might difficult to use such a Function for that purpose.

    You might be better of substract the address in more fields (better normalized data), like for example:

    txtAddress                                      '7th Street

    txtHousNr                                       ' 7678

    txtAddressDirection                        ' NW

     

    Then you can concatenate this to a full address in your Form, Report or Query:

     

    FullAddress = Trim(txtHousNr & " " & txtAddress & " " & txtAddressDirection)
    

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, October 6, 2011 6:29 PM
    Moderator
  • Hi BL,

     

    To use this Function in any control you like, change it into this. Save it in a seperate Module in your Database.

     

    Public Function fixAbbreviation()
    
    Dim strNew As String
    Dim ctl As Control
    
    Set ctl = Screen.ActiveControl
    
    strNew = Left(ctl.value, Len(ctl.value) - 2) & UCase(Right(ctl.value, 2))
    
    ctl.value = strNew
    
    
    End Function
    
    -----------------

     

     

    Then in your AfterUpdate event of your TextBox you want to use this Function place this code:

     

    Private Sub ADR1_AfterUpdate()
    
    Call fixAbbreviation
    
    End Sub
    

     

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Thanks for all your help!

    You are really making a difference.

    BL

    Thursday, October 6, 2011 6:51 PM
  • Hi BL,


    You are very welcome, glad we could help!

    As you marked my solution with a Function as answer, you might reconsider your design on how to store your Address value in your Table.

    That saves a lot of pain later! I know, I have been there .. ;)

     

    Cheers,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, October 6, 2011 6:54 PM
    Moderator
  • Hi BL,


    You are very welcome, glad we could help!

    As you marked my solution with a Function as answer, you might reconsider your design on how to store your Address value in your Table.

    That saves a lot of pain later! I know, I have been there .. ;)

     

    Cheers,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Yes, I marked it because it answered the origibal question but I really appreciate your input.

    BL

    Thursday, October 6, 2011 7:38 PM