Problems with Replace function...

[Papa_Bear1]

I've managed to mess up my Access 365 installation to the point that if I try to use the Replace(x, y ,z) function in an SQL statement - it actually crashes Access.
Does anyone know what dll is used by Access 365 to perform this function in SQL statements?

 

[Guus2005]

try VBA.Replace(x, y, z)
It is more specific.

If that doesn't work i need more information about the attached database (or is it local db) and/or the actual statement.

 

[Papa_Bear1]

try VBA.Replace(x, y, z)
It is more specific.

If that doesn't work i need more information about the attached database (or is it local db) and/or the actual statement.

Interesting - I had never seen that before.
I tried that and got "Undefined function 'VBA.Replace' in expression.
Note that I'm referring to use of this function in SQL.
If I can't get this to work - I'm contemplating walking every row of my data and trying a VBA replace instead. Very annoying and slow, but I may be forced to go there if I can't solve this...

As far as sample data/info:
I created a simple test database with one table called tabTesting, with one field called Field1, with one entry with the string "Testing123" in it.
If I run "SELECT Replace(Field1, "ing", "s") AS TestOut FROM tabTesting" it crashes.

Strangely - when I tried using this Replace function in a query on an older Access file I had worked in a long time ago, on an existing table in that database, the Replace function worked. I don't understand how that can be. One thing I noticed was that the old database (where it worked) - it had a reference to the Microsoft DAO 3.6 library (but it specified a path the doesn't exist --- showing it as being under "\Program Files\Common Files\Micorosft Shared\DAO\dao360.dll".) When I looked for that dll, I didn't even have a DAO folder under there - so I don't understand why that old database file isn't complaining about that missing path/file/reference --- and on top of that - the function works there! I found this DAO folder and dll under the 32-bit (Program Files(x86)) path - and even tried moving that to the 64-bit folder - only because someone else said that worked for them... I then included that reference - and that prevented it from crashing when ticked - but - then I reliably get #Error on every output of that function. So that wasn't the answer. What a mess...

 

[Guus2005]

One of the default references is VBA. Otherwise even "If" is a dirty word.

It ofcourse shouldn't crash but give you an error message when you are using a function which doesn't exist.
Perhaps your database is corrupt.
try Compact & Repair.

By the way, you could try this: "SELECT Replace(Field1, 'ing', 's') AS TestOut FROM tabTesting"

Double quotes on the outside, single quotes on the inside.

HTH

 

[Guus2005]

come to think of it, even the commands might require a semicolon instead of regulars comma's. I mix them up sometimes.

"SELECT Replace(Field1; 'ing'; 's') AS TestOut FROM tabTesting"

 

[Papa_Bear1]

One of the default references is VBA. Otherwise even "If" is a dirty word.

It ofcourse shouldn't crash but give you an error message when you are using a function which doesn't exist.
Perhaps your database is corrupt.
try Compact & Repair.

By the way, you could try this: "SELECT Replace(Field1, 'ing', 's') AS TestOut FROM tabTesting"

Double quotes on the outside, single quotes on the inside.

HTH

I should not have put the double-quotes there on the outside... they're not really there. I've noticed when running SQL directly - Access doesn't seem to care if you use single or double quotes to set off literals (e.g. around the 'ing'). When constructing SQL in VBA - I then resort to single-quotes so I can encase the whole string in double-quotes - so I guess maybe I included those double-quotes earlier out of habit... ha... Anyway - the syntax isn't really at issue as the function works in an old database file, and not in new ones... so something is hosed up in Access - and I can't find it to fix it. Was hoping to just figure out which dll file it is and get it placed and registered - but I don't know if that's possible even...

 

[Guus2005]

try shift-f2 with your pointer on the replace command. it brings you to where it is defined.
it should open the object browser.

when you click on the blue VBA text. It would show the DLL it uses for this particular command.

 

[Papa_Bear1]

try shift-f2 with your pointer on the replace command. it brings you to where it is defined.
it should open the object browser.

View attachment 90858

when you click on the blue VBA text. It would show the DLL it uses for this particular command.

View attachment 90860

Very nice to know - but I think that is applicable to use of the function in VBA - right? When I try that in the SQL design view - it just opens a "Zoom" pop-up.
It turns out that I've discovered that the VBA Replace function still works - so I don't know what the Replace function in SQL is calling. So, for now, just to get by, I've written my own MyReplace(pString, pFind, pReplace) kind of function, within which I use the VBA Replace function. This seems to be an end-around for now. But I still do not like NOT knowing what it is doing. How do we do equivalent of that Shift-F2 thing, but for SQL commands? Perhaps that is not possible --- thus my dilemma. Thanks for that Shift-F2 shortcut!

 

[Isaac]

In my experience, whenever native functions, that you obviously know you don't need a reference for, start to behave as if VBA doesn't know what they are--(Replace, Instr, Iif) -- that means it's corrupted.

 

[Guus2005]

This is what i type in QBF:

TestOut : Replace([Field1];"ing";"s")

and this is what i get when viewing SQL code:

SELECT Replace([Field1],"ing","s") AS TestOut
FROM tabTesting

Your output is more or less the same. Try to put square brackets around the field name.

 

[MajP]

when vba functions stop working this is often a broken reference. When access tries to run a function it needs to figure out where to get that function. It searches the libraries in alphabetical order, if it hits a broken reference it cannot continue searching. Since the vba reference library is usually the last in alphabetical order it never gets to VBA. Verify under references you do not have a broken reference

 

[Papa_Bear1]

So - I know it is not a syntax thing - because I can get the function to work in one Access file, but not another...
As far as references - it is very interesting to try to 'pull the thread' on how 'internal' functions like this - used in SQL - somehow link to references...

 

[MajP]

come to think of it, even the commands might require a semicolon instead of regulars comma's. I mix them up sometimes.

"SELECT Replace(Field1; 'ing'; 's') AS TestOut FROM tabTesting"

This actually depends and can be changed, based on your region. This is the list separator property which can be a , or a ;. Most places default to comma, but other regions default it to a semicolon.

 

[MajP]

I missed this statement

It turns out that I've discovered that the VBA Replace function still works - so I don't know what the Replace function in SQL is calling.

So this has nothing to do with a reference issue since it works in vb.

 

[Guus2005]

1. see post #4: Compact & Repair, I am assuming you have already tried that.

2. copy all objects to a new database hoping the error goes away

3. post a sample database describing the problem...

HTH