Dropping leading 0's?

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I have a file that I am comparing with a customer file.

The customer file has all the codes without 0's like this.

123456
111111
222222

Our report has it with 0's like this.

0123456
000111111
00222222

I need to compare these 2 fields to bring back which codes
do not match.

I am thinking the easiest way is to drop the 0's in front
of our report then compare..

1. How do you drop leading 0's?
2. Is there an easier way to do this?

Thanks
Scott
 
If the text fields that you are comparing only contain numbers then consider
convert the strings to numbers and then compare. If possible, just convert
the fields to numeric instead of storing as text.

Being the overly cautious person that I am I would first verify that the
value to be converted was numeric before doing the conversion. That way the
program won't break when values like XXXXX show up in the text fields.
 
Unfortunately all of the fields are not just test..they
are a mixture of text and numbers..

I just need to remove any leading zeros

Scott
 
I have a file that I am comparing with a customer file.

The customer file has all the codes without 0's like this.

123456
111111
222222

Our report has it with 0's like this.

0123456
000111111
00222222

I need to compare these 2 fields to bring back which codes
do not match.
Click to expand...

Gnnn... There's no really easy way to do this. If you want to do it
permanently (i.e. trim out the zeros and leave them out) you can
create an Update query with just this field; use a criterion of

LIKE "0*"

and update to

Mid([fieldname], 2)

This will trim off a single leading zero. Run the query over and over
until it's done the two, three and four zero records.

If you want to continue to store an arbitrary number of leading zeros,
you'll need some VBA. Here's some AIR CODE, untested:

Public Function TrimZero(strIn As String) As String
Dim iPos As Integer
TrimZero = ""
If Len(strIn) = 0 Then Exit Function
For iPos = 1 to Len(strIn)
If Mid(strIn, iPos, 1) <> "0" Then
TrimZero = Mid(strIn, iPos)
Exit Function
Next iPos
TrimZero = strIn
End Function

Copy this into a module, compile, and use

TrimZero([fieldname])

in your comparison.
 
scott said:
I have a file that I am comparing with a customer file.

The customer file has all the codes without 0's like this.

123456
111111
222222

Our report has it with 0's like this.

0123456
000111111
00222222

I need to compare these 2 fields to bring back which codes
do not match.

I am thinking the easiest way is to drop the 0's in front
of our report then compare..

1. How do you drop leading 0's?
2. Is there an easier way to do this?
Click to expand...
Hi Scott,

Another alternative might be to create
an additional match field in a preliminary query
on your "report" table, then compare this query to
the "customer file" table.

SELECT *,
IIF(IsNumeric(f1)=-1, CStr(CLng(NZ(f1,0))), f1)
AS MatchField
FROM ReportTable;

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Rethinking, you won't need NZ()
because nulls will fail on IsNumeric test.
so...

SELECT *,
IIF(IsNumeric(f1)=-1, CStr(CLng(f1)), f1)
AS MatchField
FROM ReportTable;
 
WOW this is great..I used


LIKE "0*"

and update to

Mid([fieldname], 2)


and it worked great..I printed out the help file on MID so
I can understand what it does.

Thanks!
Scott
-----Original Message-----
I have a file that I am comparing with a customer file.

The customer file has all the codes without 0's like this.

123456
111111
222222

Our report has it with 0's like this.

0123456
000111111
00222222

I need to compare these 2 fields to bring back which codes
do not match.
Click to expand...

Gnnn... There's no really easy way to do this. If you want to do it
permanently (i.e. trim out the zeros and leave them out) you can
create an Update query with just this field; use a criterion of

LIKE "0*"

and update to

Mid([fieldname], 2)

This will trim off a single leading zero. Run the query over and over
until it's done the two, three and four zero records.

If you want to continue to store an arbitrary number of leading zeros,
you'll need some VBA. Here's some AIR CODE, untested:

Public Function TrimZero(strIn As String) As String
Dim iPos As Integer
TrimZero = ""
If Len(strIn) = 0 Then Exit Function
For iPos = 1 to Len(strIn)
If Mid(strIn, iPos, 1) <> "0" Then
TrimZero = Mid(strIn, iPos)
Exit Function
Next iPos
TrimZero = strIn
End Function

Copy this into a module, compile, and use

TrimZero([fieldname])

in your comparison.


.
Click to expand...
 
Hopefully you backed up
your db first. If your example
data was correct, you just lost
some non-zero digits and you
still have some leading zeroes:

[examplefield] Mid(examplefield],2)
0123456 23456
000111111 0111111
00222222 222222

scott said:
WOW this is great..I used


LIKE "0*"

and update to

Mid([fieldname], 2)


and it worked great..I printed out the help file on MID so
I can understand what it does.

Thanks!
Scott
-----Original Message-----
I have a file that I am comparing with a customer file.

The customer file has all the codes without 0's like this.

123456
111111
222222

Our report has it with 0's like this.

0123456
000111111
00222222

I need to compare these 2 fields to bring back which codes
do not match.
Click to expand...

Gnnn... There's no really easy way to do this. If you want to do it
permanently (i.e. trim out the zeros and leave them out) you can
create an Update query with just this field; use a criterion of

LIKE "0*"

and update to

Mid([fieldname], 2)

This will trim off a single leading zero. Run the query over and over
until it's done the two, three and four zero records.

If you want to continue to store an arbitrary number of leading zeros,
you'll need some VBA. Here's some AIR CODE, untested:

Public Function TrimZero(strIn As String) As String
Dim iPos As Integer
TrimZero = ""
If Len(strIn) = 0 Then Exit Function
For iPos = 1 to Len(strIn)
If Mid(strIn, iPos, 1) <> "0" Then
TrimZero = Mid(strIn, iPos)
Exit Function
Next iPos
TrimZero = strIn
End Function

Copy this into a module, compile, and use

TrimZero([fieldname])

in your comparison.


.
Click to expand...
Click to expand...
 
Please disregard my ignorant post above.
If I could cancel it, I would.
Gary Walter said:
Hopefully you backed up
your db first. If your example
data was correct, you just lost
some non-zero digits and you
still have some leading zeroes:

[examplefield] Mid(examplefield],2)
0123456 23456
000111111 0111111
00222222 222222

scott said:
WOW this is great..I used


LIKE "0*"

and update to

Mid([fieldname], 2)


and it worked great..I printed out the help file on MID so
I can understand what it does.

Thanks!
Scott
-----Original Message-----
Click to expand...
wrote:

I have a file that I am comparing with a customer file.

The customer file has all the codes without 0's like this.

123456
111111
222222

Our report has it with 0's like this.

0123456
000111111
00222222

I need to compare these 2 fields to bring back which codes
do not match.

Gnnn... There's no really easy way to do this. If you want to do it
permanently (i.e. trim out the zeros and leave them out) you can
create an Update query with just this field; use a criterion of

LIKE "0*"

and update to

Mid([fieldname], 2)

This will trim off a single leading zero. Run the query over and over
until it's done the two, three and four zero records.

If you want to continue to store an arbitrary number of leading zeros,
you'll need some VBA. Here's some AIR CODE, untested:

Public Function TrimZero(strIn As String) As String
Dim iPos As Integer
TrimZero = ""
If Len(strIn) = 0 Then Exit Function
For iPos = 1 to Len(strIn)
If Mid(strIn, iPos, 1) <> "0" Then
TrimZero = Mid(strIn, iPos)
Exit Function
Next iPos
TrimZero = strIn
End Function

Copy this into a module, compile, and use

TrimZero([fieldname])

in your comparison.


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top