Formatting Text in VBA

  • Thread starter Thread starter Sash
  • Start date Start date
S

Sash

I need to reformat a field via VBA. But it's an odd situation.

I have a field where I store code and it may be

E800.0
E801
E8002

ONLY in the cases where it is E8002, I need to reformat it to be E800.2

In the same field I need to strip off leading zeros. Could I do something
like:

If field like 0* then
mid([field],2,10)
End if

The reason for using mid is that the length of the field varies, but is
never more than 10.
 
Okay. I figured out the E problem and stripping off the leading zero, but
how would I strip of a trailing 0 if each field is different lengths? Can
someone help???
 
This will change E8002 to E800.2:
replace([field],"E8002","E800.2")

As to removing a trailing zero:

If Right([field],1) = "0" Then
[field] = Left([field], Len([field]) -1)
End If

--
Dave Hargis, Microsoft Access MVP


Sash said:
Okay. I figured out the E problem and stripping off the leading zero, but
how would I strip of a trailing 0 if each field is different lengths? Can
someone help???

Sash said:
I need to reformat a field via VBA. But it's an odd situation.

I have a field where I store code and it may be

E800.0
E801
E8002

ONLY in the cases where it is E8002, I need to reformat it to be E800.2

In the same field I need to strip off leading zeros. Could I do something
like:

If field like 0* then
mid([field],2,10)
End if

The reason for using mid is that the length of the field varies, but is
never more than 10.
Click to expand...
Click to expand...
 
Back
Top