We help IT Professionals succeed at work.

string manipulation in MS Access query

Medium Priority
878 Views
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

               
Comment
Watch Question

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

Open in new window

Author

Commented:
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

Commented:
Could it be a parenthesis extra?

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

Commented:
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

Author

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

Commented:
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

Author

Commented:
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.

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

Author

Commented:
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;

                Response.Write(SQLStatement.Text);

Commented:
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

Author

Commented:
Still got the error

Undefined function 'REPLACE' in expression.

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2012
Commented:
I guess you did not like the answer you got here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24120272.html

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

Author

Commented:
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.

OR

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.