using result of REPLACE function as the criteria for query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Has anyone used this function in Access 2000? trying to
strip out 4 periods which appear randomly similar to a
dotted decimal IP address. Objective is to use create a
table with the result of the REPLACE function...

Function name is stripPeriod

query:
newTable:stripPeriod(datawithPeriods)
---
Option Compare Database
Option Explicit


Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "")
End Function
 
using result of REPLACE function as the criteria for
query/further explanation: field data coming in as:

datawithPeriods 234.3356.339.2177 (ex)
 
Hi chaos,

You might try

Public Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "",1,-1,1)
End Function

I suspect the lack of of "Public" was
the problem.

I also always use the "full version" of
Replace in case db finds itself in Win98
environment.

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
outstanding advise and made changes but I am still
getting "Undefined function 'stripPeriod' in expression".

Is this function supported in Access 2000? or ???

My actual calling query is:

newhCode: stripPeriod([harmonized_code]) (in the Field
row.) (is this format correct?)

harmonized_code is the name of the field established from
a Make table query. when I execute this Select query I
receive the above error.

Thank you very much Walter!
 
Hi chaos,

First...is it possible you named your
code module "stripPeriod" also?

You went to Modules and either opened
a previous code module or clicked on "New."

You typed in the public function.

If it was a new module, you ended up with
code that looked like:

Option Compare Database
Option Explicit

Public Function stripPeriod(pstring As Variant) As String
On Error GoTo Err_stripPeriod

If Len(Trim(pstring & "")) > 0 Then
stripPeriod = Replace(pstring, ".", "", 1, -1, 1)
Else
stripPeriod = vbNullString
End If

Exit_stripPeriod:
Exit Function

Err_stripPeriod:
MsgBox Err.Description
Resume Exit_stripPeriod

End Function


You then clicked on "Save" icon.

If it was a new code module, you were asked
to give the module a name and you typed in
something like "modUtilities"
(not "stripPeriod").

You then clicked on the top menu item "Debug"
and chose to "Compile" which completed without
error.

Then in the immediate window at the bottom of
the debug window, you tested the function

?stripPeriod("234.3356.339.2177")
23433563392177

If all above is true, then I guess we'll have
to look deeper.

Good luck,

Gary Walter




outstanding advise and made changes but I am still
getting "Undefined function 'stripPeriod' in expression".

Is this function supported in Access 2000? or ???

My actual calling query is:

newhCode: stripPeriod([harmonized_code]) (in the Field
row.) (is this format correct?)

harmonized_code is the name of the field established from
a Make table query. when I execute this Select query I
receive the above error.

Thank you very much Walter!
-----Original Message-----
Hi chaos,

You might try

Public Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "",1,-1,1)
End Function

I suspect the lack of of "Public" was
the problem.

I also always use the "full version" of
Replace in case db finds itself in Win98
environment.

Please respond back if I have misunderstood.

Good luck,

Gary Walter





.
Click to expand...
Click to expand...
 
Gary, again, outstanding feedback....I decided to run your
code since it looked cleaner and received a I ran your
code and received

"compile error Expected variable or procedure,not module"

I must be making an incredibly stupid and obvious mistake.
I will rerun my code and post results.Note I recreated
your code in a new test dB and created both stripPeriod
and modUtilities Module, compiled, ran test and received
above compile error with each separate function.

-----Original Message-----
Hi chaos,

First...is it possible you named your
code module "stripPeriod" also?

You went to Modules and either opened
a previous code module or clicked on "New."

You typed in the public function.

If it was a new module, you ended up with
code that looked like:

Option Compare Database
Option Explicit

Public Function stripPeriod(pstring As Variant) As String
On Error GoTo Err_stripPeriod

If Len(Trim(pstring & "")) > 0 Then
stripPeriod = Replace(pstring, ".", "", 1, -1, 1)
Else
stripPeriod = vbNullString
End If

Exit_stripPeriod:
Exit Function

Err_stripPeriod:
MsgBox Err.Description
Resume Exit_stripPeriod

End Function


You then clicked on "Save" icon.

If it was a new code module, you were asked
to give the module a name and you typed in
something like "modUtilities"
(not "stripPeriod").

You then clicked on the top menu item "Debug"
and chose to "Compile" which completed without
error.

Then in the immediate window at the bottom of
the debug window, you tested the function

?stripPeriod("234.3356.339.2177")
23433563392177

If all above is true, then I guess we'll have
to look deeper.

Good luck,

Gary Walter




outstanding advise and made changes but I am still
getting "Undefined function 'stripPeriod' in expression".

Is this function supported in Access 2000? or ???

My actual calling query is:

newhCode: stripPeriod([harmonized_code]) (in the Field
row.) (is this format correct?)

harmonized_code is the name of the field established from
a Make table query. when I execute this Select query I
receive the above error.

Thank you very much Walter!
-----Original Message-----
Hi chaos,

You might try

Public Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "",1,-1,1)
End Function

I suspect the lack of of "Public" was
the problem.

I also always use the "full version" of
Replace in case db finds itself in Win98
environment.

Please respond back if I have misunderstood.

Good luck,

Gary Walter


using result of REPLACE function as the criteria for
query/further explanation: field data coming in as:

datawithPeriods 234.3356.339.2177 (ex)


-----Original Message-----
Has anyone used this function in Access 2000? trying to
strip out 4 periods which appear randomly similar to a
dotted decimal IP address. Objective is to use create a
table with the result of the REPLACE function...

Function name is stripPeriod

query:
newTable:stripPeriod(datawithPeriods)
---
Option Compare Database
Option Explicit


Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "")
End Function
----

??? Error is 'Undefined Function'

Thanks!
.



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


.
Click to expand...
 
I did name it stripPeriod and am recieving same compile
error with mine "expected variableor procedure not
module....

-----Original Message-----
Hi chaos,

First...is it possible you named your
code module "stripPeriod" also?

You went to Modules and either opened
a previous code module or clicked on "New."

You typed in the public function.

If it was a new module, you ended up with
code that looked like:

Option Compare Database
Option Explicit

Public Function stripPeriod(pstring As Variant) As String
On Error GoTo Err_stripPeriod

If Len(Trim(pstring & "")) > 0 Then
stripPeriod = Replace(pstring, ".", "", 1, -1, 1)
Else
stripPeriod = vbNullString
End If

Exit_stripPeriod:
Exit Function

Err_stripPeriod:
MsgBox Err.Description
Resume Exit_stripPeriod

End Function


You then clicked on "Save" icon.

If it was a new code module, you were asked
to give the module a name and you typed in
something like "modUtilities"
(not "stripPeriod").

You then clicked on the top menu item "Debug"
and chose to "Compile" which completed without
error.

Then in the immediate window at the bottom of
the debug window, you tested the function

?stripPeriod("234.3356.339.2177")
23433563392177

If all above is true, then I guess we'll have
to look deeper.

Good luck,

Gary Walter




outstanding advise and made changes but I am still
getting "Undefined function 'stripPeriod' in expression".

Is this function supported in Access 2000? or ???

My actual calling query is:

newhCode: stripPeriod([harmonized_code]) (in the Field
row.) (is this format correct?)

harmonized_code is the name of the field established from
a Make table query. when I execute this Select query I
receive the above error.

Thank you very much Walter!
-----Original Message-----
Hi chaos,

You might try

Public Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "",1,-1,1)
End Function

I suspect the lack of of "Public" was
the problem.

I also always use the "full version" of
Replace in case db finds itself in Win98
environment.

Please respond back if I have misunderstood.

Good luck,

Gary Walter


using result of REPLACE function as the criteria for
query/further explanation: field data coming in as:

datawithPeriods 234.3356.339.2177 (ex)


-----Original Message-----
Has anyone used this function in Access 2000? trying to
strip out 4 periods which appear randomly similar to a
dotted decimal IP address. Objective is to use create a
table with the result of the REPLACE function...

Function name is stripPeriod

query:
newTable:stripPeriod(datawithPeriods)
---
Option Compare Database
Option Explicit


Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "")
End Function
----

??? Error is 'Undefined Function'

Thanks!
.



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


.
Click to expand...
 
Gary, it worked! I understand now...it did not work given
use of same name for module and function just as you said
and confirmedby Microsoft knowledge base article
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;210098
so, now the big question is whether this will work within
my query vs. the 'Immediate window test'...

I cannot thank you enough but stay with me on this as
getting this query to work will be a huge accomplishment!

regards...
-----Original Message-----
Hi chaos,

First...is it possible you named your
code module "stripPeriod" also?

You went to Modules and either opened
a previous code module or clicked on "New."

You typed in the public function.

If it was a new module, you ended up with
code that looked like:

Option Compare Database
Option Explicit

Public Function stripPeriod(pstring As Variant) As String
On Error GoTo Err_stripPeriod

If Len(Trim(pstring & "")) > 0 Then
stripPeriod = Replace(pstring, ".", "", 1, -1, 1)
Else
stripPeriod = vbNullString
End If

Exit_stripPeriod:
Exit Function

Err_stripPeriod:
MsgBox Err.Description
Resume Exit_stripPeriod

End Function


You then clicked on "Save" icon.

If it was a new code module, you were asked
to give the module a name and you typed in
something like "modUtilities"
(not "stripPeriod").

You then clicked on the top menu item "Debug"
and chose to "Compile" which completed without
error.

Then in the immediate window at the bottom of
the debug window, you tested the function

?stripPeriod("234.3356.339.2177")
23433563392177

If all above is true, then I guess we'll have
to look deeper.

Good luck,

Gary Walter




outstanding advise and made changes but I am still
getting "Undefined function 'stripPeriod' in expression".

Is this function supported in Access 2000? or ???

My actual calling query is:

newhCode: stripPeriod([harmonized_code]) (in the Field
row.) (is this format correct?)

harmonized_code is the name of the field established from
a Make table query. when I execute this Select query I
receive the above error.

Thank you very much Walter!
-----Original Message-----
Hi chaos,

You might try

Public Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "",1,-1,1)
End Function

I suspect the lack of of "Public" was
the problem.

I also always use the "full version" of
Replace in case db finds itself in Win98
environment.

Please respond back if I have misunderstood.

Good luck,

Gary Walter


using result of REPLACE function as the criteria for
query/further explanation: field data coming in as:

datawithPeriods 234.3356.339.2177 (ex)


-----Original Message-----
Has anyone used this function in Access 2000? trying to
strip out 4 periods which appear randomly similar to a
dotted decimal IP address. Objective is to use create a
table with the result of the REPLACE function...

Function name is stripPeriod

query:
newTable:stripPeriod(datawithPeriods)
---
Option Compare Database
Option Explicit


Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "")
End Function
----

??? Error is 'Undefined Function'

Thanks!
.



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


.
Click to expand...
 
First...is it possible you named your
code module "stripPeriod" also?
Click to expand...
EXACTLY RIGHT!


Gary...MY QUERY WORKED!!! YOU ARE THE MAN!!!

I cannot thank you enough sir...I would like to stay in
touch with you if you do not mind...

-----Original Message-----
Hi chaos,

First...is it possible you named your
code module "stripPeriod" also?

You went to Modules and either opened
a previous code module or clicked on "New."

You typed in the public function.

If it was a new module, you ended up with
code that looked like:

Option Compare Database
Option Explicit

Public Function stripPeriod(pstring As Variant) As String
On Error GoTo Err_stripPeriod

If Len(Trim(pstring & "")) > 0 Then
stripPeriod = Replace(pstring, ".", "", 1, -1, 1)
Else
stripPeriod = vbNullString
End If

Exit_stripPeriod:
Exit Function

Err_stripPeriod:
MsgBox Err.Description
Resume Exit_stripPeriod

End Function


You then clicked on "Save" icon.

If it was a new code module, you were asked
to give the module a name and you typed in
something like "modUtilities"
(not "stripPeriod").

You then clicked on the top menu item "Debug"
and chose to "Compile" which completed without
error.

Then in the immediate window at the bottom of
the debug window, you tested the function

?stripPeriod("234.3356.339.2177")
23433563392177

If all above is true, then I guess we'll have
to look deeper.

Good luck,

Gary Walter




outstanding advise and made changes but I am still
getting "Undefined function 'stripPeriod' in expression".

Is this function supported in Access 2000? or ???

My actual calling query is:

newhCode: stripPeriod([harmonized_code]) (in the Field
row.) (is this format correct?)

harmonized_code is the name of the field established from
a Make table query. when I execute this Select query I
receive the above error.

Thank you very much Walter!
-----Original Message-----
Hi chaos,

You might try

Public Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "",1,-1,1)
End Function

I suspect the lack of of "Public" was
the problem.

I also always use the "full version" of
Replace in case db finds itself in Win98
environment.

Please respond back if I have misunderstood.

Good luck,

Gary Walter


using result of REPLACE function as the criteria for
query/further explanation: field data coming in as:

datawithPeriods 234.3356.339.2177 (ex)


-----Original Message-----
Has anyone used this function in Access 2000? trying to
strip out 4 periods which appear randomly similar to a
dotted decimal IP address. Objective is to use create a
table with the result of the REPLACE function...

Function name is stripPeriod

query:
newTable:stripPeriod(datawithPeriods)
---
Option Compare Database
Option Explicit


Function stripPeriod(strHcode As String)
stripPeriod = Replace(strHcode, ".", "")
End Function
----

??? Error is 'Undefined Function'

Thanks!
.



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


.
Click to expand...
 
EXACTLY RIGHT!


I cannot thank you enough sir...I would like to stay in
touch with you if you do not mind...
Click to expand...
Hi chaos,

Thank you for the update on your success.

These newsgroups are the venue to "stay in touch."

While I find myself fading in and out due to work,
the volunteers who manage to provide expert help
here day in and day out are the ones *I learned
from* (and continue to learn from).

Don't forget the resources they provide

http://www.mvps.org/access/

and the ability of "Advanced Search" on Google Groups

http://www.google.com/advanced_group_search?hl=en

where you can enter a Newsgroup, say

microsoft.public.access.*

If I'm having problems, many times someone
has already faced it down and solved it previously
in the newsgroups.

Good luck,

Gary Walter
 
Back
Top