Removing Leading 0

[AccessIM]

I am trying to link two tables using a [PONumber] field but the po # in one
of the tables includes leading 0s and the other does not. As a result,
Access doesn't see any of them as matches.

I searched the posts and found how to remove leading spaces but found this
does not apply for leading 0s.

Is this possible? Thank you in advance.

 

[tkelley via AccessMonster.com]

Try something like this ... (more than one way, but this is easier for me to
explain)

Make a query on table1 by itself (the one with the zeroes).

In it, create a new expression field:

PONoZero: Mid([PONumber],1,Len([PONumber]-1))

If the [PONumber] field in the other table isn't a string, but a number:
PONoZero: Clng(Mid([PONumber],1,Len([PONumber]-1)))

Then in your second query, add query1 instead of the table, then link on that
expression field, [PONoZero].


That's off the top of my head ... you may have to tweak it. Hope it helps.

I am trying to link two tables using a [PONumber] field but the po # in one
of the tables includes leading 0s and the other does not. As a result,
Access doesn't see any of them as matches.

I searched the posts and found how to remove leading spaces but found this
does not apply for leading 0s.

Is this possible? Thank you in advance.