Remove leading zero

[Carla]

I have two tables that I want to query against each
other, with the common field being a policy number. One
table has the policy numbers in 8-digit format with a
leading zero; the other has policy numbers in a 7-digit
format (no zero). How can I drop the leading zeros from
the 8-digit column so that they will match the numbers in
the other table? Thanks for your help.

 

[mo]

You could try using the 'Mid' function as an expression in a query:

Mid([YourField],2,7)

Look in the help files for more info.

HTH


P.S. The 'Right' function would also work.

 

[Roxie Aho]

For comparing and selecting, run a select query (query1 on
the table with the 8-character number with the fields you
need plus a field: SevenDigit: Right$([Number],7).

Then run a select query with 7-character table and
Query1. The relationship is between the policy number in
the table and the calculated field "SevenDigit" in Query1.

If for some reason, you want to PERMANENTLY change the 8-
character policy numbers to 7-character, an Update query
against the table. Right$([Number],7) in the Update To
field.

Roxie Aho
(e-mail address removed)

 

[[MVP] S. Clark]

Using the Left(), Right(), and Mid() functions, you can extract a part of
the string.

i.e. Right("01234567",7) = "1234567"

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-