Adding 'leading' Zeroes?

[JPed]

Hi,

I have a form which a order number is put in and is compared to data which has been pulled from the system. The issue I'm having is that that on the software we take the orders on it adds zeroes in front of the numbers to make it up to an 8 digit sequence, but generally when people input into the Access system they are ignoring the zeroes.

I have found some answers how to get zeroes in front of a sequence of numbers (ie 1234 to 00001234) however our order numbers also have different two letter combination at the front depending on the product type.

Is it possible to somehow edit a text box on a form so that after AJ1234 is inputted it would automatically change to AJ00001234, or at the very least be able to perform a DLookUp on data which is in the form AJ00001234?

Thanks for any help,
Regards.

 

[wader]

I would try an imput mask such as LL99999999 would that work for you?

 

[Mile-O]

Why not use an Input Mask on your Control (or the Field) to ensure that users enter it just the way you want?

EDIT: Posted in sync with wader.

 

[wader]

You could also change the field to a drop down if I think what your saying is that the correct sequence already exists in the data..

 

[JPed]

You could also change the field to a drop down if I think what your saying is that the correct sequence already exists in the data..

This is what I tried originally but we have a lot of these order numbers so it just looked like a mess and I didn't know whether it was possible to have the order numbers leave the box once they'd been processed/shipped.

I have set an input mask so that it only allows for a certain amount of letters which is a good idea and an improvement, but if it's possible just to fill in the 0's that would be better.

One possibility might be to have two boxes? One for the AJ/BJ/ZN etc and then one for the numbers which should be able to have leading zeroes up to a certain point?

 

[wader]

You could also use the after update event to pad the number with zeros but that might be failry complex string manipulation....i.e. you would have to somehow test for and seperate the text and numbers in code then add the zeros put the whole string back together and then update the original value.

It might be easier to keep the input mask with padded numbers and maybe put an example of how the entry should be done on the form in a label.....also wondering if this wouldn't be a better input mask for you AA00000000

 

[JPed]

You could also use the after update event to pad the number with zeros but that might be failry complex string manipulation....i.e. you would have to somehow test for and seperate the text and numbers in code then add the zeros put the whole string back together and then update the original value.

It might be easier to keep the input mask with padded numbers and maybe put an example of how the entry should be done on the form in a label.....also wondering if this wouldn't be a better input mask for you AA00000000

If I could do anything close to anything on Access I'd try something fancy but for now unless someone has a simple solution just gonna go with the input mask.

Just wondering though is it possible to get it so that lets say you miss a zero out and are a digit short you can go back and add that without having to rewrite it again? At the moment when I begin typing it has a thick flashing bar rather than the usual thing one (almost as if I've pressed the insert key) and its over writing what is already there.

 

[wader]

Not sure...might just have to play with the properties to see if it will do what you want.

 

[MarkK]

You can return a variable number of characters from the VBA.String() function. Here's some example code. Masks can be a headache . . .

Private Sub Test12938641920384()
    Dim vTest
    Dim var
    Dim tmp As String
    
    VBE.Windows("Immediate").Visible = True         [COLOR="Green"]'show the immediate pane[/COLOR]
    
    vTest = Split("756 5268 160345.12 157 1 xx a")  [COLOR="Green"]'create a list of test data[/COLOR]
    For Each var In vTest
        tmp = AddLeadingChars(CStr(var), 12, ".")   [COLOR="Green"]'call the function for each[/COLOR]
        Debug.Print tmp, Len(tmp)                   [COLOR="Green"]'print the result[/COLOR]
    Next
    
End Sub

Private Function AddLeadingChars(ToText As String, ToLength As Long, UseChar As String) As String
    AddLeadingChars = [COLOR="Blue"]String[/COLOR](ToLength - Len(ToText), UseChar) & ToText
End Function

So see how the Function AddLeadingChars() takes the string, the target length and the character to add, and adds as many as are required to create a string of the desired length.

 

[JPed]

I have managed to get it to add leading zeros by using the following code

Private Sub OrderNumbertxt_LostFocus()
OrderNumbertxt = Format([OrderNumbertxt], ">@@00000000")

However this will basically return 8 0s no matter how many numbers are in the order number? I'm trying to make the number up to 8 digits long so if it were BJ12345678 it would stay the same whereas BJ123456 would become BJ00123456. Is there anything else I can do to get to this?

 

[JPed]

So something I've tried to do now is to have two text boxes which are next to each other; one where BJ/CJ/etc is typed and one where its just numbers and auto adds the zeros.

I've managed to do both these boxes seperately which then link back to the main table and combine to create the order number, I just now want to make it auto-tab from the BJ/CJ box into the numbers as soon as two digits are entered.

I've set the field size to 2 and turned auto-tab on but it doesn't tab.

Where am I going wrong?

 

[wader]

So something I've tried to do now is to have two text boxes which are next to each other; one where BJ/CJ/etc is typed and one where its just numbers and auto adds the zeros.

I've managed to do both these boxes seperately which then link back to the main table and combine to create the order number, I just now want to make it auto-tab from the BJ/CJ box into the numbers as soon as two digits are entered.

I've set the field size to 2 and turned auto-tab on but it doesn't tab.

Where am I going wrong?

You can either set the tab order for the two controls so that the user can tab from one to the next (I'm forgetting which version you said you had). In access 2007 you can set that in the control properties window on the other tab where it says tab index...and they just need to be sequential..ie if the one control is 5 then the next is 6. Or you can set the focus to the second control in the after update event on the first control you have. I was also doing some validation work on one of my forms and realized that I failed to mention a validation might be simpler if you dont want to do the option your exploring at the moment. I.e. the Validation rule would be something like Like "LL00000000" and then your validation Text would be the instructions if the validation failed. Just another idea...good luck

 

[MarvinM]

JPed,

My first question goes back to how you are using this on the form. It sounds like you are accepting an entry in an unbound text box, want to convert it to the proper format, and then compare it with data in a table. Maybe query the table or something, right?

If it's an unbound control, I don't think the After Update event will work. I would go with the On Exit event.

Second, it seems like the first 2 characters are letters and the rest are numerals. Is that right?

Let's try this:

Private Sub TextBox1_Exit(Cancel As Integer)
    Dim strNew as String
    strNew = Mid(TextBox1.Value, 1, 2) & Format(Val(Mid(TextBox1.Value, 3))[COLOR=black][FONT=Verdana],"00000000")[/FONT][/COLOR]
    TextBox1.Value = strNew
End Sub

I admit there are other ways to do this. But I think this is going the direction you were thinking.

• We take the input from the user in TextBox1.
• We separate the first 2 characters from the rest of the string with the Mid function.
• We convert the numerals that begin at character 3 to a number with the Val function.
• We Format the number into an 8 digit string including leading zeros.
• We concatenate the 2 strings into one.
• Then we set the TextBox1 value to the new string we created.

Please let us know if this works. Thanks.