Update Query to remove characters

[phillsheen]

Hi Everyone!

Is was all going well till this morning and now its all gone wrong!!

I have a table full of bank payments. This table is updated from a spreadsheet, which is made from an Internet banking statement. So basically every month I import the spreadsheet into the database updating 'tblbankpayments'.

'tblbankpayments' has a field called 'description'. This is a reference to the person who has paid so for example 'Mr Smith '. This reference for the past two years has been sent with two spaces at the end of the value ('Mr Smith '). Today I have received the statement and to my surprise the spaces have gone. This totally screws up another part of my database, which displays people’s payments.

So what I would like to do is create an update query that would remove all of the spaces on all of the 'descriptions' so everything matches. So the 'description' would be 'Mr Smith' rather than 'Mr Smith '.

I hope all that makes sense??

Cheers
Phill

 

[Kiwiman]

Howzit

Try

UPDATE yourtable SET yourtable.yourfield= Trim([yourtable]![yourfield]);

Take a back up first

 

[phillsheen]

Take a back up first

I learnt that one the hard way

thanks for that piece of code, just one question before I go forth, is that for a query in SQL view?

 

[Kiwiman]

Howzit

That is the SQL view of a query,

In the design view of the query, VIEW >> SQL VIEW or there is a button on the Query Design toolbar which you can also use to select the SQL view

 

[phillsheen]

Hi,
I used you code, looked like this

UPDATE Bankpayments SET Bankpayments.Description = Trim([bankpayments]![description]);

It says its going to update the 7000+ records but when I the two spaces are still on the end of the description? Any ideas?

 

[Kiwiman]

Howzit

The trim function removes any leading or trailing spaces. I have tried it on mine and it does just that.

Have you actually run the query?

If you are just viewing the recordset before running the query, the query will return what is currently there, not what it will be after the query has run.

 

[gemma-the-husky]

not quite right - surely both operatores should be dots, not one dot and one bang

UPDATE Bankpayments SET Bankpayments.Description = Trim([bankpayments]![description]);

i would do this with a stored query, not a sql statement, so you see what is going on - THEN look at the sql to see how that should be set out.

just design an update query for that field

just update the field to trim(fieldname)

 

[phillsheen]

Hi gemma, welcome to my problem

I have now done both SQL and query design view. None seem to work. I have attached an example of the data im working with and the query that I cant get working so you can see what my problem is.

Thanks for all your help!

Cheers
Phill

 

[neileg]

I can see that there's an invisible trailing character but it's not an ascii space, it's ascii 160 which is á.

That's why Trim doesn't work.

I've tried left([description],len([description])-1) but that seems to error out.

 

[phillsheen]

Invisible?? oh dear!

Any way I can get excel to show these characters?

 

[Kiwiman]

Howzit

I came to the same conclusion as Neil - but didn't track down the ascii character NO.

Neil how did you find that?

This code does however work - use the replace function

UPDATE novembermess SET novembermess.description = Replace([Novembermess]![description],Chr(160),"");

 

[phillsheen]

That worked perfectly! Everything works again, panic over!

Cheers for your help!

Phill

 

[neileg]

I came to the same conclusion as Neil - but didn't track down the ascii character NO.

Neil how did you find that?

I used the ASC function on the rightmost character of the string. I've never needed to use it in Access but the same function exists in Excel so I gave it a try in Access.