Solved Using Mid and Replace in the same line of code

[tgstephens]

Access experts,

I'm trying to write what I thought would be a simple line of code on the Lost Focus event to find out if the 34th character in a text box on a form is a period (.) and, if so, replace it with a comma (,). Sounds simple, right? Yeah....not so much. I've been trying several iterations of the below but nothing seems to work:

If Mid(Me.BLOCK35, 34, 1) = "." Then Replace(Me.BLOCK35, 34, 1), ",")

This, however DOES work but it requires the user to make the manual change. I want the change to be automatic and transparent to the user.

If Mid(Me!BLOCK35, 34, 1) = "." Then MsgBox "yada yada yada"
If Mid(Me!BLOCK35, 34, 1) = "." Then Me.BLOCK35.SetFocus
If Mid(Me!BLOCK35, 34, 1) = "." Then Me.BLOCK35.SelStart = 34

Any ideas? Thanks!!
Tommy Stephens

 

[Gasman]

Have you looked at the syntax for that function?

Replace function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[CJ_London]

presume there are multiple commas and you only want to replace the one at 34th character position, otherwise you would just have

Me.BLOCK35=Replace(Me.BLOCK35, ".",",")

so try

Me.BLOCK35=iif(Mid(Me.BLOCK35, 34, 1)=".",left(Me.BLOCK35,33) & "," & mid(Me.BLOCK35,35),Me.BLOCK35)

 

[oleronesoftwares]

You have to assign a new variable to Block35
If Mid(Me!BLOCK35, 34, 1) = "." Then
Me.BLOCK35=Left([BLOCK35],33)& "," & Mid([BLOCK35],35))
End if

 

[tgstephens]

YES!!! Thank you soooo much CJ_London!!!! Now I can have a beer and relax