Removing zero's from a column using an expression?

Therat

Access Denied
Local time
Today, 11:41
Joined
May 21, 2002
Messages
53
Can someone write an expression for me that removes preceeding 0's from a column? The column has a mix of Numbers and Text so changing the field type isn't an option!

For instance.....
Transform this:
0045676
068
00356796
045678
V45678

Into this:
45676
68
356796
45678
V45678

Thanks in advance!
TheRat
 
Try the val() function. Example from the
debug window:

x = val("0045676")
? x
45676

HTH - Bob
 
Thanks but....Not so fast....

In my example I had text as well. For instance the value "V45678" using val function returns a "0." I need it to return "V45678." Is there a way for an IIF statement to recognize the difference in a column between text values and number values and apply a different formula. Like:

If the field is a number use the val function, however if the field is a text, return the field.

Thanks in advance,
TheRat
 
Expr1: IIf(IsNumeric(Left([tblword],1)),Val([tblword]),[tblword])
 
Might be better to drop the Left function under some circumstances: -

Code:
Option Explicit
Option Compare Text


Sub Test()
    Dim MyField As String
    
    MyField = "0001V345"
    
    MsgBox IIf(IsNumeric(MyField), CStr(Val(MyField)), MyField)
    
    MsgBox IIf(IsNumeric(Left(MyField, 1)), CStr(Val(MyField)), MyField)

End Sub
Just in case there could be a non-numeric imbedded in the string.

Edit to add:
Another question; what is required if the field contained "-0001345"

Hope that helps.

Regards,
Chris.
 
Last edited:
I would first sort A to Z

The record at the top is the one with the most leading 0s, lets say it has 4 of them.

I would then do a query with 0000*, then 000* and so on. In other words the A to Z sort will just tell you the most the number of zero you have to deal with.

Make a Mid function

Mid([Field6],5,50) for the 0000* records, Mid([Field6],4,50) for the 000* records and so on.

I fogot to add....make another field for your table and include that in the query and then when the query is opened copy and paste the results from the field that you created with the Mid function into your new field.

Mike
 
Last edited:

Users who are viewing this thread

Back
Top Bottom