Show leading zeros in form field

[Elfman]

Hi All,

Here’s my problem:

User wants to see leading zeros in firm field when adding a new record but access keeps deleting them. I know it makes no difference if there recorded just wondering for the user’s happiness (and mine) can it be displayed on form??

Thanks in advance

-Elfman

 

[RuralGuy]

Format([YourField],"000000") which is a string.

 

[Elfman]

Tahbks RG,

not sure where to put that though tried several option under proerties but to no avail ??

 

[Dennisk]

either format at the table level, or the format property for the control on the form.

 

[RuralGuy]

I assume it is a field that is locked so the user can not change it so put it in the ControlSource of the control instead of the FieldName.
=Format([FieldName],"000000")

 

[Elfman]

Thanks for the help but..
Any other way to do it just on the form ??

 

[RuralGuy]

Did you try my suggestion?

 

[royschmoll]

I've been looking at this and I am not sure where the control source of the control is. Could you explain it like I was a 40 year old - Nice and slow?

 

[spikepl]

OK, here is a special guide for a 40-year old:

1. Click on the link provided in the next line
http://lmgtfy.com/?q=control+source+control+access
2. Click on the first link that comes up
3. Read text (remember the reading glasses)
4. Come back if incomprehensible

 

[missinglinq]

In Form Design View

• Right click on the control
• Click on Properties
• Click on Data
• In the Control Source Property enter =Format([FieldName],"000000")

 

[royschmoll]

I think my real problem is the database is web based. I'm not sure this is what the problem is since I have yet to have it work.

but I do get the message "the expression you entered is not valid for web-compatible forms" or something like that.

how do I change a form to a non web based form?

 

[Catalina]

What is that nonsense about a 40 year old...

I'm 68 and still sharp as a razor!

 

[royschmoll]

I noticed how most of the answers on here are designed for people that have been into this for some time. I want them to explain to me they would a 5 year old. I thought it would be funnier to say 40 year old since most 5 year olds can program a VCR and most 40 year olds cannot.

 

[missinglinq]

User wants to see leading zeros in firm field when adding a new record but Access keeps deleting them.

Sorry, should have noticed this earlier! I'm guessing your field is defined as having a Datatype of Number! Go into Table Design and change this to Text and the leading Zeros should stay in place.

Linq

 

[Galaxiom]

If textbox is bound to a number field then leading zeros are removed. As Linq suggested you can change the field to text and it is then treated as a text string.

However it is possible to show leading zeros in a number field or control using the Format property of the control on the form. Enter a string of zeros to match the total number of digits you would ike displayed.

eg 000000 will display all numbers with six digits, padding the leading end with zeros as required.

Unlike changing the control source to an expression containing the Format function as described above, this technique still allows data to be entered in the control. Note however that the leading zeros disappear when the control is in focus.

 

[Galaxiom]

I thought it would be funnier to say 40 year old since most 5 year olds can program a VCR and most 40 year olds cannot.

Most five year olds would not even know what you were talking about when referring to a VCR. Thye could probably program the digital recorder though.

 

[royschmoll]

The problem stems from the fact that I am trying to do UPC codes. Some of them have leading zeros and the type is a numeric. I am bringing one table from an external source and it will not allow me to change the type. I have tried the STR command and I tried to change the format within the command they both give me a type error.

I tried a separate table that has the list of UPCs as a string then I tried to tie the two tables together. When I did this in the form it asked me for a relationship between the two tables. I was using the product identification number that is the same in both tables but the modal dialog box that was used in the combining never did go away (didnt accept it.)

So I am in the same place as I was two days ago but now I know how to program my Tivo .

 

[royschmoll]

I think I am fine now - I needed a relationship between tables. Once I did that I could use the second table as text and I added the leading zeros. Thanks for all of the help. Seems to be working great now.

 

[ChocQu]

Format([YourField],"000000") which is a string.

It doesnt work.. I'll already tried

 

[Galaxiom]

It doesnt work.. I'll already tried

The original field would need to be numeric. It won't work on a string.

If it is a string then try this.

Format(CLng([field]), "000000")