Link to home
Create AccountLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Find account number with spaces and an L in them

I have a Monarch model that distills one column into account numbers.  However, as it is not perfect, some account numbers come in with trailing three spaces and an L or LOA (or whatever) after the end of the account number.  This happens when the account number is not a full 12 digits.  So, when I bring this data into an ACCESS 2003 database, how can I check for these trailing spaces and the Alpha character?  I do not want to delete the account number, only get rid of the spaces and letter(s) at the end.  So, if something comes in as 12345678   L, I want it to be change to 12345678.  I assume I would need to loop through each account number in the table.

Avatar of IrogSinta
Flag of United States of America image

You can just use an update query:
Update NameOfTable Set AcctNum=Left(AcctNum & " " , InStr(AcctNum & " ", " ")-1)

Open in new window

Avatar of Sandra Smith


IrogSinta, that did not work.
You're doing this within Access, right?  And you did use the actual fieldname as well?  Are you getting an error message?
Avatar of Sandra Smith
Sandra Smith
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Solved myself.