Change number format in linked table

[Guest]

I need to make a table using fields from a linked Oracle database. The SSN
field in the Oracle database is a Number, not text. Where there are leading
zeros, they are dropped. I need a way to replace the leading zeros in my new
table.

 

[John W. Vinson]

I need to make a table using fields from a linked Oracle database. The SSN
field in the Oracle database is a Number, not text. Where there are leading
zeros, they are dropped. I need a way to replace the leading zeros in my new
table.

You can use a calculated field:

Format([numberfield], "000000000")

or, if you want to actually store the hyphens,

Format([numberfield], "000-00-0000")

This function will return a Text datatype value which can be appended into a
text field in your local table.

John W. Vinson [MVP]

 

[Guest]

Thanks, John. This is my first attempt to use this site - I had no idea so
much help was available.

I need to make a table using fields from a linked Oracle database. The SSN
field in the Oracle database is a Number, not text. Where there are leading
zeros, they are dropped. I need a way to replace the leading zeros in my new
table.

You can use a calculated field:

Format([numberfield], "000000000")

or, if you want to actually store the hyphens,

Format([numberfield], "000-00-0000")

This function will return a Text datatype value which can be appended into a
text field in your local table.

John W. Vinson [MVP]

 

[rbeach]

This returns a text datatype. How do set this to a number rather than a text?
--
Rick

I need to make a table using fields from a linked Oracle database. The SSN
field in the Oracle database is a Number, not text. Where there are leading
zeros, they are dropped. I need a way to replace the leading zeros in my new
table.

You can use a calculated field:

Format([numberfield], "000000000")

or, if you want to actually store the hyphens,

Format([numberfield], "000-00-0000")

This function will return a Text datatype value which can be appended into a
text field in your local table.

John W. Vinson [MVP]

 

[John Spencer MVP]

SSN should be text. You won't use them for arithmetic.

IF you need to store leading zeroes in the field then you cannot store the
value as a number, you have to store the data as text.

If you insist on having the SSN stored as a number then your only choice is to
format the SSN when you display it using either the format property of the
control set to "000-00-0000" or "000000000" or using the format function in
the underlying query to return the formatted value.

Format(SSN,"000-00-0000")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County