Making field a certain length

[Guest]

I have a field [DeptNumber].

Some of the [DeptNumber] are 1, 2, 3 or 4 characters. I need to make this
field 4 characters. In other words,
2 would be 0002
12 would be 0012
112 would be 0112
1112 would be 1112

Thanks in advance.

 

[Ofer Cohen]

Use

Format([FieldName],"0000")

In a query
Select TableName.* , Format([FieldName],"0000") As NewFieldName From
TableName

If you want to update this field and have leading zero's you'll need to
change the field type to text or change the format of the field

 

[John W. Vinson]

I have a field [DeptNumber].

Some of the [DeptNumber] are 1, 2, 3 or 4 characters. I need to make this
field 4 characters. In other words,
2 would be 0002
12 would be 0012
112 would be 0112
1112 would be 1112

Make the field Text, not number - you'll never need to do arithmatic with
department numbers, I'd guess!

You can force the leading zeros a couple of ways. One would be to set an Input
Mask of 0000 to force typing of all four digits; or, you can do data entry in
a Form and use code in the textbox's AfterUpdate event:

Private Sub DeptNumber_AfterUpdate()
If Len(Me.DeptNumber) < 4 Then
Me.DeptNumber = Right("0000" & Me.DeptNumber, 4)
End If
End Sub

John W. Vinson [MVP]