Leading zero's variable number length and variable number of leading 0's

[VORTEGA]

I have 2 fields where I track pass numbers which need to be calculated fields. These numbers are 6 to 8 digits long and have varying number of leading 0's (very novice level) at access how can I format these fields to accept these 0s and show them

[PhilOfWalton]

as en example

format(23.83,"00000000.00")
gives 00000023.83
 

Is that what you want?

Phil

[VORTEGA]

The difficulty I am having is that no matter what I do I can only make it have an exact number of digits when I need it to be able to vary because the numbers have to be exact if I make it eight digits long then it adds additional zeros to the six digit numbers and so on how can I get it to allow variable lengths of numbers and variable numbers of leading zeros example pass numbers could be 001214 or 000503 or 0925645 depending on the pass type and the way this works is these numbers are subtracted by last number of sequence minus first number of sequence +1 to get the quantity of passes sold so they have to be number fields.

[PhilOfWalton]

Are you saying that the number 0925645 should be regarded as 2 separate numbers 0925 and 645, (I don't know where the split should be).
If so why aren't they in 2 separate fields.

AFAIK to get leading zeros, you have to format a number, and a formatted number is text. You can then get the left four letter ("0925") and convert it back to a number using the CLng function, and same thing with the rest of the text ("645")

Really need to see your tables and the calculations in more detail to advise with any certainty.

Phil

[VORTEGA]

There are two identical fields I have the issue with one is first pass used and can any combination of up to 7 numbers. The second is last pass number which is the same way. Both fields could have as many as six leading zeros or as few as no leading zeros the numbers are either 6 or seven digits long depending on the type of pass or whether we grow into more digits as the numbers become used. The two fields will be calculated as (last pass number - first pass number +1) in order to find the quantity sold from the number sequence. Example first pass # 0999998 and last pass number 1000010 or first pass # 000998 last pass # 001010 the fields have to be able to handle any number including 0000001 or 0000001 or 999999 or 0999999. Exactly as they are entered without changing the system changing the data and removing the 0s or adding 0s.

[zmbd]

VORTEGA:
In Access, numbers are numbers, doesn't matter how many leading or trailing zeros part of the number for any field that is typecast as a numeric datatype.

To make sure I am perfectly clear:
000000 == 00000 == 0000 == 000 == 00 == 0
000001 == 00001 == 0001 == 001 == 01 == 1
000002 == 00002 == 0002 == 002 == 02 == 2
(...)
099999 == 99999

Furthermore
099999 - 000001 = 099998
099999 - 00001 = 099998
099999 - 0001 = 099998
099999 - 001 = 099998
099999 - 01 = 099998
099999 - 1 = 099998

Trailing zeros before the decimal and between a decimal and any numerical value are of course a different matter as they serve as placeholders.

So if you want to have the leading zeros matter then we need to look at a text field. Perhaps if you will tell us why this formatting is so important we may be able to provide you with a better solution.

If the zeros are only for cosmetic reasons then
Open the table in design view
select the field
In the general tab of the field property enter 000000
Your numbers will always display at least six zeros (the user enters the value of zero for the field) or the correct number of zeros to pad for a six character numeric value.

[jforbes]

Typically, if you want to manage a number and preserve the leading Zeros, you would use a String instead of a Number, because leading Zeros aren't significant digits, so they are ignored by Access... and pretty much any other computer program. Zmbd does a good job at explaining why the leading zeros are not significant as they have no impact on the result of any Math performed on the Number. He also shows how to format number for leading zeros, which may be what your after.

But if you are saying you need to manage two different length numbers with varying lengths then I'm guessing you aren't really using Numbers, but String values that are comprised of Numbers. I would change your DataTypes to String and then use Val() method when you perform your Math.

[VORTEGA]

It is for inventory tracking of parking passes so unfortunately all the numbers matter, each has a unique number which may or may not have leading zeros and may be 6 or 7 digits in length currently and possibly get longer as time goes on,normally wouldn't be an issue because they normally have a two or three digit alpha prefix, but since we want to calculate the number sold based on (last pass # minus first pass # + 1= total passes sold) we have to be able to calculate the field for math purposes as well which I don't believe we would be able to do if the prefix letters were included in the fields. So currently the prefixes SC, GDP, and AHS are a drop down in a separate field. If there is a way to just allow the prefix in the field and still calculate the numbers then that would actually be the Ideal solution. Is that possible?

[zmbd]

Easiest method would be to have two fields, one with the alpha-part and the second with the numeric part.

However, I've been playing with the RegEx available in newer versions of Access now and came up with:

Function ReturnNumeric(zInStr As String) As Long
    Dim zRegExObj As Object
    Set zRegExObj = CreateObject("vbscript.regexp")
    With zRegExObj
        .Pattern = "\d+"
        If .test(zInStr) Then ReturnNumeric = .Execute(zInStr)(0)
'Why Execute()(0)? In vba test (0) not required
'in SQL test (0) require or too few augments error
    End With
    Set zRegExObj = Nothing
End Function

You can then use this function against data such as

[TicketSN_Start][TicketSN_END][Tickets_Sold]
     AG00001       AG006             5 
    BGX01568       BGX1769          201

SELECT TicketSNFeedTable.pk
   , TicketSNFeedTable.TicketSN_Start
   , TicketSNFeedTable.TicketSN_End
   , (ReturnNumeric([ticketSN_END])
      -ReturnNumeric([ticketSN_Start])) 
      AS Tickets_Sold
FROM TicketSNFeedTable;

This is a very simple SQL in that there is no check for the prefix characters (so if you take the first row of the example data above: AG006 - XYZ0001 = 5 ) nor if the ending serial number is less than the starting serial number (XYZ0001 - AG006 = -5 ) I am sure such could be implemented within SQL; however, might be easier in VBA.

Quick reference and tutorial on:
VBScript - Regular Expressions keep in mind that this is a vbscript site NOT a vba site so the other coding here will not always directly port in to VBA.

[jforbes]

That's a slick solution ZMBD. Another very helpful resource for RegEx that I've come to rely on: RegExr is an online tool to learn, build, & test Regular Expressions It's a reference site, but it also has a very well written interface to test RegExs.