[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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
0
linbayzak
Asked:
linbayzak
  • 4
  • 3
  • 2
  • +1
5 Solutions
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now