Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 857
  • Last Modified:

string manipulation in MS Access query

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

               
0
jr_bautista
Asked:
jr_bautista
  • 7
  • 4
  • 2
  • +1
1 Solution
 
reb73Commented:
Here you go -
SELECT  REPLACE(fname,MID(fname,INSTR(1,fname,'('),INSTR(1,fname,')')),'') as fname1;

Open in new window

0
 
jr_bautistaAuthor 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
0
 
ralmadaCommented:
Could it be a parenthesis extra?

SELECT  REPLACE(A.fname,MID(A.fname, INSTR(1,A.fname,'('), INSTR(1,A.fname,')'),'')  
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
ralmadaCommented:
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
0
 
jr_bautistaAuthor Commented:
found out that reb73: query is correct but it gets an error when encountering data that has no parenthesis to remove
0
 
reb73Commented:
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

0
 
jr_bautistaAuthor 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.
0
 
reb73Commented:
REPLACE is a valid Access/Transact-SQL keyword.. Do you mind posting the relevant C# code here?
0
 
jr_bautistaAuthor 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);
0
 
reb73Commented:
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

0
 
jr_bautistaAuthor Commented:
Still got the error

Undefined function 'REPLACE' in expression.
0
 
jr_bautistaAuthor 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
0
 
Anthony PerkinsCommented:
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.
0
 
jr_bautistaAuthor Commented:
Oooppss...sorry about that. I didn't realize I already have the question on a different forum...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now