I've built a simple complaint tracking system, and part of the data collected is a tracking number. With the year turning over to 2010, the first digit is now a zero. This zero gets dropped when entering, and one some products that end up with two leading zeros, our tracking number loses two out of the five digits.

Is there any way to prevent this and force this particular field to keep a minimum of five digits, regardless of leading zeros? It's currently a Long Int, but if I convert it to a string will it still be sortable the same for all my reports and such?

Thanks,

eTom

Depending on your specifics, you can leave it as a Long and format it on forms and reports to display the leading zeros, or change it to Text. You'd format it like so:

Format(FieldName, "00000")

We have two main categories of products, and because they come from different manufacturers they have different tracking number formats. Is it possible to make it conditional?

The data on which product something is comes from a separate table of course, so I could easily add a field for "TrackingFormat" and enter either 5 or 6 for each product.

Could I then use that to format the reports and forms to conditionally display 5 or 6 digits?

Sure, you could test that field with an IIf() and format it differently based on the result.

Sure, you could test that field with an IIf() and format it differently based on the result.

Would this make sense on a form for entering product testing data:

1. User selects product from a ComboBox (populated from a products table.)

2. On Change event: IF statement formats the tracking number field on the form to 5 or 6 digits. Each product in the product table contains a ManufacturerID which is linked to a primary key in a Manufacturers table. This manufacturer table also has a field for "TrackingNumberDigits"

ie: If [tblManfacturers]![TrackingNumberDigits] = 5 then [LotNumber].Format = 00000

I know the syntax is horribly awry, but I haven't gotten that far yet. Still in the planning stage. It makes sense logically, yes?

Thanks again!

eTom

Alright, well that didn't work. I narrowed it down to needing to use the VB Code builder, and this is what I was thinking:

For a report: On Load:


Private Sub Report_Load()

Report_Load = IIf(tblManufacturers.LotNumberDigits = 6, LotNumber.Format = "000000", LotNumber.Format = "00000")


End Sub


edit: I just double checked and made sure that I have added the LotNumberDigits field from tblManufacturers to the query that this report is based on. Also, I do realize that the field names have changed since previous posts. I hadn't actually coded anything so I didn't settle on Field names until this final post.

Unfortunately, it doesn't work. Any suggestions?

Thanks!

You could try this as the control source:

=IIf(LotNumberDigits = 6, Format(LotNumber, "000000"), Format(LotNumber, "00000"))

Using code, you want it in the format event of the section containing the control, and it would look like:

If Me.LotNumberDigits = 6 Then
Me.LotNumber = Format(LotNumber, "000000")
Else
Me.LotNumber = Format(LotNumber, "00000")
End If

You could try this as the control source:

=IIf(LotNumberDigits = 6, Format(LotNumber, "000000"), Format(LotNumber, "00000"))

The control source for what, the Text Box on the Report? I get an error about it being a circular reference, and the report displays #Error for the lot number.


Using code, you want it in the format event of the section containing the control, and it would look like:

If Me.LotNumberDigits = 6 Then
Me.LotNumber = Format(LotNumber, "000000")
Else
Me.LotNumber = Format(LotNumber, "00000")
End If

I tried using this in the Report's "On Load" to no avail. That's where I would want to script this, yeah?

Edit: I just realized that you said in the Format event for the section containing the control. I tried the Format even for both the header and the detail section, and it still leaves 012345 as just 12345.

Thanks again!

eTom

For the first, make sure no control has the same name as either of the fields (which is what the wizard will do). For the second, make sure the textbox isn't bound to a numeric field, and doesn't have a numeric format. I suspect either of those will override the Format() function, though that is untested.

Hmmm, thanks, I'll give that a shot. Here's what I think is an interesting question. The report list products from both manufacturers, so some records would need five digits and some six.

Would this format the field for the entire report worth of records all based on, say, the last record? Or should it format each line individually? The way I assume the report builds itself, I'd think that it would essentially write a new header/detail/footer to the screen for each record, so it should be capable of formatting line by line.

Now that you mention non-numeric fields, it reminded me. The reason I didn't just use a text field in the first place (which would easily allow me to just enter as many 0's as I wanted) is that I then can't sort it by LotNumber for reports and such.

It'll still sort, but it does it A-Z. For example: 100 would come before 20.

Is there a way around that, perhaps?

Edit: Well, I love a challenge and programming itself, but I'm also VERY very lazy. I decided to create another field called LotNumberText. The report still sorts data based on LotNumber, but it actually displays LotNumberText. For now I've just added the 0's by hand, but now that all the records are up to date it's just a matter of programing my data entry forms to fill in the LotNumberText field with the appropriate number of leading 0's in the case of any.

It strikes me that it may be easier to enter the LotNumberText, and then use a function to copy the data into the numeric field. It'd automatically drop any leading zeros and I wouldn't have to mess around with if statements to decide how many it needs to add, and whether it needs to add any at all (in the case that the number starts with a zero, thus not having any leading zeros in the first place.)

Does that make sense? Sorry to use this forum as a sounding board, but sometimes putting my ideas down on... er... paper (?) and getting a second opinion really helps. That, and I am in way over my head here sometimes.

100 will come before 20, but not before 020

On the other hand just putting a format on the control of "0000000000" will display any missing zero's.
By happy mistake you did it right, numbers should be stored as numbers... not as strings if only to prevent a user from entering 4 zeros instead of 5.

100 will come before 20, but not before 020
True, but I have some that are 5 digits, some that are 6. Ideally I don't want 090000 coming before 10000, for example.


On the other hand just putting a format on the control of "0000000000" will display any missing zero's.
By happy mistake you did it right, numbers should be stored as numbers... not as strings if only to prevent a user from entering 4 zeros instead of 5.
So would it make sense to set the entry box on the forms to LotNumberText, have them enter the number and then use something like CLng to convert the string to a Long Int? I guess the problem with that there's nothing stopping someone from entering some wild and crazy string of letters.

I'll have to have them enter a number into the actual Long Int field and then have Access convert it for me. At least that way Access will prevent them from trying to enter any letters, right?

Would this format the field for the entire report worth of records all based on, say, the last record? Or should it format each line individually?

Either method should format each record individually.

Well, I've started thinking that my understanding of VB and Access aren't as good as I thought, as I can't get anything to work.

I'm now starting extremely simple. I created a form that will let me input a number into a text box. It also has another text box ("LotText").

On the event tab of TextBox1 ("Lot") I've used the code builder to create the following:

Private Sub LotNumber_AfterUpdate()

Me.LotText = Me.Lot

End Sub

Might as well start with something as simple as possible and build from there. Unfortunately, I can't even get this to work! I added the folder my DB file is stored in as a "trusted source" in Access Options, but still no luck.

Any ideas?

Edit: Ugh, there was a security warning at the top, too. Is that going to happen everytime I run a VB script, or just the first time after opening the database everyday? At least I finally got a script to run, right?

So is the code running, or not? From your description, I'm envisioning a form with 2 textboxes, but your code appears to have 3.

It's starting to come together, and is running. I've now integrated it into one of my other forms. (A simpler form.) It's used to enter product data when we receive it.

Essentially, I have a bunch of text boxes and a combo box where data is entered. I've added a new text box bound to the new LotNumberText field. After entering a LotNumber in the LotNumber text box, this code copies it directly into LotNumberText field.

Private Sub LotNumber_AfterUpdate()
Me.LotNumberText = Me.LotNumber
End Sub

A perfect first step. I also added a column to the "Product" ComboBox so that I could pull a value from tblManufacturers (the LotNumberDigits, linked through tblProducts via a PK called ManufacturerID). Adding the column (though hidden) to the combobox was the only way I could find to bring this value to my form.

This code enters it into a text box I've added (just so that I can ensure the number is coming over properly.) When I'm finished, I want to use this number to "pad" the LotNumberText up to the correct number of digits, I assume using a WHILE loop. (ie: While the number of digits of LotNumberText < the LotNumberDigits text box)

Private Sub FormulaID_AfterUpdate()
Me.Digits = Me.FormulaID.Column(2)
End Sub

It's baby steps, but I'm making headway. Thanks so much for all the help! :)

Now I'm reading through tutorials looking for the function I'd call to count the number of digits in the LotNumberText string.

Look at the Len() function.

Look at the Len() function.

Ah hah! Perfect! Here is what I've tried for my While loop, but it just keeps putting 0's in the field until it surpasses the length of the text box size and then gives me the option to End.

Private Sub LotNumber_AfterUpdate()
Me.LotNumberText = Me.LotNumber
Me.CountedDigits = Len(Me.LotNumberText)

Dim LotNumberLength
LotNumberLength = Len(Me.LotNumberText)
While LotNumberLength < Me.FormulaID.Column(2)
Me.LotNumberText = "0" + Me.LotNumberText
LotNumberLength = Len(Me.LotNumberText)
Wend

End Sub

The "dummy" text boxes that correctly report the length the lot number should be (based on the column from the combo box) and the counted Len(). It seemed like an easy way to ensure that it was working. Once it's all running I can remove those of course.

Edit: That should read "I created 'dummy' text boxes..."

I don't see the need for the loop, but the problem might be the use of "+" instead of "&". Why not use the Format() function with 5 or 6 zeros as appropriate?

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."

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!

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

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

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