Append Query with Conversion

  • Thread starter Thread starter Cheryl
  • Start date Start date
C

Cheryl

I need to append data to a database, but the number format in the
sending database is padded and spaced differently than the receiving
database. I need to convert this:

3XA-04-00074CR (could have up to 4 leading zeros)

to this:

3XA-04-74 CR

Thank you for any help you can provide.
 
Cheryl

Assuming your original number is always of the structure:
3 characters hyphen 2 characters hyphen then the rest
.... then I think this will work...
ConvertedNumber: Left([OriginalNumber],7) & Val(Mid([OriginalNumber],8))
& " " & Right([OriginalNumber],2)
 
Back
Top