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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :-)
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 :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Laura
The points are just a bonus, we're just glad we could help :)
ASKER
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 :-)
ASKER