Format a field with leading zeros 1

[sjgoodin]

Need help on formatting a numeric field. Field length is 7 characters. I want to allow users to enter less than 7 digits and if less than 7 digits, automatically add leading zeros and have it stored that way.

 

[SkipVought]

sjgoodin,

Numbers are STORED as numbers. Leading ZEROES are only a display format.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[sjgoodin]

Thanks, Skip. While I agree with you it doesn't help. The required (government) specs of the data my table will hold requires leading zeros on numbers that are less than 7 digits. I am only trying to make it convenient for the user not to have to enter a number like "123" as "0000123".

 

[earthandfire]

That being the case then you will have to store the numbers as text.

You would then need to trap the OnKeyPress Event to ensure only numeric values were entered and then one of the exit events ie Lost Focus, Exit, Before / After Update etc. to ensure the correct formatting, so that the correct value is written back.

Hope this helps.

 

[PHV]

And what about an input mask set to 0000000 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[earthandfire]

PHV, I had thought of that. The problem there, however, is that there is nothing to force the user to fill the data from the right. Therefore one or other of the exit events will still need to be trapped. Additionally the data has to be stored with the leading zeroes, which is why I opted for the key handling event.

 

[DrSimon]

I can't see a way of doing this as a Table field property either. But this function will do the conversion:

Function SevenLeading(InNum As Long) As String
Dim NumStr As String
NumStr = Format(InNum, "0")
SevenLeading = Left("0000000", 7 - Len(NumStr)) & NumStr
End Function

Simon Rouse