Converting Text to a Integer,

[Jim S]

I have two databases I am trying to join together. One is in a text format,
the other is in number format.

The text number is ssn. I am trying to link the databases together but when
I try to convert the text to integer, i get the Invalid use of null. I know
it is because I am trying to convert null to integer.

How can i convert the text to a number or a data fomat where I wont loose
the 0's to the left of number.

The most success I ve had is converting both forms to string, but when I
conver ssn to string I lose the 0's, so now only the ones that dont start
with a 0 match.

I am a new user.

How can i convert this text to a integer? or what other data type should i
use?
One table is type int. 9 place,, the other is text.

 

[Douglas J. Steele]

Use the Nz function to convert the Null to a value first.

To convert the integer value to a string, use the Format function to include
leading zeroes:

Format(Nz([IntegerValue], 0), "000000000")

To convert the text value, you'll need to use the CLng function (because a
SSN is bigger than an Integer value can be):

CLng(Nz([TextValue], "0"))

 

[Stefan Hoffmann]

hi Jim,

I have two databases I am trying to join together. One is in a text format,
the other is in number format.

Huh? Databases?

The text number is ssn. I am trying to link the databases together but when
I try to convert the text to integer, i get the Invalid use of null. I know
it is because I am trying to convert null to integer.

How can i convert the text to a number or a data fomat where I wont loose
the 0's to the left of number.

You cannot conver a string to a number and preserve leading zeros, cause
these have no mathematical meaning.

The most success I ve had is converting both forms to string, but when I
conver ssn to string I lose the 0's, so now only the ones that dont start
with a 0 match.

How do you convert it?

One table is type int. 9 place,, the other is text.

The only thing you may try:

Link the fields as string, e.g.

Format("000000000", [intSSN]) = txtSSN


mfG
--> stefan <--

 

[Jim S]

Use the Nz function to convert the Null to a value first.

To convert the integer value to a string, use the Format function to include
leading zeroes:

Format(Nz([IntegerValue], 0), "000000000")

To convert the text value, you'll need to use the CLng function (because a
SSN is bigger than an Integer value can be):

CLng(Nz([TextValue], "0"))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have two databases I am trying to join together. One is in a text format,
the other is in number format.

The text number is ssn. I am trying to link the databases together but
when
I try to convert the text to integer, i get the Invalid use of null. I
know
it is because I am trying to convert null to integer.

How can i convert the text to a number or a data fomat where I wont loose
the 0's to the left of number.

The most success I ve had is converting both forms to string, but when I
conver ssn to string I lose the 0's, so now only the ones that dont start
with a 0 match.

I am a new user.

How can i convert this text to a integer? or what other data type should i
use?
One table is type int. 9 place,, the other is text.

*************Hey thanks a million i finally got them to work together!!!!!!!!!!! noboday at my work could, event the it department!!!!!!!!!! YES THANKS!!!!!!!!