Change number format in linked table

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
Click to expand...

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]
 
Thanks, John. This is my first attempt to use this site - I had no idea so
much help was available.

John W. Vinson said:
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.
Click to expand...

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]
Click to expand...
 
This returns a text datatype. How do set this to a number rather than a text?
--
Rick


John W. Vinson said:
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.
Click to expand...

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]
Click to expand...
 
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
 
Back
Top