Leading zeros in MS Access form needed 2

[Kativs]

I have MS Access 2000 and in my form I want to show the leading zeros in my text box. I've read this old thread:
thread702-1441155 which is similar but I only have 4 digits to worry about...such as 0123 0875 0046 etc etc
How do I get these zeros back in? Do I need to build an event such as this other thread? Do I just go to the Properties Format? Right now I have the format set as General Number.
I tried building an event like this:
format(MyField), "0000")
I'm sure this is incorrect.
Any help would be appreciated.
But while we're on the subject of numbers....
I posted a thread awhile back with no answers. I was able to create a hyperlink to websites in this same form and the hyperlinks work however they display such as 00000/00000.0000 etc etc Lots of zeros. Can't figure out why. Where it says how to display is correct such as

http://www.mywebsite.com

but viewing them in the form is weird. I wasn't too concerned since the links work but it's distracting.
Thank you again for any help.

 

[lameid]

You had an extra closing parenthesis

format(MyField, "0000")

 

[Kativs]

Wow thanks but it still isn't working. Not sure if I'm putting it all in the correct place.
Is it in the event timer? Format now says general number.
Getting confused as the day goes by.
I appreciate your help. I'm sure it's easier than it appears right now.

 

[lameid]

To show it you would put an equal sign (=) in front of it and use it as the control source. This will prevent you from entering data into the field.

 

[Kativs]

Thanks I tried an = sign but maybe I had things in the wrong order. I will try again tomorrow morning.
Thank you again.

 

[PHV]

Have you tried to set a display format of "0000" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Kativs]

Where am I putting this? Format, Data, Event??
Sorry I'm just lost now.

 

[lameid]

He means the format property of the control. I should have thought of that too.

 

[Kativs]

Okay..this is what I've tried and where I've gone.
In my form, design view, right clicked on field and selected build event. In the build event I typed in
format(Tray#, "0000")
I also tried format = (Tray#, "0000")
Nothing seems to work. Obviously I'm doing something wrong or in the wrong place for the code.
Note: I have "general number" in my format in the properties.

 

[lameid]

Recommended:

Make your control source: [Tray#]
Make your format Property: "0000"


Alterantely Make your control source = Format([Tray#],"0000")

Anytime Access does not understand a table or field name you have to surround it with square brackets []. Typically this means you have a space in the name. In your case you have a character that is used for a date delimeter (#) in your field name.

Other characters off the top of my head that would or I expect would cause problems in a field names:

;
"
'
*
?
@
.
All Numeric like 9999 or 1583

As a rule of thumb table and field names should not have spaces or punctuation in them. The underscore is commonly used instead of a space. TRAY_NUM for example would arguably be the best name for your field.

SQL Server best practice says to Capitalize field and table names and separate words using underscores.

Personally I used to capitalize the first letter of each word or TrayNum. But now I use the underscore and mix case Tray_Num (I have a harder time reading SQL that screams at me I guess).

I mention SQL Server because if a database becomes big enough or has too many users for Access to support, it will end up there. The good news is that the latest versions of SQL server are almost as forgiving as Access in field names. But consider this, you have to type more characters when you use the special characters. And if you migrate to something other than SQL Server, you probably will have a real problem on your hands.

 

[Kativs]

Thanks...I took your advice first and changed to say Tray_Num. The control now works however all I get in all my Tray numbers is 0000 but not the numbers entered such as 1827 or 0563. I tried to re-enter a number 0327 for my first tray number, saved it and it keeps changing it to 0000.

 

[Kativs]

also here's a stupid question. I probably can't do this but if the control is set at 4 zeros and I only want it to read 327 will it still read 0327 ? I would like only the numbers needed such as tray 063 or 327 or 1420.
Does this make sense? Most of our tray numbers are 3 digits but we have a few that are 4 digits. I really don't want the 3 digit trays to read 0063 ... I'd like 063. Impossible with needing 4 digits as well?

 

[PHV]

So, set the format property to "000"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

You would have to change the field in the base table. If you have Autocorrect turned on (Tools, Options, General Tab, Perform name Autocorrect checked) and do not have any code that uses the field name, then the changes should propogate through when you change the table. It is ok to leave it too, you just have to use the square brackets...[tray#]. As a rule of thumb once you do any significant development against a database, it is too late to fix names. You may or may not be too far along to fix your names.

 

[Kativs]

Wow...too smart for me..thanks that worked.
Now,,,it's really weird. If I am in that field the number that was originally entered such as 327 shows but once I click out of that field all I read there is 000. How can I get it to stay with the correct number.

 

[lameid]

I missed the nuance myself until I tested it... don't use the double quotes for the format property.

Set the format property to just 000

 

[Kativs]

ahahhaha cool..now it shows 327.000

 

[lameid]

Change the Decimal places property to 0 (I expect it is 3 or auto now).

 

[Kativs]

EXCELLENT !! THANK YOU SO MUCH. AND THANKS FOR BEING THERE TO RESPOND SO QUICKLY.
20 STARS FOR YOU !
Now, can I sort of apply this to my other problem with my hyperlinks for websites? See my original question.
Thanks again !

 

[lameid]

My first inclination would be to look at the properties and see if there is anything wierd there like a format that says something.

Next I would hit Ctrl + G to bring up the immediate window...

Then I'd type...

? Forms![Form Name]![Control Name]

and then push enter.

Naturally substitute your form and conrol name for your hyperlink. This should return the text value of the hyperlink. It will be formatted...

Address#Sub-Address#Display-Text

That should tell you if there is something wierd with the hyperlink itself or not.