Trim blanks spaces from right

[RichardMc]

I am creating a Find Unmatched query in Access 2000.

One of the tables I am comparing has blank spaces at the end of the firstname field. Before I can successfully run a find unmatched query, I need to trim out the additional spaces. The number of spaces is not constant, but varies.

Suggestions?

Thanks!

 

[dcx693]

You can use the Trim() function. It will take out leading and trailing spaces.

 

[RichardMc]

Can you give me an example of how to use it here?
'Fraid I don't know how to use it to Trim an indeterminate number of blank spaces!

Thanks!

 

[dcx693]

Trim(" t ext ")
Trim("t ext ")
Trim(" t ext")
Trim("t ext")
will all return "t ext".

You don't need to physically trim out the spaces in your table data (but it's not a bad idea to get rid of extraneous info). You can just use a calculated expression in your query.

 

[RichardMc]

Thanks!!

I actually ended up including the Trim as part of the concatenation of name fields:

UPDATE Tablename SET Tablename.Name = Trim$([Firstname]) & " " & Trim$([Lastname]);

 

[Mile-O]

UPDATE Tablename SET Tablename.Name =

Conside FullName rather than Name as Name is a reserved word in Access and can cause problems that you may not be expecting right about now...

 

[RichardMc]

Thanks for the tip! Fullname it will be from here on in....

No point in tempting fate since 'The best laid schemes o' mice an' men Gang aft agley'

 

[Mile-O]

'The best laid schemes o' mice an' men Gang aft agley'

Absolutely.