VBA Code for Replace function

  • Thread starter Thread starter tbd
  • Start date Start date
T

tbd

Hi! I am trying to write code to create a Replace funtion
that I can use in one of my queries. I have been told
that the Replace function is actually a part of Service
Pack 3 from Microsoft, but I am having difficulty
downloading it. I was also kindly given code previously
but have somehow managed to lose it.

Basically, I have a table containing values like 1.036 and
2.111. I want to replace the points in these figures so
they are displayed as 1036 and 2111, and would like to do
this through a query. Can anyone help with the code to
create this function?

Thanks in advance
 
Is this a Text field or a Number field?

If Number, all with 3 digits, just type this into the Update row under the
MyField column:
[MyField] * 1000

If it is a Text field, and you want to remove all the dots:
MyReplace([MyField], ".", "")

The wrapper function is only needed for unpatched A2000:

Function MyReplace(strExpression As String, _
strFind As String, _
strReplace As String, _
Optional lngStart As Long = 1, _
Optional lngCount As Long = -1) As String

MyReplace = Replace(strExpression, strFind, strReplace, lngStart,
lngCount)
End Function
 
Thats worked perfectly, thank you very much Allen!!!


-----Original Message-----
Is this a Text field or a Number field?

If Number, all with 3 digits, just type this into the Update row under the
MyField column:
[MyField] * 1000

If it is a Text field, and you want to remove all the dots:
MyReplace([MyField], ".", "")

The wrapper function is only needed for unpatched A2000:

Function MyReplace(strExpression As String, _
strFind As String, _
strReplace As String, _
Optional lngStart As Long = 1, _
Optional lngCount As Long = -1) As String

MyReplace = Replace(strExpression, strFind, strReplace, lngStart,
lngCount)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I am trying to write code to create a Replace funtion
that I can use in one of my queries. I have been told
that the Replace function is actually a part of Service
Pack 3 from Microsoft, but I am having difficulty
downloading it. I was also kindly given code previously
but have somehow managed to lose it.

Basically, I have a table containing values like 1.036 and
2.111. I want to replace the points in these figures so
they are displayed as 1036 and 2111, and would like to do
this through a query. Can anyone help with the code to
create this function?

Thanks in advance
Click to expand...


.
Click to expand...
 
Back
Top