Format bigint with leading zero

  • Thread starter Thread starter Iris
  • Start date Start date
I

Iris

I have searched and can't find where anyone else has had this problem, so
maybe I'm missing the obvious. But, any help would be appreciated.

I'm rebuilding an Acc2000 .mdb to an Acc2000 .adp with SQL Server back end.
I have various columns that need to be formatted to show leading zeroes on
the Access front end. The format property for the control works fine for
SSN and other smaller columns. But, when trying to format a bigint column,
the format does nothing. I really don't want to change this to a varchar
field because it is used as search criteria and I believe the search is
faster with integers, right?

Does anyone know why a bigint column won't format in Acc2000 .adp? Is there
another choice of data type other than varchar that would work instead?

Example:

0123-4567-8901-2-3

I entered 0000-0000-0000-0-0 into the format property and it still displays
on the Access form as:

1234567890123

An input mask didn't work either. It made it display as 1234-5678-9012-3-

Thanks for any help!

Iris
 
Found the solution right after posting the question.

I changed the data type to Numeric with a precision of 14 and scale of 0 and
the formatting in Access .adp works fine. So, it must be something about
the Bigint datatype that Access doesn't like.

For the benefit of others new to this (like me), here's a little more
detail.

First, I tried datatype Float because it's supposed to be the SQL Server
equivalent to the Jet datatype Double (which is what it was in the old
..mdb). But that put a decimal point in, which I didn't want, and I couldn't
find a way to take it out. The Numeric datatype allows you to specify how
many numbers you want to allow before and after the decimal point with
precision (before) and scale (after).

Thanks all!

Iris
 
Back
Top