jackedupshelby
asked on
Left and right zero fill...
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
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
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,fo rmat(text1 ,"00000000 0")
End Sub
Using the format function in the afterupdate function will actually insert the zeros into the field.
Private Sub text1_afterupdate()
text1 = iif(isnull(text1),text1,fo
End Sub
Using the format function in the afterupdate function will actually insert the zeros into the field.
jackedupshelby,
Try something like this:
select right('000000000'+trim(You rColumn),9 )
from YourTable;
Try something like this:
select right('000000000'+trim(You
from YourTable;
mphel changed the proposed answer to a comment
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.
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.
(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?)
BTW: I called the field "strZeroFill" *not* "strZeroFilled" (obviously, eh?)
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,ri ght("00000 0000" & text1,9))
Richard
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,ri
Richard
ASKER
Adjusted points from 100 to 150
ASKER
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
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you everyone... Good work, I really appreciate this help.
ASKER
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.