Undefined function 'replace' in expression

[freem500]

I'm trying to help someone with some text functions in Access, and I have used the Replace function to strip out spaces in a postcode. I created a dummy database in Access 2003 but in 2000 format since she is still on Access 2000. However, she is getting the above message. Incidentally, I don't get the message when I run it in Access 2000.

She has checked her references and has no missing ones. She has:

• Visual Basic for Applications
• Microsoft Access 9.0 Object Library
• OLE Automation
• Microsoft DAO 3.6 Object Library
• Microsoft ActiveX Data Objects 2.5 Library

I have attached the DB - I'd be really grateful if someone could try opening it in Access 2000 to see if they get the same error.

Does anyone have any ideas?

Many thanks

 

[boblarson]

It could be that the function Replace isn't available to use in queries like that in A2K. I just tried opening it in 2000 and it worked for me, but I also have 2003 and 2007 installed here at home. Unfortunately I won't be in the office today (where I only have 2000 installed) to test. But, when I open the query and navigate to the available functions it does not show up.

Just a possibilty, mind you.

 

[RuralGuy]

I too do not have ac2k but have another suggestion. Aside from MISSING references, there is also the possibility the references simply need to be refreshed. It is the 3rd bullet point under Quick Solution.

 

[freem500]

Thanks for the replies

Bob, you are quite right - in 2000 the Replace function is not included in built-in functions. I hadn't noticed that. However, I can still run the query on a machine that only has Access 2000.

RuralGuy, I think that my colleague has already tried bullet point 3 as I had seen that solution on another forum, but it still isn't working. However, I have just found this http://www.mcse.ms/message1580720.html
on a search, which seems to suggest writing a VBA function, as the Replace function did not work in all versions of Access 2000. Have you ever heard of this?

And finally, if my colleague cannot get Replace to work in her environment, is there another way of stripping out the space from the postcode?

Thanks for your help

 

[RuralGuy]

Here's a function that I'm sure raskew would let you use.

Function onespace2(pStr As String, pDelim As String) As String 
'******************************************* 
'Purpose: Removes excessive spaces from a string 
' and replaces remaining spaces with user
' selected delimiter.
'Coded by: raskew 'Inputs: From debug window:
' ? onespace2(" the quick brown fox", ".") 
'Output: "the.quick.brown.fox"
'******************************************* 
Dim strHold As String
strHold = RTrim(pStr)
Do While InStr(strHold, " ") > 0
  strHold = Left(strHold, InStr(strHold, " ") - 1) & Mid(strHold, InStr(strHold, " ") + 1)
Loop
strHold = Trim(strHold)
Do While InStr(strHold, " ") > 0
   strHold = Left(strHold, InStr(strHold, " ") - 1) & pDelim & Mid(strHold, InStr(strHold, " ") + 1)
Loop
onespace2 = Trim(strHold)
End Function

 

[MStCyr]

Hi

I tried the database using Access and do end up with the same error you did.

You may have to create the replace function yourself.

Maurice

 

[Brianwarnock]

I'm sure all post codes end with 3 characters after the space, therefore
Left([fieldname],Len([fieldname])-4) & LTrim(Right([fieldname],4))
will achieve what you desire.

Brian

Edit Checked all british postcodes end with 3 characters, I had assumed British given the time of posting.

 

[neileg]

Brian, I'm not sure Trim is supported in A2k.

 

[Brianwarnock]

Brian, I'm not sure Trim is supported in A2k.

Oh! I didn't think of that.

Brian

 

[boblarson]

Trim is in Access 2000.

 

[freem500]

Thanks for everyone's help - I've suggested that my colleague tries the LTrim solution suggested by Brian (Yes, I am working with British postcodes), as I don't fancy trying to talk her through the VBA solution over the phone.

Thanks again - I'll let you know if it doesn't work!

 

[JayLobos]

I know it has been awhile since anyone has updated this thread, but there is way to correct this problem in Access 2000. The problem is that you are most likely running VBA version 6.00 (this was release with early versions of Access 2000). This function became available in VBA version 6.05. I had this problem the other day and was able to fix it by downloading the security patch for VBA 6.5 which in turn updated my Access VBA version to 6.05. Error went away and works perfect now.

To get the download just do a google search for:
Update vba 6.5 download

 

[RuralGuy]

@JayLobos: Updates like you posted are always appreciated. Thank you.