Undefined Function Replace In Access Query

Question

5.00/5 (1 vote)

See more:

When I use replace function in access query it work fine but when I execute this query in my vb.net program it show error undefined function replace.

Here Is a Query :

SQL

SELECT ShopMaster.ShopId, ShopMaster.ShopName, Replace(ShopName,' ','') AS SearchShopName FROM ShopMaster

This query work fine in access but not through my vb.net program.

I am using vb.net 2008 and access 2007.

Thanks
Rohit Sharma

Posted 9-Dec-10 1:55am

rohit_189

Updated 16-Mar-17 21:27pm

Henry Minute

v3

Add a Solution

Comments

Toniyo Jackson 9-Dec-10 8:00am

Always use code/query on code block.

rohit_189 9-Dec-10 8:13am

Thanks ToniyoJackson

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Slacker007 · Answer 1 · 2010-12-09T02:14:00

Ah. Access.

I believe you will not be able to do this outside of MS Access. Does VB.Net 2008 have the Replace() function and if so, are you using it correctly? I don't program with VB.Net so this is why I ask.

Please note: Access works great when you are doing stuff "in" Access but it is a very hard database to work with as a backend to VB, C#, etc...

I have found that SQL Server (Express) as my backend and Access as my frontend works well and reduces the headaches involved when working with MS Access as the backend.

Kschuler · Answer 2 · 2011-03-17T05:53:00

If all you are trying to do is get rid of spaces, why don't you instead use this:

SELECT ShopMaster.ShopId, ShopMaster.ShopName, Trim(ShopMaster.ShopName) AS SearchShopName FROM ShopMaster

If it's not just about extra spaces, you might want to try the iif instead of Replace. (I didn't test with a 2007 access database, so I'm not sure it's still available...)

SELECT ShopMaster.ShopId, ShopMaster.ShopName, iif(ShopName,' ','') AS SearchShopName FROM ShopMaster

Dave Kreskowiak · Answer 3 · 2011-03-17T08:19:00

Solution 5

The query works in Access because the Replace function is defined by Access, not by the limited subset of TSQL that the Jet database engine supports.

Posted 17-Mar-11 8:19am

Dave Kreskowiak

Updated 30-Sep-16 2:35am

v2

M Kennedy · Answer 4 · 2011-10-14T04:28:00

Solution 6

Example: To get rid of carriage-returns in your text:

UPDATE OUTFILE05AAA SET MatlDesc =
Replace(Replace(Replace([MatlDesc],Chr(10)," "),Chr(4)," "),Chr(3)," ");

Posted 14-Oct-11 4:28am

M Kennedy

Undefined Function Replace In Access Query

4 solutions

Solution 1

Solution 4

Solution 5

Solution 6

Add your solution here

Preview 0