Need to change format!!

P

PJ218

Hello,

I need to change my number format. Right now it goes as follows:

208-1000003/1

I need it to look like:

208/100-0003.1

Any suggestions??
 
M

[MVP] S.Clark

The Replace() function returns a string in which a specified substring has
been replaced with another substring a specified number of times. (Sorry,
but that is straight from the Help file. I know it's an awful description,
but maybe if I post it, someone will note how awful it is.)

Anyway, you could do it with two Replace() calls.

Replace("208-1000003/1", "/", ".") = 208-1000003.1
Replace("208-1000003.1", "-", "/") = 208/1000003.1
 
D

Douglas J. Steele

I started to post a similar solution this morning, but then noticed that
PJ218 also wanted a dash added after position 7.

PJ218: Assuming it's always in position 7, you can use the Left and Mid
functions to split the string for you. Store the results of the the two
Replaces Steve talked about in a variable (by the way, it can be done on one
line as Replace(Replace("208-100003/1", "/", "."), "-", "/")), and then do
something like:

Left(strValue, 7) & "-" & Mid(strValue, 8)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Top