Problem using the Format statement

[ShellWorker]

Here’s the problem:

When I execute the following line of code:
Format(X, "000000")
Where X = "0S111", It returns:
0S111
Which is what I want.

But...

When I execute the same line of code where X = "0E111", It returns:
000000
Which is NOT what I want. I wanted it to return:
0E111

Any ideas?

 

[vbaInet]

Try this:

Format(X, "@@@@@&")

 

[Mr. B]

ShellWorker,

You may need to add "Trim" to the declaration like:

Trim(Format(x, "@@@@@&"))

At least I had to when I tested vbaInet's format code, other wise I got a space at the start of the resulting string.

 

[vbaInet]

ShellWorker,

You may need to add "Trim" to the declaration like:

Trim(Format(x, "@@@@@&"))

At least I had to when I tested vbaInet's format code, other wise I got a space at the start of the resulting string.

Good thing you tested it Mr. B

Great tip for the OP to trim.

Thanks

 

[Mr. B]

No, problem. I just wanted to see for myself what was going on with the format thing. You came up with an interesting fix.

Great job.

 

[vbaInet]

Cheers. Let's just hope it works for the OP

 

[DCrake]

Just a footnote to this thread the poster said that 0E111 did not work, itg may be that Access thinks that this is an expotential number and rounds it to 0 thus the formatting of 000000 is infact correct. To prove this try using other letters than E.

 

[vbaInet]

Ah yes, that's what it's doing there David. So it works but was just used for the wrong purpose.