I think this may have been covered before. However after doing a number of searches, I couldn't find a thread. If you know of a thread, feel free to simply direct me the proper place.
We recieve a daily flat file from our mainframe. There are odd instances where one of the text fields has a blank space before the text. For example "JOHN SMITH AGENCY" comes in as " JOHN SMITH AGENCY". I am working with the programmer of to rectify the problem. However, I would like to clean up our current data. (Each day's file is appended to the end of our table which already has hundreds of thousands of records.)
I thought one method might be to run a make table query creating a new field. The formula would look something like:
Agency Code: IIf(Right([AgencyCode],1) Like " ", Mid([AgencyCode],2,31), [AgencyCode])
However, for the sake of learning new tricks, is there any SQL syntax that would delete the blank space without the need to create a new table?
Thanks in advance.
We recieve a daily flat file from our mainframe. There are odd instances where one of the text fields has a blank space before the text. For example "JOHN SMITH AGENCY" comes in as " JOHN SMITH AGENCY". I am working with the programmer of to rectify the problem. However, I would like to clean up our current data. (Each day's file is appended to the end of our table which already has hundreds of thousands of records.)
I thought one method might be to run a make table query creating a new field. The formula would look something like:
Agency Code: IIf(Right([AgencyCode],1) Like " ", Mid([AgencyCode],2,31), [AgencyCode])
However, for the sake of learning new tricks, is there any SQL syntax that would delete the blank space without the need to create a new table?
Thanks in advance.
