Solved Using Mid and Replace in the same line of code (1 Viewer)

tgstephens

New member
Local time
Today, 00:21
Joined
Sep 15, 2010
Messages
3
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 07:21
Joined
Feb 19, 2013
Messages
16,553
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

Passionate Learner
Local time
Today, 00:21
Joined
Sep 22, 2014
Messages
1,159
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

New member
Local time
Today, 00:21
Joined
Sep 15, 2010
Messages
3
YES!!! Thank you soooo much CJ_London!!!! Now I can have a beer and relax :)
 

Users who are viewing this thread

Top Bottom