Leading Zeros

[eTom]

Well, originally it was because I couldn't get that code to work. Of course, now it turns out it was a security option issue. Oops.

Now that I've gone to the trouble of creating a second Field in my tables for LotNumberText, I have extra options. We have a few products that don't actually have a LotNumber, just a best before date. I've been making up a lot number based on that date, but with a text field as well I have the option of entering my made up lot number and having the code create a text based version of the actual lot number instead.

Ideally, I could enter 010109 and have the code enter "JAN 01 2009" into the text field for me. I suppose I could have the reports do that function for me too, but if I just have it done at the form level then I never have to worry about remembering to do the formatting whenever I create some new report.

It'll also save me a lot of questions when I present reports. Every time I get asked "but I thought that product didn't have a lot number? Where'd this number come from."

 

[eTom]

Success! Mostly. I finally got the bugs out, and here is what I ended up with:

Private Sub LotNumber_AfterUpdate()
  Me.LotNumberText = Str(Me.LotNumber)
  
  Dim LotNumberLength
  LotNumberLength = Len(Me.LotNumberText)
  While LotNumberLength <= CLng(Me.FormulaID.Column(2))
    Me.LotNumberText = "0" & Me.LotNumberText
    LotNumberLength = Len(Me.LotNumberText)
  Wend
  
End Sub

Everything seems to work now. The reason it kept looping was because I had to CLng() the LotNumberDigit pulled from Column(2) of the combo box, even though it was formatted as a number in the original table. Oh well, it works.

It does however add a space between the 0's and the number I've input. If I put "0123" into the LotNumber text box, it drops the 0 (because it's formated as a Long Int) and then copies "123" into the LotNumberText text box. It then loops and adds 0's to the beginning until it reaches 5 characters, but it adds a single space. No matter what I enter I get these results:
23 -> 000 23
123 -> 00 123
1 -> 0000 1

The space always gets added between the 0's that I'm padding the Text box with and the number I input. Weird!

 

[pbaldy]

One way to find out how that's happening is to set a breakpoint and step through the code. Here's a tutorial if you're unfamiliar with that:

http://www.baldyweb.com/Debugging.htm

 

[eTom]

One way to find out how that's happening is to set a breakpoint and step through the code. Here's a tutorial if you're unfamiliar with that:

http://www.baldyweb.com/Debugging.htm

Fantastic! That did the trick. I had to create a temporary variable for the string to sit in during the loop so that I could look at it in the Locals part of the window, but that narrowed it down to the Str() function. As it turns out, Str() returns your string with a leading space if it's a positive number, I assume to leave room for the negative sign if it's a negative number.

I switched it out for CStr() instead and it worked beautifully, other than having to change my loop from <= to just <.

Your site is definitely going to be my go to place now! I tried using FieldName.Format = "00000" and that works like a charm now as well, so now I can start playing with all sorts of formatting.

Thanks again, you've been a real help!

eTom

 

[pbaldy]

Glad it helped you! I'm trying to make it a good reference.