Replace Function

[Tamer Seoud]

Hi All,
I want to eliminate a space in a query field. I used the
following syntax from a cmd button event click, but even
though I don't get any error message, the space in the
field is still there when I open the query by using the
cmd button.
Please note that I tried to use
Replace([fieldname]," ","")in the query grid and didn't
work, I tried also to use an update query to eliminate the
space, but it didn't work either. It seemed that I can't
use the Replace function in a query, so I wrote the
following code and I named the function any name (but
Replace) because when I named it Replace, it returened an
error message.

Public Function Tamer(vField As Variant, ByVal strOld As
String, _
ByVal strNew As String) As String
Dim Temp As String, P As Long
Temp = vField
P = InStr(Temp, strOld)
Do While P > 0
Temp = Left(Temp, P - 1) & strNew & Mid(Temp, P + Len
(strOld))
P = InStr(P + Len(strNew), Temp, strOld, 1)
Loop
Tamer = Temp
End Function

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Dim StDocName As String
Dim Space As String
Dim NoSpace As String
Dim StFieldName As String
StDocName = "qry_FPNamesMismatch"
StFieldName = "qry_FPNamesMismatch!name"
Space = " "
NoSpace = ""
DoCmd.OpenQuery StDocName
Call Tamer(StFieldName, Space, NoSpace)
End Sub

What am I missing to eliminate the space? Please advise.
Thanks a million

 

[Dan Artuso]

Hi,
This is a known problem with the Replace function when used in a query.
Just use the Replace function inside your function.

Public Function Tamer(vField As Variant, ByVal strOld As String, _
ByVal strNew As String) As String

Tamer = Replace(vField,strOld,strNew)
End Function

Then call your function from the query grid.

 

[Tamer Seoud]

Dan,
Thank you very much, it worked finally after I tried your
great suggestion.
Best Regards,
Tamer Seoud

-----Original Message-----
Hi,
This is a known problem with the Replace function when used in a query.
Just use the Replace function inside your function.

Public Function Tamer(vField As Variant, ByVal strOld As String, _
ByVal strNew As String) As String

Tamer = Replace(vField,strOld,strNew)
End Function

Then call your function from the query grid.


--
HTH
Dan Artuso, Access MVP


"Tamer Seoud" <[email protected]> wrote

in message news:[email protected]...

Hi All,
I want to eliminate a space in a query field. I used the
following syntax from a cmd button event click, but even
though I don't get any error message, the space in the
field is still there when I open the query by using the
cmd button.
Please note that I tried to use
Replace([fieldname]," ","")in the query grid and didn't
work, I tried also to use an update query to eliminate the
space, but it didn't work either. It seemed that I can't
use the Replace function in a query, so I wrote the
following code and I named the function any name (but
Replace) because when I named it Replace, it returened an
error message.

Public Function Tamer(vField As Variant, ByVal strOld As
String, _
ByVal strNew As String) As String
Dim Temp As String, P As Long
Temp = vField
P = InStr(Temp, strOld)
Do While P > 0
Temp = Left(Temp, P - 1) & strNew & Mid(Temp, P + Len
(strOld))
P = InStr(P + Len(strNew), Temp, strOld, 1)
Loop
Tamer = Temp
End Function

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Dim StDocName As String
Dim Space As String
Dim NoSpace As String
Dim StFieldName As String
StDocName = "qry_FPNamesMismatch"
StFieldName = "qry_FPNamesMismatch!name"
Space = " "
NoSpace = ""
DoCmd.OpenQuery StDocName
Call Tamer(StFieldName, Space, NoSpace)
End Sub

What am I missing to eliminate the space? Please advise.
Thanks a million

.