How do I get my form to display a leading zero? 3

[kat25]

Whenever I type in a number with a leading zero on a field in my form, the number is saved without the zero. Example:
I key in 0102. The field on the form displays: 102.
I need for the leading zero to be apart of the number the value for that field. The data type for the field is "number".

Any help would be appreciated.

Thanks.

 

[scriverb]

Numeric fields do not save the leading zeroes. Text fields actually store leading zeroes to Numeric looking text strings. But, if all you need is to have a numeric number display a leading zero then we can use the FORMAT function to do this.

Format(120, "0000&quot

This will display 0120.

This format "0000" can be used in the Format property of the numeric fields control on a form or a report.

I hope this helps with your project.

Bob Scriver

 

[kat25]

Thanks!
That fixed my problem.

kat25

 

[BLBurden]

hey Bob, I sent you a star on this post, because it help me also. Keep up the good work.

BLB - Always Grateful
A good friend will bail you out of jail. A true friend will be sitting at your side saying, "Boy - didn't we fun?"

 

[Captbob007]

Kind of the same problem - little different scenario.

I have a form that uses an unbound field to input a numeric value into a table (via SQL). In the table, I have it set to be a text field. I have a input mask on the table (00\-000), and the same one on the field on the form, so it looks pretty. However when I enter a number on the form with a leading zero, it appears in the table as only the last four digits. I've tried it with non-leading zeros and it works just fine, so it must be dropping the zero somewhere in there.

Ive double checked and triple checked - it's a text field on the table. Somehow its getting lost between the form and the table. Any ideas would be appreciated!

~Bob

 

[scriverb]

Bob, please give me the exact numbers you are trying to type in with this InputMask. I don't seem to be having a problem with it. Tell me what is entered and what is actually being saved.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Captbob007]

I'm entering numbers like 03-111 or 03-589 and its saving "31-11" and "35-89" in the table...

 

[scriverb]

I suspect that you text field in your table is set to a field size of 5. Change it to 6 as the hyphen(-) is being saved as part of the string of characters.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Captbob007]

Nope that's not changing anything either... I even tried taking it up to 7 (like if for some unknown reason the "\" in the input mask were having some effect, even though it shouldn't) It just cant stand that leading zero!

 

[scriverb]

I will keep playing with it but the only way that I could get the same results given the Input Mask, data entered, and data stored was if the field size was 5. Because, the hyphe(-) is being saved in the field along with the other entered characters the zero was being truncated off the left side.

I will post back with a solution if there is one.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Okay, here is what I have. Table with two text fields. One is set to 5 characters and one to 6. I have a form with a single text control with an input mask of 00\-000. After entering 03111 the display on the form looks like 03-111. I run a query that updates my table. This is the SQL for that query:

UPDATE tblTextFields as A SET A.Text5 = [Forms]![frmTextFields]![TextEntry], A.Text6 = Format([Forms]![frmTextFields]![TextEntry],"00-000&quot
WHERE (((A.RecCounter)=[Forms]![frmTextFields]![RecCounter]));

The results in my table are A.Text5 = 03111, A.Text6 = 03-111. Now there are no input or format entries on either field in the table.

Review your situation and see if there is anything different in all of your table fields, form fields, and query.

Post back with what you find.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Captbob007]

Thanks so much! That works fine - I think removing the input mask from the table did the trick, but I changed the SQL as well. It now does exactly what I need!