We help IT Professionals succeed at work.

string manipulation in MS Access query

Medium Priority
Last Modified: 2013-11-27
I want to do something like this which works in MS SQL but not in Access

SELECT    REPLACE(fname,SUBSTRING(fname,CHARINDEX('(', fname),CHARINDEX(')', fname)),'') as fname,

I need it to work in MS Access

e.g. when fname = Mike(2323) will return Mike
                 (23234)Bernard = Bernard

Watch Question

Here you go -
SELECT  REPLACE(fname,MID(fname,INSTR(1,fname,'('),INSTR(1,fname,')')),'') as fname1;

Open in new window


Got this error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

This is the actual code:

SELECT  REPLACE(A.fname,MID(A.fname,INSTR(1,A.fname,'('),INSTR(1,A.fname,')')),'') as A.FName

Could it be a parenthesis extra?

SELECT  REPLACE(A.fname,MID(A.fname, INSTR(1,A.fname,'('), INSTR(1,A.fname,')'),'')  

Actually i think is because of the quotes
 SELECT REPLACE( A.fname, MID(  A.fname,   INSTR(1,A.fname,"("),   INSTR(1,A.fname,")") ) ,"")  as A.Fname


found out that reb73: query is correct but it gets an error when encountering data that has no parenthesis to remove

Use an IIF clause -
SELECT IIF(INSTR(1,fname,"(") > 0 AND INSTR(1,fname,"(") > 0, REPLACE(fname,MID(fname,INSTR(1,fname,"("),INSTR(1,fname,")")),""), fname) as fname1;

Open in new window


Great, that's what I need...but there's one thing left: the query runs perfectly inside Access but when use in C# got this error:

Undefined function 'REPLACE' in expression.

I Response.write the query from the browser  and paste in Access and got no problem but when I run the .aspx page got the above error.

REPLACE is a valid Access/Transact-SQL keyword.. Do you mind posting the relevant C# code here?


Sure, here you go

sqlStmt = sqlStmt + " SELECT A.Timedate as td, A.FName, IIF(INSTR(1,A.fname,'(') > 0 AND INSTR(1,A.fname,'(') > 0, REPLACE(A.fname,MID(A.fname,INSTR(1,A.fname,'('),INSTR(1,A.fname,')')),''), A.fname) as fname1,  A.LName, A.Code ";
                sqlStmt = sqlStmt + " FROM EvnLog A ";
                sqlStmt = sqlStmt + " INNER JOIN ";
                sqlStmt = sqlStmt + " (SELECT Min(B.Timedate) As MinDate, B.FName, B.LName, B.Code ";
                sqlStmt = sqlStmt + " FROM EvnLog B ";
                sqlStmt = sqlStmt + " GROUP BY  B.FName, B.LName, B.Code, DateValue(B.Timedate) ";
                sqlStmt = sqlStmt + ") C ";
                sqlStmt = sqlStmt + " ON ";
                sqlStmt = sqlStmt + " A.Timedate = C.MinDate AND A.Code = C.Code ";
                sqlStmt = sqlStmt + " WHERE A.TimeDate IS NOT NULL AND A.LName <> '' ";
                if (Session["empLogbyCompany"].ToString() != "")
                    sqlStmt = sqlStmt + " AND (" + Session["empLogbyCompany"] + ")";
                    sqlStmt = sqlStmt.Replace("%","*");
                sqlStmt = sqlStmt + " ORDER BY A.Timedate DESC, A.Code, A.FName + A.LName ";

                SQLStatement.Text = sqlStmt;


Could it be the length of the line? Can you break up the first line as follows -
sqlStmt = sqlStmt + " SELECT A.Timedate as td, A.FName, ";
sqlStmt = sqlStmt + " IIF(INSTR(1,A.fname,'(') > 0 AND INSTR(1,A.fname,')') > 0, ";
sqlStmt = sqlStmt + " REPLACE(A.fname,MID(A.fname,INSTR(1,A.fname,'('), ";
sqlStmt = sqlStmt + " INSTR(1,A.fname,')')),''), A.fname) as fname1,  A.LName, A.Code ";
sqlStmt = sqlStmt + " FROM EvnLog A ";

Open in new window


Still got the error

Undefined function 'REPLACE' in expression.


Here's the query via Response.write

SELECT A.Timedate as td, A.FName, IIF(INSTR(1,A.fname,'(') > 0 AND INSTR(1,A.fname,')') > 0, REPLACE(A.fname,MID(A.fname,INSTR(1,A.fname,'('), INSTR(1,A.fname,')')),''), A.fname) as fname1, A.LName, A.Code FROM EvnLog A INNER JOIN (SELECT Min(B.Timedate) As MinDate, B.FName, B.LName, B.Code FROM EvnLog B GROUP BY B.FName, B.LName, B.Code, DateValue(B.Timedate) ) C ON A.Timedate = C.MinDate AND A.Code = C.Code WHERE A.TimeDate IS NOT NULL AND A.LName <> '' AND ( (A.fname LIKE '%Barry%' AND A.Lname LIKE '%Green%')) ORDER BY A.Timedate DESC, A.Code, A.FName + A.LName
Top Expert 2012
I guess you did not like the answer you got here:

But to set the record JET does not support the REPLACE() function.

P.S. Please don't add the MS SQL Server zone, this question has nothing whatsoever to do with MS SQL Server.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


Oooppss...sorry about that. I didn't realize I already have the question on a different forum...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.