Macro command equal to VBA replace function (1 Viewer)

kengooch

New member
Local time
Yesterday, 21:07
Joined
Feb 29, 2012
Messages
9
I need a Macro command so I can check a form field for "," and replace it with ", "
Thanks in advance for your help!
 

vba_php

Forum Troll
Local time
Yesterday, 23:07
Joined
Oct 6, 2019
Messages
2,884
there isn't one. why can't you do it with code? not to mention that this question is very unusual. I don't think I've ever seen it asked before. although people *have* asked it regarding excel sheets. but the bigger question is...why are you doing it? are you preparing to parse the box values out using a delimiter or something?
 

theDBguy

I’m here to help
Local time
Yesterday, 21:07
Joined
Oct 29, 2018
Messages
10,258
Hi. Have you tried the Set Value action?

Sent from phone...
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 06:07
Joined
Apr 27, 2015
Messages
2,860
KenGooch,

Have you considered using an update query in conjunction with the Replace() Function? The format would be:
Replace ( [FieldName], ",", ", ")
 

kengooch

New member
Local time
Yesterday, 21:07
Joined
Feb 29, 2012
Messages
9
there isn't one. why can't you do it with code? not to mention that this question is very unusual. I don't think I've ever seen it asked before. although people *have* asked it regarding excel sheets. but the bigger question is...why are you doing it? are you preparing to parse the box values out using a delimiter or something?
So, we use the Database to track info from our Medical Records system, that doesn't have a way to track this particular issue. We block and copy the name from the MRS to the Database and it put's a comma with no space between the lastname,firstname. For the ProperCase function to work there has to be a space. As to writing the VBA code, I did. but I was hoping to put it in a macro, they seem cleaner and faster. Here is the VBA code I wrote that works perfectly.

Private Sub tPatient_AfterUpdate()
If InStr(tPatient, ", ") Then
tPatient.Value = StrConv(tPatient, vbProperCase)
Else
tPatient.Value = Replace(tPatient, ",", ", ")
tPatient.Value = StrConv(tPatient, vbProperCase)
End If
End Sub
 

kengooch

New member
Local time
Yesterday, 21:07
Joined
Feb 29, 2012
Messages
9
I saw that as a possibility, but for some reason I'm not sure how to link a form field to a query, or perhaps I should have just written it into the underlying query upon which the form is built... I guess I just haven't thought it through. My first thought is usually VBA then Macros... Thanks so much for the help!
 

vba_php

Forum Troll
Local time
Yesterday, 23:07
Joined
Oct 6, 2019
Messages
2,884
yeah. i don't know all the macro actions, as I never use macros. i just know a part of the list. don't even remember setValue() at this point. take care Ken. nice work, guy. =)
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom