Is it possible to have a number field that retains leading zeros? 1

[Dan01]

Hi, we have a number field for a customerID#. Some customers have numbers with leading zeros. Is it possible to set the table to retain the leading zeros? For example, when the ID# 00020344 is entered, the field only retains 20344, while dropping the leading zeros. Thanks, Dan.

 

[ksgirl2]

Hi,
We have the same thing with our product number ..leading zeros. I set the feild to text and just made it only 8 digits long. (or for you however long all your customer id #'s are if they are a set length)
That's one way unless you really really have to have it as numbers, then I'm not sure.

 

[fvsj33a]

i think you might be able to format as "00000" (0 for each character, forces to be 0 or number) but if number is different sizes this won't work

 

[kd1s]

Make the field a text field. Then set a validation rule for ######## so it'll only take numbers on the form.

I then do an event procedure after update to grab the value from the field, look at it's length and pad zeros where needed and stuff it back into the field. I kludged this one together but it works for my purposes - in my case I had to tag the date into the field.

Private Sub Add_Click()

On Error GoTo Err_Add_Click


DoCmd.GoToRecord , , acNewRec
'Forms![F_Case]!AGNo.Value = ""
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim crser As Long
Dim ser As String
Dim ser1 As String
Dim ser2 As String
Dim dt As Date
Dim ys As String
Dim an As String

' Now function returns current date/time
dt = Now
'Year function with RIGHT string operation to strip first two year chars out.
ys = Right(Year(dt), 2)


' Now get the current serial number
Set dbs = CurrentDb
strSQL = "SELECT * FROM T_Serial"
Set rst = dbs.OpenRecordset(strSQL)

With rst
crser = .Fields("Count&quot
End With
rst.Close
dbs.Close

'First convert numeric value to string value
ser = Str(crser)
'Strip out leading space to get number without sign placeholder
ser1 = Right(ser, Len(ser) - 1)

'Test length of serial number to see if leading zero padding is required.
If Len(ser1) = 1 Then
ser2 = "000" + ser1
End If

If Len(ser1) = 2 Then
ser2 = "00" + ser1
End If

If Len(ser1) = 3 Then
ser2 = "0" + ser1
End If

If Len(ser1) = 4 Then
ser2 = ser1
End If

'Now create composite string with yy-nnnn format.
an = ys + "-" + ser2

'increment serial count by one
crser = crser + 1

'Write value back to table.
Set dbs = CurrentDb
strSQL = "SELECT * FROM T_Serial"
Set rst = dbs.OpenRecordset(strSQL)
With rst
.Edit
.Fields("Count&quot = crser
.Update
End With

rst.Close
dbs.Close

'Now open the form
DoCmd.GoToRecord , , acNewRec
Forms![F_Case]!AGNo.Value = an

Exit_Add_Click:
Exit Sub

Err_Add_Click:
MsgBox err.Description
Resume Exit_Add_Click

End Sub

 

[Dan01]

Hi,
ksgirl2, kd1s: thanks for the suggestion. That was the first thing I thought of also. Unfortunately, this table is linked to multiple tables,and is even imported into a second database where further table links with customerid# occur.

fvsj33a, thanks also for the suggestion. This one looks like it will solve the problem.

A good weekend to all of you! Dan.

 

[Rolliee]

I would save the number ID as a string. YOu can do it with a str(val(variable)). Then set a variable
str0 = = "00000000000"

Now you can concatenate with new value

nuVal = right(str0, 10 - len(str(val(variable)) &_
len(str(val(variable))

It is now 10 characters long with leading zeros.

Rollie E

 

[raskew]

Storing a number and displaying it are two different issues.
For the most efficient use of memory, store it as a number.
Here's an example you can try in the debug window:

mynum = 123
fmt = "000000"
? format(mynum, fmt)
000123
? format(mynum * 333, fmt)
040959

 

[Rolliee]

Nice one Raskew!!

Rolliee

 

[tlbroadbent]

I agree with raskew. Developers must learn to separate storage format from display or presentation format. When you can get over the obstacle of thinking a number or date or anything else is stored the way you see it, you'll overcome a lot of problems.

MS Access makes it difficult to separate the two concepts because the development, dipsplay and storage environments are so integrated. We open a table and see a date displayed as mm/dd/yyyyy and think that is the storage format. Not so. Dates are stored as numbers.

We can set a display format for numbers in a data view, on a form or in a report. We begin to think the number is stored that way. Not so. The program is interpreting and formatting for us.

Bottom line - Store numbers as numbers and format them the way you want for presentation to users. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Dan01]

FYI: I set the format to 000000000 for the number and that works fine. However, when users tried to search for a number with a leading zero, Access could not find the record. When I had them click the "Search field as formatted" in the search dialog box, Access then found the record. We closed the Access database, and restarted it. Then we searched with the number with a leading zero; selection of "Search field as formatted" was no longer required. Access remembered the previous setting. Dan.

 

[tlbroadbent]

Dan,

Thanks for the final feedback. Access is a great tool because of its power and flexibility. As long as the developer understands storage and formatting, the end user doesn't have to know as much. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.