linking tables and leading zeros

[Guest]

DB2 mainframe tables have leading zeros on customer and contract numbers,
Access does not. Is there a way to link them so that Access believes 123456
is the same as 00123456?

 

[Guest]

Ellen,
You will need to make the fields the same format in order to link. To do
this, you could either bring the DB2 table into a query and make a field
[ContractNumber]*1 which will change that text field with leading zeros to a
number or you could use the Access table with
Format([ContactNumber],"00000000") which will put leading zeros on the number.

Hope this helps.

 

[Guest]

Ellen
You can create a query and format the field just like Jackie wrote, either
of the table, you choose, then you can use that query as a table for your
join in other queries.

 

[Guest]

Hi, Ellen.

Is there a way to link them so that Access believes 123456
is the same as 00123456?

No. The field's data type is fixed at the time of linking. One may take
the Access data from the number column and coerce the value to compare to the
DB2 string value in queries by using the CStr( ) function and a user-defined
Format( ) function to provide the leading zeros, but that's asking for
trouble -- not to mention the extra processing time necessary to use these
functions on every record in the data set.

Another way is to store the Access data as a text data type, not a numerical
data type, so that it can be compared properly to the DB2 data, but that's
asking for trouble, too -- not to mention the extra storage space needed.

So the bottom line is both methods can cause problems, so you'll need to
decide which is the least unpalatable.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[John Vinson]

DB2 mainframe tables have leading zeros on customer and contract numbers,
Access does not. Is there a way to link them so that Access believes 123456
is the same as 00123456?

I'd just amplify on Gunny's suggestion. You'll never be doing
arithmatic calculations with contract numbers, will you? If not, store
them as Text fields with leading zeros. Even if you're using
custom-counter code to increment the value, you can store the field as
text (with leading zeros), and convert it to number and back to text
in the code.

John W. Vinson[MVP]