Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Left and right zero fill...

Posted on 2000-03-22
13
Medium Priority
?
652 Views
Last Modified: 2008-03-10
How do you zero fill in Access other than the format property?

I have a field in a table that needs to be 9 digits. Since the input may differ from 1-9 I need to left zero fill. When using the format property, the fill isnt saved from the form to the table. The field is a text field so as to not drop the zeros.

This is an easy question for an expert but an answer is needed badly so I will give extra points for this one..

Thank you
0
Comment
Question by:jackedupshelby
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 1

Expert Comment

by:Deverill
ID: 2647349
Create an update query.  Add the fieldname in question.  
Criteria is   Len([fieldname])<9
Update To is  "0"+[fieldname]

Execute this and repeat until you get the message saying "You are about to update 0 records" then you are done.


Make sure you have a backup... one typo could be ugly to your data.
0
 

Expert Comment

by:mphel
ID: 2647362
I have this problem sometimes with capital letter fields - I use the ">" symbol in the format field but if the user enters a lowercase letter, the lowercase is saved while the uppercase is displayed.  The only way I work around it is to put code into the field's afterupdate event i.e.

Private Sub text1_afterupdate()
    text1 = iif(isnull(text1),text1,format(text1,"000000000")
End Sub


Using the format function in the afterupdate function will actually insert the zeros into the field.
0
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2647378
jackedupshelby,

Try something like this:

select right('000000000'+trim(YourColumn),9)
  from YourTable;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Expert Comment

by:mphel
ID: 2647400
mphel changed the proposed answer to a comment
0
 
LVL 7

Expert Comment

by:Believer
ID: 2647404
The easiest option is to use the input mask.  Unfortunately, it would force your users to enter all 9 digits.  Therefore I think the only way you'll be able to do this is through code.
In the After_Update event for this field on your form, enter the following code (I'm calling the field/control "strZeroFilled"):
  strZeroFill = String$(9 - Len(strZeroFill), "0") & strZeroFill

The String$(x,"y") function will return the character in "y" x number of times.  String$(3,"0") would return 3 zeroes "000".  The number of zeroes I told it to use is 9 - Len(strZeroFill).  After that, I added the original value to the end.
0
 
LVL 7

Expert Comment

by:Believer
ID: 2647411
(Wow, looks like we were all typing frantically at the same time!  I thot I was the only one here!  LOL)
BTW: I called the field "strZeroFill" *not* "strZeroFilled" (obviously, eh?)
0
 
LVL 2

Expert Comment

by:repstein
ID: 2647859
This will also work:

Private Sub Text1_AfterUpdate()
  Text1.Value = Right("000000000" & Text1.Value, 9)
 
End Sub

You can replace text1.value iwth text1 if you want, since .value is the default property of a text box

When  you say the field "needs" to be 9 digits, I assumed that it was required, and that "000000000" should be entered if the user skips it. If you want to keep the field blank, then, similar to mphel's suggestion

text1 = iif(isnull(text1),text1,right("000000000" & text1,9))

Richard
0
 

Author Comment

by:jackedupshelby
ID: 2649413
Adjusted points from 100 to 150
0
 

Author Comment

by:jackedupshelby
ID: 2649414
Hey guys,Im sorry BUT these solutions are not working for me.. Maybe I should have told you that I am not a programmer..and I may need more details. The field name (column name) is Total_Bill. Does this matter...

Thank you
0
 
LVL 7

Accepted Solution

by:
Dedushka earned 600 total points
ID: 2649601
Hi, jackedupshelby.
You should use
Me.Total_Bill = Right("000000000" & Me.Total_Bill, 9)
in BeforeUpdate event for the form, not for the textbox, so:
1. Open your form in design mode, open "Properties" window.
2. Click on "Event" tab and select [Event procedure] for BeforeUpdate event and click (...) button on the right side.
2. In VBA editor you should have this procedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Total_Bill = Right("000000000" & Me.Total_Bill, 9)
End Sub

4. Compile, Save and  Close VBE. Save and Close your form. Now you are ready to run the form.

Good luck,
Dedushka

0
 
LVL 7

Expert Comment

by:Believer
ID: 2649676
Field name doesn't matter too much... that you aren't a programmer is significant info, though! :-)
Let's see if we can get some clarifications from you...
This data is being edited on a form, correct?
And the data source for the form is a table/query?
The field Total_Bill is on the form?

If all that's true, do this:
1. In form design view, double-click Total_Bill.  That should cause the properties window to appear.  Or single-click Total_Bill and click (on the menu) View/Properties.

2. In the properties window, go to either the tab labelled "Event" or "All".

3. Click in the property/event "After Update"

4. Drop the combo box down and select [Event Procedure]

5. Click the button to the right with three dots on it (a.k.a. elipsis)

6. You should now be in a code window.  You should also be in the code that will run after the user types a value in the field: Total_Bill_AfterUpdate()

7. Copy and paste the following into the procedure (Your cursor should be sitting in the procedure):
  Total_Bill = String$(9 - Len(Total_Bill), "0") & Total_Bill

The procedure should look like this when you are done:
   Private Sub Total_Bill_AfterUpdate()
      Total_Bill = String$(9 - Len(Total_Bill), "0") & Total_Bill
   End Sub

If you need to update information that already exists in the table, I can tell you how to do that, too, just let me know.

0
 

Author Comment

by:jackedupshelby
ID: 2649794
Thank you everyone... Good work, I really appreciate this help.
0
 

Author Comment

by:jackedupshelby
ID: 2649807
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question