stripping out leading zeros

[RichardMc]

I need to create a find unmatched query to weed out duplicate records from 2 tables.
I need to use a confirmation number field that is common to both tables.
However in table A, there are leading zeros, e.g.
0000004139
0000018763
In Table B there are no leading zeros, e.g.
4139
18763
Can I use Trim to get rid of the leading zeros? If so, some sample code would be greatly appreciated!
Thanks!

 

[dcx693]

Try using the CDbl() formula to chop off the leading zeroes and convert it to a number at the same time.

 

[RichardMc]

Thanks! But I am not sure how to use this formula. Any chance you could give me a brief example?

 

[dcx693]

OK, first the question is: do you want to permanently alter the data in tableA? You said you wanted to trim the leading zeroes, but I'm guessing you just wanted to do that for the purposes of matching up the data.

You can use the CDbl() function in a calculated field in a query. Add both tables to your query, add whatever fields you want to see to the grid, place this in the "Field:" line of an empty column:
TrimmedA:CDbl([tableA].[the_field])

This will produce a new column in your query called "TrimmedA". You can call it something more meaningful.

Now, how to match up the records from table A to those in table B? Since you now have the trimmed column of numbers from table A, you can place this in the "Criteria:" line of the new TrimmedA field:
[tableB].[the_field]
This will cause the query to return only those records where the field from table A (now trimmed of leading zeroes) equals those from table B.

 

[Mile-O]

Try using the CDbl() formula to chop off the leading zeroes and convert it to a number at the same time.

What's wrong with CLng() ?

 

[raskew]

If there are leading zeros as stored in the table it's a string, not a number. Check your field format.

You can use the val() function to return the numeric portion, e.g.

x = "0000004139"
? val(x)
4139

Bob

 

[RichardMc]

Thanks to all of you for the input!!
I took the simple way out!
Following Raskew's suggestion I looked at the field type and changed it from text to Number and the leading zeros disappeared!

 

[InQuery2004]

Hi,

To add another level of complexity to this post, I have the following problem. While I too would like to eliminate leading zeros from a text field. Not all of my records in that field are numeric. Some values for instance are "0123A" or "0054B". If I am to use the Val function or Cdbl, then I would lose the traling letter, which is not acceptable.

Any ideas, how to eliminate leading zeros from a strictly text field??

Thanks guys.

Mike J.

 

[raskew]

This little function will strip out leading zeros from a string, returning the remainder of the string.

Function StripZeros(pstr As String) As String
Dim n As Integer

    n = 1
    Do While Left(pstr, n) = "0"
       n = n + 1
    Loop
    n = IIf(n > 1, n + 1, 1)
    StripZeros = Mid(pstr, n)
    
End Function

HTH - Bob

 

[Dominic1980]

I have a question regarding to trimming a number value. I have a number which is actually the date/time and a string of unknown digits. like for e.g.
20051130090135xxxxxxxxxxxxxxx

I need to remove the xxxxxxxx behind to just get 20051130090135 so that i can format it to date/time 30/11/2005 09:01:35

Anyone can shed some light on this?

Regards
Dom

 

[tkpstock]

This little function will strip out leading zeros from a string, returning the remainder of the string.

Function StripZeros(pstr As String) As String
Dim n As Integer

    n = 1
    Do While Left(pstr, n) = "0"
       n = n + 1
    Loop
    n = IIf(n > 1, n + 1, 1)
    StripZeros = Mid(pstr, n)
    
End Function

HTH - Bob

Did you check your code to make sure it works?
Try this:

Function StripZeros(pstr As String) As String
Dim n As Integer

    n = 1
    Do While Mid(pstr, n, 1) = "0"
       n = n + 1
    Loop
    n = IIf(n > 1, n, 1)
    StripZeros = Mid(pstr, n)
    
End Function

 

[raskew]

Tom -

Did I check my code? Sure as hell thought I did, but you're right.

Good catch.

Thanks,

Bob

 

[tkpstock]

Tom -

Did I check my code? Sure as hell thought I did, but you're right.

Good catch.

Thanks,

Bob

Sometimes I find myself coding directly in the reply box - I try to avoid it, but sometimes it is easier...