access vba course - Removing unwanted characters from a text field

The UK's most regular instructor-led training courses.
Training information: access vba course · Access course microsoft training · Microsoft Access Training uk
See also · microsoft access training · access vba courses · access database course

Resolved · Low Priority · Version Standard

Removing unwanted characters from a text field

Richard has attended:
Access Advanced course

by - delegate Richard [6 posts] (2007 May 30 Wed, 10:18) Reply

I have been provided a text file that I have imported into Access 2003. The file contains over 190,000 records.

One of the fields is a contact ID, an example of a contact ID is as follows:
{0000001F-0000-0000-0000-000000000000}

I want to remove the { } from every contact ID

I have been playing around with the Left and Right commands but cannot get it to work.

When the first { is removed I do not want it replaced with a space, but instead the entire contact id is to move one space to the left.

The right } just needs to be removed.

Can you please advise me on the best way of achieving this?

Thank you

Richard

Version	Date	Location	Places available	Book	Next place rate:
Access Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Nov 25 Tue	Bloomsbury	4	book now	£207	£215
2002/XP	2008 Nov 26 Wed	Tooting	0	FULL
2003	2008 Dec 2 Tue	Southwark	5	book now	£198	£215
2007	2008 Dec 5 Fri	Bloomsbury	4	book now	£224	£224
2003	2008 Dec 9 Tue	Bloomsbury	0	FULL
2002/XP	2008 Dec 11 Thu	Tooting	0	FULL
Full Schedule: See all 55 Access Intermediate course dates. Bookings currently available until 24th November 2009.

RE: Removing unwanted characters from a text field

by - trainer Richard platinum contributer [818 posts] (2007 Jun 1 Fri, 10:05) Reply

You can use Find and Replace to do this.

-Open the table
-Open Find and Replace (Ctrl+H)
-enter { in the find box
-leave the replace box empty
-under LOOK IN select the field you want to look in
-under MATCH choose Any part of field

Let me know if that works

Regards
Richard

· How To Find Good Access Database Courses In London
· Tips for designing databases in Microsoft Access
· Microsoft Access Training Unleashes the Power of the Pivot Table
· Microsoft Access as a Business's Database Management System
· Quick Access Course: Creating a Customer Contact Database in 8 Simple Steps

Access tip:

Compact database automoatically

To ensure optimal performance, you should compact and repair your

Open the Access database or Access project that you want Access to compact automatically.
On the Tools menu, click Options.
Click the General tab.
Select the Compact on Close check box.

View all Access hints and tips