i need to trim leading spaces in a number field in an access 2007 table using an update query

Hi Experts,

I have some data that is inconsitent, causing a list box to populate inaccurately.  The field in question is a CaseNumber field, it should be 10 digits, and it does have leading zeros which is fine.  But, some of the records have empty spaces before the leading zeros and this is not ok.  It causes some missing data, only brings in the ones that match the casenumber on the form.  So i need to update the case number in the client table and in the trips table to erase all the empty spaces before the case number.  I've tried trim in query, but didn't get the result i wanted.  Can you help.  

This is what i tried to populate the listbox  Case Number: Val(Right([CaseNumber],10))

I would rather just update these fields in the two tables than manipulate the data in the query, but i'll take either solution if it works.  Thanks in advance,

Laura
linbayzakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveCommented:
It sounds like TRIM(CaseNumber) would do it for you. You could either use the TRIM(CaseNumber) in your query, do an update to the table or create a second table with the TRIMmed values.
0
Patrick MatthewsCommented:
Trim is almost surely what you need.  Just be aware that Trim will remove trailing spaces too, so if by chance you have those and need them preserved, please let us know.

Patrick
0
Rey Obrero (Capricorn1)Commented:
and, dont' use the function Val(), it will remove all your leading zeroes
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

linbayzakAuthor Commented:
yep, found that out on some test data, big mistake, but luckily it was only test data :-)
0
linbayzakAuthor Commented:
ok, i tried to do an update query, on test data of course, and when i put this into the update row Trim(CaseNumber), it updated the CaseNumber field to "CaseNumber". Did i do this wrong?  

And, when i put Trim(CaseNumber) in the field of the query, it doesn't pull any data.  Sorry, i might have done this wrong.  Thanks :-)
0
Rey Obrero (Capricorn1)Commented:
Change
Trim(CaseNumber)

to

Trim([CaseNumber])

the square bracket will tell the query that is a field not a string
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCommented:
In access query builder it put it out like this:
SELECT Trim([CaseNumber]) AS Expr1
FROM Your_table;
0
linbayzakAuthor Commented:
Thanks everyone.  This works, it's so so so important to put those brackets [ ] around the field, otherwise it will simply see it as a string just like you mentioned.  Also, you can use Str(Val([CaseNumber])) and it works but it does trim the leading zeros as well as the leading blank spaces.  Thanks for all your help and the quick responses.  Hope you are happy with the points.  :-)
Laura
0
SteveCommented:
The points are just a bonus, we're just glad we could help :)
0
linbayzakAuthor Commented:
Thanks so much.  I know i depend on you a lot, but you don't know how nice it is to have someone in your corner when you're in a pinch.  I appreciate all you do :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.