VBA Code for Replace function

[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

 

[Allen Browne]

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

 

[tbd]

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

.