Remove a Gap

  • Thread starter Thread starter Allison
  • Start date Start date
A

Allison

5-CR19AI46130-6 -5651 (wrong)

1-RT1AI59401-1-5751 (should appear like this)

2-RZ1AI30464-13A1-5751 (or it may appear like this)

The above numbers are concatenates of other fields
the field is entitled Account Number.

How can I remove the Gap in the first example?

By the way this is how the field appears in my make table
query:
Account Number: [TYPE] & "-" & [ACT] & "" & [AI] & "" &
[GRANT] & "-" & [YR] & "" & [SUPP] & "-" & Right([CAN]
 
Use the TRIM function in the MAKETABLE query. Works fine if the field is text

Account Number: TRIM([TYPE]) & "-" & TRIM([ACT]) & "" & [AI] & "" &
TRIM([GRANT]) & "-" & TRIM([YR]) & "" & TRIM([SUPP]) & "-" & TRIM(Right([CAN])
 
Back
Top