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,