Leading Spaces and space after name

[Sumanth.Ganjam]

Hi Everyone,

I am having the table in access database which contains customers name with and without leading spaces and the spaces after customer name.



Please help me how to find out Leading spaces and spaces after customer name in access Database.

[June7]

Why don't you do a Find/Replace on the table to remove the leading space?

In my experience, Access does not retain spaces at end of text

[Sumanth.Ganjam]

Hi,

Thank you very much for your replay, For me the client requirement is they need to find out the leading spaces.

When I import excel to access, excel can contain the spaces after customer name how can find out that spaces in Access.

[June7]

Try a query like:

SELECT * FROM table WHERE Left([CustomerName],1)=" ";

or

SELECT * FROM table WHERE [CustomerName] LIKE " *" OR [CustomerName] Like "* ";

Are you sure the space after name is there?

[Sumanth.Ganjam]

Thank you very much and is working fine.

Can you please tell me how to Replace the spaces.

[June7]

You can use the Find/Replace tool from the ribbon. Basically, have it find all spaces that are at beginning of string and replace with nothing.

Space at end of string is more difficult but there shouldn't be any, although your experience indicates it is possible and I just read another thread that had the same issue with imported data.

An UPDATE sql action should actually be able to take care of both.

UPDATE table SET fieldname=Trim([fieldname])