access 2003 text display leading zero

Question

We are currently using Access 2003. I have a vehicle maintenance log which has a text field for vehicle numbers. The field needs to be text as it will have a R in it when a vehicle is retired. They would like to have the field be a four digit number with leading zero's. So vehicle 22 would be displayed in the table and reports as 0022 and when retired it would be R0022. I have tried to change the format of the field to "0000" and "0000"# but neither of these will display the leading zero's.

Answer 1

Do you really want you users to manually edit that field?
I don't like solutions like this, because it's error-prone (unless you check a lot of things to make sure that no one enters invalid data) and feels unelegant to your users.

I would just save the following in the table:

• the vehicle number in a numeric field (22, not 0022)
• a boolean field which indicates if the vehicle is retired

This is much easier for your users to work with:

• they can just enter new vehicle numbers without having to think about leading zeros
• to retire a vehicle, they just need to set a checkbox, instead of putting the right letter in front of the vehicle number

Plus, showing the desired number R0022 now becomes just a matter of displaying/formatting the data from the table:

Public Function GetDisplayNo(VehicleNo As Integer, IsRetired As Boolean) As String

    GetDisplayNo = IIf(IsRetired, "R", "") & Right("0000" & VehicleNo, 4)

End Function

You can use this function like this:

GetDisplayNo(22, True) returns R0022

GetDisplayNo(22, False) returns 0022

And if you need to display a list of vehicle numbers in a report or a continuous form, you can directly use this function in the underlying query:

SELECT 
    Vehicles.VehicleNo, 
    Vehicles.IsRetired,
    GetDisplayNo([VehicleNo],[IsRetired]) AS DisplayNumber
FROM Vehicles;