Function Replace Not Defined

[Dan Pinheiro \(Hotmail\)]

Hi All,
I´m trying to run the code bellow in Access 2000/XP:

.... SELECT
'2'+Right('000000'+LTrim(P3.PRODUTO),6)+Right('0000000000000000000000000000'+LTrim(Replace(P3.PRECO_VEND,',','')),18)+'000'+Format(P3.DT_CAD_PRO,"dd")+Format(P3.DT_CAD_PRO,"mm")+Format(P3.DT_CAD_PRO,"yy")
AS Expr1 ...

It doesn´t work, appear the message "Function Replace Not Defined in
expression".

Running in the Access 2003 it works fine.

What function I have to use?

Tks in advance.

Dan Pinheiro
Rio de Janeiro - Brasil.

 

[Wayne Morgan]

There was a bug in Access 2000 that caused this. The replace function was
available in VBA but not in the query handler. I have heard that it may have
been fixed in service pack 3 for Office 2000 or in one of the Jet 4 service
packs. If not, then what you have to do is create your own function in a
standard module and use the Replace() function in it. Call your function
from the query.

Jet and MDAC updates:
http://msdn.microsoft.com/data/downloads/updates/default.aspx

Office 2000 SP3:
http://www.microsoft.com/downloads/...70-47d0-4306-9fa4-8e92d36332fe&DisplayLang=en
or
http://office.microsoft.com/en-us/officeupdate/default.aspx

You may need to apply service release 1 for Office 2000 before applying SP3.
I believe you can skip SP2.

 

[M.L. Sco Scofield]

Replace was new in Office 2000, so it is there in both Access 2000 and
Access 2002 (Office XP.)

When a function is reported as missing or undefined when it should be there
it is typically a symptom of missing or broken reference.

Take a look at the article at
http://support.microsoft.com/default.aspx?scid=kb;en-us;231413. Follow the
instructions in the "Resolution" section. This should solve your problem.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[aaron.kempf]

yeah.. so it's not there; the easiest way to get it working is to look
for this replace function on www.freevbcode.com

replace was new in VB6; and there is a VB5 version of the code there--
you can just paste that into a module and it works like a charm

hth

aaron kempf
access nationalist

 

[Dan Pinheiro \(Hotmail\)]

Thanks Wayne, I´ll try to fix updating the office.
Do you have any article where I can learn to create a module?

 

[Wayne Morgan]

To create a standard module, go to the modules tab in the main database
window and click the New button. Give the module a descriptive name, but not
the name of anything else, including the names of procedures you plan on
adding to it. The module is just a holder, you can put multiple procedures
in it. When the code window opens go to Insert|Procedure. Choose Function
and Public. Give the function a name such as MyReplace. The first and last
lines of the function will be created for you. You'll have to add the
parameters though inside the ( ) on the first line. For the parameters, take
a look at the help file for the Replace() function. You'll need to pass
which ever parameters of the Replace() function you intend on using.

 

[M.L. Sco Scofield]

If it's not a reference problem, then it must be a service pack issue.

Replace works just fine in queries in 2000 - SP3, 2002 - SP2, and 2003 -
SP1.

As I keep all of my machines up to date, I can't verify when the problem was
fixed.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Dan Pinheiro \(Hotmail\)]

Hi "M.L. Sco Scofield"
Can you confirm what references are checked in your Access 2000?
I updated to SP3, but didn´t work.
The options checked in my system are:
*Visual Basic For application
* Microsoft Access 9.0 Object Library
* OLE Automation
* Microsoft ActiveX Data Objects 2.7 Library (The default was 2.1 Library)

tks...

 

[M.L. Sco Scofield]

Dan,

The references are not an Access setting. They are a database setting, so
each database you have may be different.

I changed my test database to the same as your list and no problems.

Two questions:

1 - Does your database compile with no errors?
2 - Did you make sure the latest Jet updates are installed?
(See http://support.microsoft.com/kb/300216/)

Hope this helps.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com