Find&Replace

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Good morning.

I pull queries from main frame tables and use social
security numbers quite frequently. Redesigning the tables
is unfortunately not an option or I would do that. What I
am needing to do is find dashses in social security
numbers and replace them with a null value. I would like
to take a social in the format '000-00-000' and make it
look like '000000000'. The best place for me to do this
is in a query. How would you suggest I go about this.

Thank you for your help!
Sincerely
Ken
 
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version with updated SP version of
Jet.

Else write your own public function that uses the Replace function as noted
above and then call that function from your calculated field.
 
That is how I have been fixing this problem, but I am
looking for a more automated way to remove the dashes.

Do you have any other suggestions.
Thanks
Ken
 
I am getting this error with the public function.

'Compile Error:
Expected variable or procedure, not module'

My module is as follows:

Public Function RReplace(strStringValue As String,
strCurrString As String, strNewString As String)

RReplace = Replace(strStringValue, strCurrString,
strNewString)
End Function

The query expression is as follows:

Expr1: RReplace([Last],Mid([Last],InStr([Last],","),1),"")


Can you tell what I am doing wrong? Any help you can
provide will be greatly appreciated!!

Thank you, once again!
Ken


-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version with updated SP version of
Jet.

Else write your own public function that uses the Replace function as noted
above and then call that function from your calculated field.

--
Ken Snell
<MS ACCESS MVP>

Ken said:
Good morning.

I pull queries from main frame tables and use social
security numbers quite frequently. Redesigning the tables
is unfortunately not an option or I would do that. What I
am needing to do is find dashses in social security
numbers and replace them with a null value. I would like
to take a social in the format '000-00-000' and make it
look like '000000000'. The best place for me to do this
is in a query. How would you suggest I go about this.

Thank you for your help!
Sincerely
Ken
Click to expand...


.
Click to expand...
 
I think you named the module and the function the same. That is not allowed.
Try renaming the module to modRReplace.

I am getting this error with the public function.

'Compile Error:
Expected variable or procedure, not module'

My module is as follows:

Public Function RReplace(strStringValue As String,
strCurrString As String, strNewString As String)

RReplace = Replace(strStringValue, strCurrString,
strNewString)
End Function

The query expression is as follows:

Expr1: RReplace([Last],Mid([Last],InStr([Last],","),1),"")

Can you tell what I am doing wrong? Any help you can
provide will be greatly appreciated!!

Thank you, once again!
Ken
-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version with updated SP version of
Jet.

Else write your own public function that uses the Replace function as noted
above and then call that function from your calculated field.

--
Ken Snell
<MS ACCESS MVP>

Ken said:
Good morning.

I pull queries from main frame tables and use social
security numbers quite frequently. Redesigning the tables
is unfortunately not an option or I would do that. What I
am needing to do is find dashses in social security
numbers and replace them with a null value. I would like
to take a social in the format '000-00-000' and make it
look like '000000000'. The best place for me to do this
is in a query. How would you suggest I go about this.

Thank you for your help!
Sincerely
Ken
Click to expand...


.
Click to expand...
Click to expand...
 
I believe that John Spencer has pinpointed the error in your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

Ken said:
I am using Access '97 and I am not familiar with writing
public functions. I tried creating a module, but that did
not work. Is there any help you can provide with this
regard?

Thank you so much for your help!
Ken
-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version with updated SP version of
Jet.

Else write your own public function that uses the Replace function as noted
above and then call that function from your calculated field.

--
Ken Snell
<MS ACCESS MVP>

Ken said:
Good morning.

I pull queries from main frame tables and use social
security numbers quite frequently. Redesigning the tables
is unfortunately not an option or I would do that. What I
am needing to do is find dashses in social security
numbers and replace them with a null value. I would like
to take a social in the format '000-00-000' and make it
look like '000000000'. The best place for me to do this
is in a query. How would you suggest I go about this.

Thank you for your help!
Sincerely
Ken
Click to expand...


.
Click to expand...
Click to expand...
 
I am getting the same error. Any suggestions? Just not
sure where I am going wrong.

Thanks, again!
Ken
-----Original Message-----
I believe that John Spencer has pinpointed the error in your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

Ken said:
I am using Access '97 and I am not familiar with writing
public functions. I tried creating a module, but that did
not work. Is there any help you can provide with this
regard?

Thank you so much for your help!
Ken
-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version with updated SP version of
Jet.

Else write your own public function that uses the
Click to expand...
Replace
function as noted
above and then call that function from your calculated field.

--
Ken Snell
<MS ACCESS MVP>

Good morning.

I pull queries from main frame tables and use social
security numbers quite frequently. Redesigning the tables
is unfortunately not an option or I would do that. What I
am needing to do is find dashses in social security
numbers and replace them with a null value. I would like
to take a social in the format '000-00-000' and make it
look like '000000000'. The best place for me to do this
is in a query. How would you suggest I go about this.

Thank you for your help!
Sincerely
Ken


.
Click to expand...
Click to expand...


.
Click to expand...
 
Post what your expression and your function are now. Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

Ken said:
I am getting the same error. Any suggestions? Just not
sure where I am going wrong.

Thanks, again!
Ken
-----Original Message-----
I believe that John Spencer has pinpointed the error in your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

Ken said:
I am using Access '97 and I am not familiar with writing
public functions. I tried creating a module, but that did
not work. Is there any help you can provide with this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version with
updated SP version of
Jet.

Else write your own public function that uses the Replace
function as noted
above and then call that function from your calculated
field.

--
Ken Snell
<MS ACCESS MVP>

Good morning.

I pull queries from main frame tables and use social
security numbers quite frequently. Redesigning the
tables
is unfortunately not an option or I would do that.
What I
am needing to do is find dashses in social security
numbers and replace them with a null value. I would
like
to take a social in the format '000-00-000' and make it
look like '000000000'. The best place for me to do this
is in a query. How would you suggest I go about this.

Thank you for your help!
Sincerely
Ken


.
Click to expand...


.
Click to expand...
Click to expand...
 
Function in Module named: modReplace

Public Function Replace(strStringValue As String,
strCurrString As String, strNewString As String)

Replace = Replace(strStringValue, strCurrString,
strNewString)


End Function

Expression in query:

Replace([Last],Mid([Last],InStr([Last],","),1),"")

I have also tried:

Replace([Last],",","")

Thank you, once again, for all your help! I really
appreciate it!!

Ken
-----Original Message-----
Post what your expression and your function are now. Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

Ken said:
I am getting the same error. Any suggestions? Just not
sure where I am going wrong.

Thanks, again!
Ken
-----Original Message-----
I believe that John Spencer has pinpointed the error in your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

I am using Access '97 and I am not familiar with writing
public functions. I tried creating a module, but
Click to expand...
that
did
not work. Is there any help you can provide with this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version with
updated SP version of
Jet.

Else write your own public function that uses the Replace
function as noted
above and then call that function from your calculated
field.

--
Ken Snell
<MS ACCESS MVP>

Good morning.

I pull queries from main frame tables and use social
security numbers quite frequently. Redesigning the
tables
is unfortunately not an option or I would do that.
What I
am needing to do is find dashses in social security
numbers and replace them with a null value. I would
like
to take a social in the format '000-00-000' and
Click to expand...
make
it
look like '000000000'. The best place for me to
Click to expand...
do
this
is in a query. How would you suggest I go about this.

Thank you for your help!
Sincerely
Ken


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Your function now should look like this:
Public Function fReplace(strStringValue As String, strCurrString As
String, strNewString As String)
fReplace = Replace(strStringValue, strCurrString, strNewString)
End Function

This function should be in a regular module; name that regular module
basFunctions.

Your expression in the query should look like this:
fReplace([Last],Mid([Last],InStr([Last],","),1),"")

Is this what you now have? And this is not working?
--
Ken Snell
<MS ACCESS MVP>

Ken said:
I am still getting that same message. Frustrating, isn't
it??

Thanks,
Ken
-----Original Message-----
try renaming your function to fReplace. As it stands now, you are recursively
calling your replace function and not the built-in VBA function. So it should
just loop until you run out of stack space.

Public Function fReplace(strStringValue As String,
strCurrString As String, strNewString As String)

fReplace = Replace(strStringValue, strCurrString,
strNewString)


End Function

fReplace([Last],",","")
Function in Module named: modReplace

Public Function Replace(strStringValue As String,
strCurrString As String, strNewString As String)

Replace = Replace(strStringValue, strCurrString,
strNewString)

End Function

Expression in query:

Replace([Last],Mid([Last],InStr([Last],","),1),"")

I have also tried:

Replace([Last],",","")

Thank you, once again, for all your help! I really
appreciate it!!

Ken

-----Original Message-----
Post what your expression and your function are now.
Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

I am getting the same error. Any suggestions? Just not
sure where I am going wrong.

Thanks, again!
Ken

-----Original Message-----
I believe that John Spencer has pinpointed the error in
your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

I am using Access '97 and I am not familiar with
writing
public functions. I tried creating a module, but
that
did
not work. Is there any help you can provide with
this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version
with
updated SP version of
Jet.

Else write your own public function that uses the
Replace
function as noted
above and then call that function from your
calculated
field.

--
Ken Snell
<MS ACCESS MVP>

Good morning.

I pull queries from main frame tables and use
social
security numbers quite frequently. Redesigning
the
tables
is unfortunately not an option or I would do that.
What I
am needing to do is find dashses in social
security
numbers and replace them with a null value. I
would
like
to take a social in the format '000-00-000' and
make
it
look like '000000000'. The best place for me to
do
this
is in a query. How would you suggest I go about
this.

Thank you for your help!
Sincerely
Ken


.



.



.
Click to expand...
.
Click to expand...
Click to expand...
 
That is what i have and it is not working. It keeps
telling me Compile Error: sub or function not defined.
When the error appears the Public Function line is
highlighted in yellow and the ....Replace... part of the
second line is highlieghted in blue. Could it be an
update or something that I am missing?

Thanks, again, for all your help!
-----Original Message-----
Your function now should look like this:
Public Function fReplace(strStringValue As String, strCurrString As
String, strNewString As String)
fReplace = Replace(strStringValue, strCurrString, strNewString)
End Function

This function should be in a regular module; name that regular module
basFunctions.

Your expression in the query should look like this:
fReplace([Last],Mid([Last],InStr ([Last],","),1),"")

Is this what you now have? And this is not working?
--
Ken Snell
<MS ACCESS MVP>

Ken said:
I am still getting that same message. Frustrating, isn't
it??

Thanks,
Ken
-----Original Message-----
try renaming your function to fReplace. As it stands now, you are recursively
calling your replace function and not the built-in VBA function. So it should
just loop until you run out of stack space.

Public Function fReplace(strStringValue As String,
strCurrString As String, strNewString As String)

fReplace = Replace(strStringValue, strCurrString,
strNewString)


End Function

fReplace([Last],",","")

Ken wrote:

Function in Module named: modReplace

Public Function Replace(strStringValue As String,
strCurrString As String, strNewString As String)

Replace = Replace(strStringValue, strCurrString,
strNewString)

End Function

Expression in query:

Replace([Last],Mid([Last],InStr([Last],","),1),"")

I have also tried:

Replace([Last],",","")

Thank you, once again, for all your help! I really
appreciate it!!

Ken

-----Original Message-----
Post what your expression and your function are now.
Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

I am getting the same error. Any suggestions?
Click to expand...
Just
not
sure where I am going wrong.

Thanks, again!
Ken

-----Original Message-----
I believe that John Spencer has pinpointed the error in
your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

I am using Access '97 and I am not familiar with
writing
public functions. I tried creating a module, but
that
did
not work. Is there any help you can provide with
this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version
with
updated SP version of
Jet.

Else write your own public function that uses the
Replace
function as noted
above and then call that function from your
calculated
field.

--
Ken Snell
<MS ACCESS MVP>

Good morning.

I pull queries from main frame tables and use
social
security numbers quite frequently. Redesigning
the
tables
is unfortunately not an option or I would do that.
What I
am needing to do is find dashses in social
security
numbers and replace them with a null value. I
would
like
to take a social in the format '000-00-000' and
make
it
look like '000000000'. The best place for
Click to expand...
me
to
do
this
is in a query. How would you suggest I go about
this.

Thank you for your help!
Sincerely
Ken


.



.



.

.
Click to expand...
Click to expand...


.
Click to expand...
 
< knock to the side of my head! >

Ken did say this in his first reply post, didn't he....sorry for missing
that, Ken!

--
Ken Snell
<MS ACCESS MVP>

John Spencer (MVP) said:
The Replace function was introduced with Access 2000. You are using Access 97,
correct?

If that is the case, you will need to write the replace function from scratch in
VBA or simpler copy this one from

http://www.mvps.org/access/strings/str0004.htm


That is what i have and it is not working. It keeps
telling me Compile Error: sub or function not defined.
When the error appears the Public Function line is
highlighted in yellow and the ....Replace... part of the
second line is highlieghted in blue. Could it be an
update or something that I am missing?

Thanks, again, for all your help!
-----Original Message-----
Your function now should look like this:
Public Function fReplace(strStringValue As String, strCurrString As
String, strNewString As String)
fReplace = Replace(strStringValue, strCurrString, strNewString)
End Function

This function should be in a regular module; name that regular module
basFunctions.

Your expression in the query should look like this:
fReplace([Last],Mid([Last],InStr ([Last],","),1),"")

Is this what you now have? And this is not working?
--
Ken Snell
<MS ACCESS MVP>

I am still getting that same message. Frustrating, isn't
it??

Thanks,
Ken

-----Original Message-----
try renaming your function to fReplace. As it stands
now, you are recursively
calling your replace function and not the built-in VBA
function. So it should
just loop until you run out of stack space.

Public Function fReplace(strStringValue As String,
strCurrString As String, strNewString As String)

fReplace = Replace(strStringValue, strCurrString,
strNewString)


End Function

fReplace([Last],",","")

Ken wrote:

Function in Module named: modReplace

Public Function Replace(strStringValue As String,
strCurrString As String, strNewString As String)

Replace = Replace(strStringValue, strCurrString,
strNewString)

End Function

Expression in query:

Replace([Last],Mid([Last],InStr([Last],","),1),"")

I have also tried:

Replace([Last],",","")

Thank you, once again, for all your help! I really
appreciate it!!

Ken

-----Original Message-----
Post what your expression and your function are now.
Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

I am getting the same error. Any suggestions? Just
not
sure where I am going wrong.

Thanks, again!
Ken

-----Original Message-----
I believe that John Spencer has pinpointed the
error in
your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

I am using Access '97 and I am not familiar with
writing
public functions. I tried creating a module, but
that
did
not work. Is there any help you can provide with
this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "-", "")

This will work in ACCESS 2002 and in 2000 version
with
updated SP version of
Jet.

Else write your own public function that uses the
Replace
function as noted
above and then call that function from your
calculated
field.

--
Ken Snell
<MS ACCESS MVP>

message
Good morning.

I pull queries from main frame tables and use
social
security numbers quite frequently. Redesigning
the
tables
is unfortunately not an option or I would do
that.
What I
am needing to do is find dashses in social
security
numbers and replace them with a null value. I
would
like
to take a social in the format '000-00-000' and
make
it
look like '000000000'. The best place for me
to
do
this
is in a query. How would you suggest I go
about
this.

Thank you for your help!
Sincerely
Ken


.



.



.

.



.
Click to expand...
Click to expand...
Click to expand...
 
Can I call on this function in a query? If so, should I
just change the 'Function' to 'Public Function' or are
there changes I should make?

Thanks!
-----Original Message-----
The Replace function was introduced with Access 2000. You are using Access 97,
correct?

If that is the case, you will need to write the replace function from scratch in
VBA or simpler copy this one from

http://www.mvps.org/access/strings/str0004.htm


That is what i have and it is not working. It keeps
telling me Compile Error: sub or function not defined.
When the error appears the Public Function line is
highlighted in yellow and the ....Replace... part of the
second line is highlieghted in blue. Could it be an
update or something that I am missing?

Thanks, again, for all your help!
-----Original Message-----
Your function now should look like this:
Public Function fReplace(strStringValue As String, strCurrString As
String, strNewString As String)
fReplace = Replace(strStringValue,
Click to expand...
strCurrString,
strNewString)
End Function

This function should be in a regular module; name that regular module
basFunctions.

Your expression in the query should look like this:
fReplace([Last],Mid([Last],InStr ([Last],","),1),"")

Is this what you now have? And this is not working?
--
Ken Snell
<MS ACCESS MVP>

I am still getting that same message. Frustrating, isn't
it??

Thanks,
Ken

-----Original Message-----
try renaming your function to fReplace. As it stands
now, you are recursively
calling your replace function and not the built-in VBA
function. So it should
just loop until you run out of stack space.

Public Function fReplace(strStringValue As String,
strCurrString As String, strNewString As String)

fReplace = Replace(strStringValue, strCurrString,
strNewString)


End Function

fReplace([Last],",","")

Ken wrote:

Function in Module named: modReplace

Public Function Replace(strStringValue As String,
strCurrString As String, strNewString As String)

Replace = Replace(strStringValue, strCurrString,
strNewString)

End Function

Expression in query:

Replace([Last],Mid([Last],InStr([Last],","),1),"")

I have also tried:

Replace([Last],",","")

Thank you, once again, for all your help! I really
appreciate it!!

Ken

-----Original Message-----
Post what your expression and your function are now.
Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

I am getting the same error. Any suggestions? Just
not
sure where I am going wrong.

Thanks, again!
Ken

-----Original Message-----
I believe that John Spencer has pinpointed the
error in
your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

I am using Access '97 and I am not familiar with
writing
public functions. I tried creating a
Click to expand...
module,
but
that
did
not work. Is there any help you can provide with
this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated field:

SSNNoDash: Replace([SSNFieldName], "- ", "")

This will work in ACCESS 2002 and in 2000 version
with
updated SP version of
Jet.

Else write your own public function that
Click to expand...
uses
the
Replace
function as noted
above and then call that function from your
calculated
field.

--
Ken Snell
<MS ACCESS MVP>

message
[email protected]...
Good morning.

I pull queries from main frame tables and use
social
security numbers quite frequently. Redesigning
the
tables
is unfortunately not an option or I would do
that.
What I
am needing to do is find dashses in social
security
numbers and replace them with a null value. I
would
like
to take a social in the format '000-00-
Click to expand...
000'
and
make
it
look like '000000000'. The best place
Click to expand...
for
me
to
do
this
is in a query. How would you suggest I go
about
this.

Thank you for your help!
Sincerely
Ken


.



.



.

.



.
Click to expand...
Click to expand...
.
Click to expand...
 
Yes, you can call it from a query. Yes, make it a Public Function in a
regular module. Make sure that the name of the module is different from the
function name.

--
Ken Snell
<MS ACCESS MVP>

Ken said:
Can I call on this function in a query? If so, should I
just change the 'Function' to 'Public Function' or are
there changes I should make?

Thanks!
-----Original Message-----
The Replace function was introduced with Access 2000. You are using Access 97,
correct?

If that is the case, you will need to write the replace function from scratch in
VBA or simpler copy this one from

http://www.mvps.org/access/strings/str0004.htm


That is what i have and it is not working. It keeps
telling me Compile Error: sub or function not defined.
When the error appears the Public Function line is
highlighted in yellow and the ....Replace... part of the
second line is highlieghted in blue. Could it be an
update or something that I am missing?

Thanks, again, for all your help!

-----Original Message-----
Your function now should look like this:
Public Function fReplace(strStringValue As String,
strCurrString As
String, strNewString As String)
fReplace = Replace(strStringValue, strCurrString,
strNewString)
End Function

This function should be in a regular module; name that
regular module
basFunctions.

Your expression in the query should look like this:
fReplace([Last],Mid([Last],InStr
([Last],","),1),"")

Is this what you now have? And this is not working?
--
Ken Snell
<MS ACCESS MVP>

I am still getting that same message. Frustrating,
isn't
it??

Thanks,
Ken

-----Original Message-----
try renaming your function to fReplace. As it stands
now, you are recursively
calling your replace function and not the built-in VBA
function. So it should
just loop until you run out of stack space.

Public Function fReplace(strStringValue As String,
strCurrString As String, strNewString As String)

fReplace = Replace(strStringValue, strCurrString,
strNewString)


End Function

fReplace([Last],",","")

Ken wrote:

Function in Module named: modReplace

Public Function Replace(strStringValue As String,
strCurrString As String, strNewString As String)

Replace = Replace(strStringValue, strCurrString,
strNewString)

End Function

Expression in query:

Replace([Last],Mid([Last],InStr([Last],","),1),"")

I have also tried:

Replace([Last],",","")

Thank you, once again, for all your help! I really
appreciate it!!

Ken

-----Original Message-----
Post what your expression and your function are now.
Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

I am getting the same error. Any suggestions?
Just
not
sure where I am going wrong.

Thanks, again!
Ken

-----Original Message-----
I believe that John Spencer has pinpointed the
error in
your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

message
I am using Access '97 and I am not familiar
with
writing
public functions. I tried creating a module,
but
that
did
not work. Is there any help you can provide
with
this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated
field:

SSNNoDash: Replace([SSNFieldName], "- ", "")

This will work in ACCESS 2002 and in 2000
version
with
updated SP version of
Jet.

Else write your own public function that uses
the
Replace
function as noted
above and then call that function from your
calculated
field.

--
Ken Snell
<MS ACCESS MVP>

message
[email protected]...
Good morning.

I pull queries from main frame tables and
use
social
security numbers quite frequently.
Redesigning
the
tables
is unfortunately not an option or I would do
that.
What I
am needing to do is find dashses in social
security
numbers and replace them with a null
value. I
would
like
to take a social in the format '000-00- 000'
and
make
it
look like '000000000'. The best place for
me
to
do
this
is in a query. How would you suggest I go
about
this.

Thank you for your help!
Sincerely
Ken


.



.



.

.



.
Click to expand...
.
Click to expand...
Click to expand...
 
You're welcome.

Ken said:
Woohoo!! It worked!

Thank you SO much for all your help!

Ken
-----Original Message-----
Yes, you can call it from a query. Yes, make it a Public Function in a
regular module. Make sure that the name of the module is different from the
function name.

--
Ken Snell
<MS ACCESS MVP>

Ken said:
Can I call on this function in a query? If so, should I
just change the 'Function' to 'Public Function' or are
there changes I should make?

Thanks!
-----Original Message-----
The Replace function was introduced with Access 2000.
You are using Access 97,
correct?

If that is the case, you will need to write the replace
function from scratch in
VBA or simpler copy this one from

http://www.mvps.org/access/strings/str0004.htm



Ken wrote:

That is what i have and it is not working. It keeps
telling me Compile Error: sub or function not defined.
When the error appears the Public Function line is
highlighted in yellow and the ....Replace... part of the
second line is highlieghted in blue. Could it be an
update or something that I am missing?

Thanks, again, for all your help!

-----Original Message-----
Your function now should look like this:
Public Function fReplace(strStringValue As String,
strCurrString As
String, strNewString As String)
fReplace = Replace(strStringValue,
strCurrString,
strNewString)
End Function

This function should be in a regular module; name that
regular module
basFunctions.

Your expression in the query should look like this:
fReplace([Last],Mid([Last],InStr
([Last],","),1),"")

Is this what you now have? And this is not working?
--
Ken Snell
<MS ACCESS MVP>

I am still getting that same message. Frustrating,
isn't
it??

Thanks,
Ken

-----Original Message-----
try renaming your function to fReplace. As it
stands
now, you are recursively
calling your replace function and not the built- in
VBA
function. So it should
just loop until you run out of stack space.

Public Function fReplace(strStringValue As String,
strCurrString As String, strNewString As String)

fReplace = Replace(strStringValue, strCurrString,
strNewString)


End Function

fReplace([Last],",","")

Ken wrote:

Function in Module named: modReplace

Public Function Replace(strStringValue As String,
strCurrString As String, strNewString As String)

Replace = Replace(strStringValue, strCurrString,
strNewString)

End Function

Expression in query:

Replace([Last],Mid([Last],InStr ([Last],","),1),"")

I have also tried:

Replace([Last],",","")

Thank you, once again, for all your help! I
really
appreciate it!!

Ken

-----Original Message-----
Post what your expression and your function are
now.
Let's see what you are
using.

--
Ken Snell
<MS ACCESS MVP>

message
I am getting the same error. Any suggestions?
Just
not
sure where I am going wrong.

Thanks, again!
Ken

-----Original Message-----
I believe that John Spencer has pinpointed the
error in
your function setup.
Post back if not.
--
Ken Snell
<MS ACCESS MVP>

message
[email protected]...
I am using Access '97 and I am not familiar
with
writing
public functions. I tried creating a
module,
but
that
did
not work. Is there any help you can provide
with
this
regard?

Thank you so much for your help!
Ken

-----Original Message-----
Use the Replace function in a calculated
field:

SSNNoDash: Replace([SSNFieldName], "-
", "")

This will work in ACCESS 2002 and in 2000
version
with
updated SP version of
Jet.

Else write your own public function that
uses
the
Replace
function as noted
above and then call that function from your
calculated
field.

--
Ken Snell
<MS ACCESS MVP>

message
[email protected]...
Good morning.

I pull queries from main frame tables and
use
social
security numbers quite frequently.
Redesigning
the
tables
is unfortunately not an option or I
would do
that.
What I
am needing to do is find dashses in
social
security
numbers and replace them with a null
value. I
would
like
to take a social in the format '000- 00-
000'
and
make
it
look like '000000000'. The best place
for
me
to
do
this
is in a query. How would you suggest I
go
about
this.

Thank you for your help!
Sincerely
Ken


.



.



.

.



.

.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top