Space remove query

Question

Sometimes there are blank spaces at the beginning of a field is there a query that will search for and remove them?

Table:[NameList].Notes

Thanks!

Answer 1

You can execute an update query to remove them, but are you sure they're spaces? They may be other characters that only look like spaces.

Let's assume they are spaces, the following will remove them (from the named field only):

UPDATE myTable SET myField = Trim(myField)

If after running the above query, you still see spaces, then they're not real spaces. In that case you need to find out what they are. To do that, you can run the following query:

SELECT myField, Asc(Left(myField, 1)) As MyFunnySpace

FROM myTable WHERE (myField NOT LIKE "[a-z]*") AND (myField NOT LIKE "[A-Z]*") AND (myField NOT LIKE "[0-9]*)

This will give you the ASCII code of the character in question. If it's not 32 (a space), then you can use the following reference (http://www.asciitable.com/) to find out what it is. Once you know what the ASCII character is, you can use a query to get rid of it.

UPDATE myTable SET myField = Replace(myField, ascii_code_to_get_rid_of, "")

"wav1" wrote in message news:*** Email address is removed for privacy *** .com...

Sometimes there are blank spaces at the beginning of a field is there a query that will search for and remove them?

Table:[NameList].Notes

Thanks!

 

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 2

UPDATE [NameList] SET Notes= Trim(Notes) WHERE Notes Like " *"