Replace Function

T

Tamer Seoud

I have a query in Access 97 that has a field containing a
space between the last and first name ex:Seoud, Tamer. I
want to eliminate the space. I realized that the replace
function will not work directly within the query, so I
made some changes to the Click event of the command button
that opens the query, the way to call the function. There
is something wrong with the declaration or with the logic
of the code. Can you please tell me what I'm missing here.
Thanks in advance.
The following are the VB code:-

Option Compare Database
Option Explicit
Public Function Replace(ByVal [qry_FPNamesMismatch]![NAME]
as field,ByVal _
" " as String,ByVal "" as String) As String

Dim Temp As String, P As Long
Temp = [qry_FPNamesMismatch]![NAME]
P = InStr(Temp, " ")
Do While P > 0
Temp = Left(Temp, P - 1) & "" & Mid(Temp, P + Len(" "))
P = InStr(P + Len(""), Temp, " ", 1)
Looop
Replace = Temp
End Function

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Dim StDocName As String
StDocName = "qry_FPNamesMismatch"
DoCmd.OpenQuery StDocName, acViewPreview, acEdit
call Replace (ByVal [qry_FPNamesMismatch]![NAME]as
field,ByVal _
" " as String,ByVal "" as String) As String

End Sub
 
J

John Vinson

I have a query in Access 97 that has a field containing a
space between the last and first name ex:Seoud, Tamer. I
want to eliminate the space. I realized that the replace
function will not work directly within the query, so I
made some changes to the Click event of the command button
that opens the query, the way to call the function. There
is something wrong with the declaration or with the logic
of the code. Can you please tell me what I'm missing here.
Thanks in advance.
The following are the VB code:-

Option Compare Database
Option Explicit
Public Function Replace(ByVal [qry_FPNamesMismatch]![NAME]
as field,ByVal _
" " as String,ByVal "" as String) As String
Click to expand...

This is the problem. You need to specify variable names, not the query
field reference or constants, and pass the query field when you call
the function.
Dim Temp As String, P As Long
Temp = [qry_FPNamesMismatch]![NAME]
P = InStr(Temp, " ")
Do While P > 0
Temp = Left(Temp, P - 1) & "" & Mid(Temp, P + Len(" "))
P = InStr(P + Len(""), Temp, " ", 1)
Looop
Replace = Temp
End Function
Click to expand...

E.g. replace the above by

Public Function Replace(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) ' using the optional arg
Loop
Replace = Temp
End Function

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Dim StDocName As String
StDocName = "qry_FPNamesMismatch"
DoCmd.OpenQuery StDocName, acViewPreview, acEdit
call Replace ([qry_FPNamesMismatch]![NAME]," " "")
End Sub

You may want to use a Recordset or simply run an Update query updating
[NAME] to Replace([Name], " ", "") instead of the second Sub.
 
G

Guest

Hi John,
Thank you for your reply. The code for the function itself
doesn't give me an error message "thanks to you", but now
I need to call the function from the event click. I tried
the following code but it's giving me an error message
saying "Expected Sub, Function or Property". The function
is defined as Public. What am I missing?

THERE IS NO PROBLEM WITH THE FOLLOWING PART

Public Function Replace(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)
Looop
Replace = Temp
End Function

THE PROBLEM IS IN THE FOLLOWING PART

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Dim StDocName As String
Dim Replace As String
Dim Space As String
Dim NoSpace As String
StDocName = "[qry_FPNamesMismatch]![NAME]"
Space= " "
NoSpace= ""
DoCmd.OpenQuery StDocName, acViewPreview, acEdit
Call Replace(StDocName, ByVal Space, ByVal NoSpace)
StDocName = Replace
End Sub

-----Original Message-----
I have a query in Access 97 that has a field containing a
space between the last and first name ex:Seoud, Tamer. I
want to eliminate the space. I realized that the replace
function will not work directly within the query, so I
made some changes to the Click event of the command button
that opens the query, the way to call the function. There
is something wrong with the declaration or with the logic
of the code. Can you please tell me what I'm missing here.
Thanks in advance.
The following are the VB code:-

Option Compare Database
Option Explicit
Public Function Replace(ByVal [qry_FPNamesMismatch]! [NAME]
as field,ByVal _
" " as String,ByVal "" as String) As String
Click to expand...

This is the problem. You need to specify variable names, not the query
field reference or constants, and pass the query field when you call
the function.
Dim Temp As String, P As Long
Temp = [qry_FPNamesMismatch]![NAME]
P = InStr(Temp, " ")
Do While P > 0
Temp = Left(Temp, P - 1) & "" & Mid(Temp, P + Len(" "))
P = InStr(P + Len(""), Temp, " ", 1)
Looop
Replace = Temp
End Function
Click to expand...

E.g. replace the above by

Public Function Replace(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) ' using the optional arg
Loop
Replace = Temp
End Function

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Dim StDocName As String
StDocName = "qry_FPNamesMismatch"
DoCmd.OpenQuery StDocName, acViewPreview, acEdit
call Replace ([qry_FPNamesMismatch]![NAME]," " "")
End Sub

You may want to use a Recordset or simply run an Update query updating
[NAME] to Replace([Name], " ", "") instead of the second Sub.



.
Click to expand...
 
J

John Vinson

THE PROBLEM IS IN THE FOLLOWING PART

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Click to expand...

Then don't do that part. Just run an Update query updating the field
to Replace([fieldname], " ", ""). You do not need, and SHOULD NOT
INCLUDE, the keyword Byval in a *call to* a function, for one thing!
 
T

Tamer Seoud

Thanks again for getting back to me.

Can you please be more specific and tell me how to run an
update query that updates the field to replace
([fieldname]," ",""). I know how to run an update query,
but I don't know what you meant by saying "Just run an
Update query updating the field to Replace
[fieldname], " ", "")". Do you mean call the replace
function from the query?? or just use the replace function
from within the query? (I tried the last and it didn't
work). That's why I thought of writing the code - that you
thankfully helped with- to perform the replce. Please
tell what you mean.

Second, to call the replace function from an event click
procedure, what is the syntax I shall use? Is it:-
Call Function Replace (arguments) OR
Call "Function Name" (arguments) OR something else?
when calling the function I'm still getting an error
message. Please Advise.
-----Original Message-----
THE PROBLEM IS IN THE FOLLOWING PART

Private Sub cmd_ViewQry_FPNamesMismatch_Click()
Click to expand...

Then don't do that part. Just run an Update query updating the field
to Replace([fieldname], " ", ""). You do not need, and SHOULD NOT
INCLUDE, the keyword Byval in a *call to* a function, for one thing!


.
Click to expand...
 
J

John Vinson

Thanks again for getting back to me.

Can you please be more specific and tell me how to run an
update query that updates the field to replace
([fieldname]," ",""). I know how to run an update query,
but I don't know what you meant by saying "Just run an
Update query updating the field to Replace
[fieldname], " ", "")". Do you mean call the replace
function from the query?? or just use the replace function
from within the query? (I tried the last and it didn't
work). That's why I thought of writing the code - that you
thankfully helped with- to perform the replce. Please
tell what you mean.
Click to expand...

The UPDATE query would be something like this in SQL view:

UPDATE [mytable]
SET [fieldname] = Replace([fieldname], " ", "")
WHERE [fieldname] LIKE "* *";

You can do this in the query grid by just typing

Replace([fieldname], " ", "")

on the Update To line underneath the field fieldname.
Second, to call the replace function from an event click
procedure, what is the syntax I shall use? Is it:-
Call Function Replace (arguments) OR
Call "Function Name" (arguments) OR something else?
when calling the function I'm still getting an error
message. Please Advise.
Click to expand...

You're making something very simple into something much more
difficult!

Select the command button in form design view. View its Properties.
Click the ... icon, and choose "Code Builder". Access will open the
VBA editor with a Sub and an End Sub line.

Within that Sub you can use an expression like

strNew = Replace(strOld, " ", "")

to remove all instances of a blank in the variable named strOld and
store the resulting string in the variable strNew.

It appears that you've somehow gotten into the mindset that the
Function statement at the beginning of the function call, and the call
to the function itself, should look the same. They should't. A
Function statement contains the names of the variables used in the
function (with, optionally, a ByRef or ByVal keyword); a call to a
function contains the values that you want to assign to those
variables (and should NOT contain the keywords). For example, if your
Function statement looks like

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

then you can use the variables named vField, strOld and strNew within
the function; you would call the function passing a Variant and two
Strings - either constants, or fieldnames in a Query, or form control
values in a Form event. For instance in a Query with a text field
ProductName you would just include

Replace([ProductName], "X", "Y")

to create a new text string containing whatever value is in the field
ProductName, with all X's replaced by Y's.
 
T

Tamer Seoud

Thank you for all your help, it worked.

Best Regards,
Tamer Seoud
-----Original Message-----
Thanks again for getting back to me.

Can you please be more specific and tell me how to run an
update query that updates the field to replace
([fieldname]," ",""). I know how to run an update query,
but I don't know what you meant by saying "Just run an
Update query updating the field to Replace
[fieldname], " ", "")". Do you mean call the replace
function from the query?? or just use the replace function
from within the query? (I tried the last and it didn't
work). That's why I thought of writing the code - that you
thankfully helped with- to perform the replce. Please
tell what you mean.
Click to expand...

The UPDATE query would be something like this in SQL view:

UPDATE [mytable]
SET [fieldname] = Replace([fieldname], " ", "")
WHERE [fieldname] LIKE "* *";

You can do this in the query grid by just typing

Replace([fieldname], " ", "")

on the Update To line underneath the field fieldname.
Second, to call the replace function from an event click
procedure, what is the syntax I shall use? Is it:-
Call Function Replace (arguments) OR
Call "Function Name" (arguments) OR something else?
when calling the function I'm still getting an error
message. Please Advise.
Click to expand...

You're making something very simple into something much more
difficult!

Select the command button in form design view. View its Properties.
Click the ... icon, and choose "Code Builder". Access will open the
VBA editor with a Sub and an End Sub line.

Within that Sub you can use an expression like

strNew = Replace(strOld, " ", "")

to remove all instances of a blank in the variable named strOld and
store the resulting string in the variable strNew.

It appears that you've somehow gotten into the mindset that the
Function statement at the beginning of the function call, and the call
to the function itself, should look the same. They should't. A
Function statement contains the names of the variables used in the
function (with, optionally, a ByRef or ByVal keyword); a call to a
function contains the values that you want to assign to those
variables (and should NOT contain the keywords). For example, if your
Function statement looks like

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

then you can use the variables named vField, strOld and strNew within
the function; you would call the function passing a Variant and two
Strings - either constants, or fieldnames in a Query, or form control
values in a Form event. For instance in a Query with a text field
ProductName you would just include

Replace([ProductName], "X", "Y")

to create a new text string containing whatever value is in the field
ProductName, with all X's replaced by Y's.


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replace Function 2
Deliberately Breaking Linked Tables Using Code 5
help with replace function 5
api 2
loop for replace function 2
loop on replace function 2
Command Button Not Executing 24
listbox align right 1

Top