Append Query with Conversion

[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.

 

[Steve Schapel]

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)

 

[Cheryl]

Thank you; that worked perfectly!

Cheryl