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.

On Tue, 24 Apr 2007 17:02:01 -0700, Judy <(E-Mail Removed)>
wrote:

>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]

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

"John W. Vinson" wrote:

> On Tue, 24 Apr 2007 17:02:01 -0700, Judy <(E-Mail Removed)>
> wrote:
>
> >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]
>

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


"John W. Vinson" wrote:

> On Tue, 24 Apr 2007 17:02:01 -0700, Judy <(E-Mail Removed)>
> wrote:
>
> >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]
>

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

rbeach wrote:
> This returns a text datatype. How do set this to a number rather than a text?