Deleting special characters

[accessn00b]

Hi,

I'm an absolubt beginner with databases ad need some help removing a special characters.

I have a field that contains numbers only in some cells and both numbers and letters in others. In order to be able to inport to my DB I had to add a ' at the beginning and end of each record so that both excell & access would pick up the dta as text. I now need to remove the ' characters but cannot figure out how it's done.

I know I need to use an update query from reading other threads here but nothing I have read has given me an answer I can understand or impliment.

please help.

P.S.

I have no knowledge on using SQL so any help needs to be in very simple access term

Thanks in advance.

 

[alow]

Are you trying to remove the characters in Access? Or in Excel?

 

[accessn00b]

Thank you for he reply and sorry I was not clear,

I'm trying to remove it from access. I had to add it in excel to allow me up import the data to acess. here's an example of the data.

Field: Unique Ref
1st record '123456789123'
2nd record '123456798L1234567'

because some of the ref numbers dont have letters Excel/Access picked them up as numbers and would not allow to import to a text field.

Adding the sp characters allowed me to import them but now I want rid of them.

Thanks again

 

[alow]

Does every record have an apostrophe at the beginning and end of the data that you want to remove?

 

[accessn00b]

Yes,

It was the only way I could get the data to upload.

 

[alow]

And my final question before I present you with an attempted answer is which version of Access are you working in?

 

[accessn00b]

Thank yu,

I'm working with access 2007

 

[alow]

Sorry for the delay. You would want to use an Update Query (if you want to update the current table) or a Make Table Query if you want to put the formatted data into a new table (safer). The formula that you will use for the fields you want to truncate is Mid([FieldName],2,Len([FieldName])-2). This will show everything from the field except for the first and last character. I've gotten quite busy today, so I hadn't had a chance to give you the full run through on that, so I hope you're familiar with queries at least a little bit. Let me know if you can get it working or not and I'll see if I can come up with more detail for you.

 

[accessn00b]

Hi,

Thanks for the help.

I'm afraid I could not get it to work (I did warn in my initial post that I'm an absolubt beginner)

I think my problem is I dont understand how your code equates to the below fields which are what I see when I go into query design.

Field:
Table:
Update To:
Criteria:
Or:

Also,

I do need to replace the existing data in the table rather than run it into a new table.

Thanks again.

 

[alow]

As soon as I have some time to put a full instruction together for you, I will try. I keep pretty busy at work, so you may have to wait a while.

 

[accessn00b]

no probs and thanks again. I'll use the time to start reading up on queries

 

[alow]

Let's see if I get the time to complete this post today:
Begin by going to the Create Tab and choosing Query Design.
From the Show Table window, select the table that has the fields you are working with, click on Add and then Close.
Now is where the fun starts. Since you are going to be updating your original table, you have to be EXTREMELY careful, because you could very easily wipe out your data. I would suggest making a copy of the database before you begin any of this.

You will only want to work with the fields that you are editing. You will use the formula I gave you earlier to truncate the fields that have the apostrophes at the beginning and end.

In the Field line below, type the following formula for the fields that you want to trim:
MID( [FieldName],2,LEN([FieldName])-2)
This will take the middle part of the field, starting with the second character, for 2 characters less than the total number of characters in the field.
Next, to make sure this does what you want, and before you change this to an update query, click on View on the Query Tools | Design Tab of the Ribbon.
You should see the data that you want. Click on the Design View button (Triangle, Ruler and Pencil) to get back into Query Design.

Now is the tricky part, that again, I would advise against. On the Query Tools | Design Tab of the Ribbon, click on Update.
Now, in the bottom, delete everything in the Field row and select the field that you would like to Update.
Then, put the formula MID([FieldName],2,Len([FieldName])-2) in the Update To row.
Next, cross your fingers and click on Run. You will get a message that "You are about to update X row(s)" Click on Yes to update the records.
Look at the data in the table, if everything went well, you will have what you are looking for. The reason I suggest so highly against this method is that it modifies your original data, and there is no undo in Access. After you verify the data in the table, close the query and do not save. If you save this query, you will have a query that, if double clicked on, will delete the first and last character of the specified fields every time.

Let me know if this helps! Good luck!

 

[accessn00b]

Hi,

sorry it's taken a while to respond.

Thank you so much for this. It worked perfectly.

Thanks again

 

[alow]

Glad everything worked out for you. Let us know if you have any other questions!