Access Find and replace

	Home Register Search Help Login

Sponsors

Hosting from $3.99 per month!

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions. dd

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Access Find and replace

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Access Find and replace

Page: [1]


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	Access Find and replace - 5/23/2005 11:44:38 I am trying to do a find/replace in Access to replace values in my database. Access does not seem to be cooperating, and does not find values that I want to replace, or only finds one value and replaces it, when there are obviously numerous entries of the same value. Is there a trick to the find/replace in Access that is different from the other Microsoft programs? Any advice would be appreciated. Thanks _____________________________ Chris http://www.sightbysite.net


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Access Find and replace - 5/23/2005 11:52:02 Can you be specific? Are you trying to replace whole records, whole field values, or just certain text in fields? Are you doing this actually inside Access or via a web page? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to fredecd)


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	RE: Access Find and replace - 5/23/2005 12:03:24 I am trying to replace a field in Access. The original database creator set up a field called strName, and has each employees name set up with first name and last name in the strName column, "Joe Smith" for example. We would like to edit all records to change it to "Smith, Joe" in each instance. Some of the pages now pull records based on strName, and I wanted to do a global change in the database for each employee name in each table. I realize that the system should have been set up with the unique ID's instead of the strName, but we are trying to undo something that we inherited without starting over. _____________________________ Chris http://www.sightbysite.net (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Access Find and replace - 5/23/2005 12:58:18 I came up with a simplified query that should help. HOWEVER, this does no error checking and if there aren't 2 names with at least a one space between them, this script will put NOTHING in the field 'cause it will error on that row. SO be sure to make a backup of the DB (but you knew that anyways right?) UPDATE myTable SET myNameField = Right(myNameField,Len(myNameField)-InStrRev(myNameField," ")) & ", " & Left(myNameField,(InStrRev(myNameField," ")-1)); At least it worked with my sample data. Hope it helps. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to fredecd)


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	RE: Access Find and replace - 5/23/2005 13:37:42 Thanks, Roger. When I ran it, I got the following error: Database Results Error Description: [Microsoft][ODBC Microsoft Access Driver] Undefined function 'InStrRev' in expression. Number: -2147217900 (0x80040E14) Source: Microsoft OLE DB Provider for ODBC Drivers _____________________________ Chris http://www.sightbysite.net (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Access Find and replace - 5/23/2005 13:52:09 I thought you told me it was in Access? Now it looks like you're using a web page. That is a web page error isn't it? Do you have a copy of the DB to test it on? Again, this query was not intended to be run on a web page but within Access itself. And I was under the assumption it was a "1 time thing". Or do you really need this to run on a page? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to fredecd)


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	RE: Access Find and replace - 5/23/2005 13:56:15 My error. I thought you were giving me an easy way out. I did try to run it from a page. I will run again from Access. Thanks. _____________________________ Chris http://www.sightbysite.net (in reply to rdouglass)


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	RE: Access Find and replace - 5/23/2005 14:15:47 Now it tells me that the expression entered contains invalid syntax, and I must have entered an operand without an operator. I am entering the sql using expression builder in query design mode. Is that correct? _____________________________ Chris http://www.sightbysite.net (in reply to fredecd)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Access Find and replace - 5/23/2005 14:31:57 Hmmm. You should have just been able to replace the field and table neames with yours and paste it into a query in SQL mode. I'm using Access 2002. Can you post the exact query that is giving you the error as well as table and field names? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to fredecd)


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	RE: Access Find and replace - 5/23/2005 14:37:07 I am using Access 2000. UPDATE tblOperators SET strName = Right(strName,Len(strName)-InStrRev(strName," ")) & ", " & Left(strName,(InStrRev(strName," ")-1)); _____________________________ Chris http://www.sightbysite.net (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Access Find and replace - 5/23/2005 15:23:43 That is very strange. I setup a DB table called 'tblOperators' and in it I put a field 'strName' and used that exact query: UPDATE tblOperators SET strName = Right(strName,Len(strName)-InStrRev(strName," ")) & ", " & Left(strName,(InStrRev(strName," ")-1)); and it worked fine for me. 1. You are using this in Access and not on a web page. 2. The table is named tblOperators 3. The field is a text field named strName 4. You have write priviledges to the DB If all of those are OK, have you tried downloading a local copy and testing on that? Any ideas with that? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to fredecd)


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	RE: Access Find and replace - 5/24/2005 13:31:49 Computers are a wonderful thing. The exact script that would not work yesterday works fine today. That did the trick. Thanks again, Roger. _____________________________ Chris http://www.sightbysite.net (in reply to rdouglass)


dpf Posts: 7126 Joined: 11/12/2003 From: India-napolis Status: offline	RE: Access Find and replace - 5/24/2005 14:04:25 quote: Computers are a wonderful thing. The exact script that would not work yesterday works fine today. like spouses? _____________________________ Dan (in reply to fredecd)


fredecd Posts: 312 Joined: 12/3/2003 From: Louisiana, USA Status: offline	RE: Access Find and replace - 5/24/2005 14:06:10 LOL ... Exactly _____________________________ Chris http://www.sightbysite.net (in reply to dpf)