0 in front of numbers in Access

[Sonya311]

Hi. I need to figure out how to get a 0 in front of my indexed primary
numbers in Access, more specifically in tables. For example, the primary key
number is 02200, however when I enter that it shows up as 2200, which is a
completely different number in our system. I have tried reformatting the
number of decimal places and everything, and I cannot figure out how to get
the 0 to stay in front of the number. Please help!!!!!!!

 

[Jackie L]

Sonya,
Your field needs to be text in your table design instead of number. Be
careful of any other links to that field you may have before changing the
data type.

Hope this helps.

 

[Douglas J. Steele]

Sounds as though you're using a numeric field as your primary key. Leading
zeroes do not matter with numeric fields: 2200 is exactly the same as 02200
with a numeric field.

Try changing your field to text. If you need to change your 3 and 4 digit
numbers to 5 character text fields with preceding zeroes, try using the
following in an update query: Right("00000" & [OldNumber], 5)

 

[SacCourt]

Use a text field instead of a number. Consider using

Me.YourFiled = Format(Me.YourField("00000000"))

When you convert to text you will neet to put back your leading zeors.


Format Function


Returns a Variant (String) containing an expression formatted according to
instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. A constant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the
year.



Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday



The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in
the year.
vbFirstFullWeek 3 Start with the first full week of the year.



Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric
formats.
Dates and times Use predefined named date/time formats or create
user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.



If you try to format a number without specifying format, Format provides
functionality similar to the Str function, although it is internationally
aware. However, positive numbers formatted as strings using Format don’t
include a leading space reserved for the sign of the value; those converted
using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a
user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date
formatting, the supplied expression must be Gregorian. If the Visual Basic
Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is
unchanged. If the calendar is Hijri, all date format symbols (for example,
dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar.
Format symbols remain in English; symbols that result in text display (for
example, AM and PM) display the string (English or Arabic) associated with
that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666

 

[Sonya311]

Is there anyway to avoid having to reenter all of the leading zeros? Is
there anything I can do to do the all at once or do I have to key it in
individually?

Use a text field instead of a number. Consider using

Me.YourFiled = Format(Me.YourField("00000000"))

When you convert to text you will neet to put back your leading zeors.


Format Function


Returns a Variant (String) containing an expression formatted according to
instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. A constant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the
year.



Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday



The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in
the year.
vbFirstFullWeek 3 Start with the first full week of the year.



Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric
formats.
Dates and times Use predefined named date/time formats or create
user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.



If you try to format a number without specifying format, Format provides
functionality similar to the Str function, although it is internationally
aware. However, positive numbers formatted as strings using Format don’t
include a leading space reserved for the sign of the value; those converted
using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a
user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date
formatting, the supplied expression must be Gregorian. If the Visual Basic
Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is
unchanged. If the calendar is Hijri, all date format symbols (for example,
dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar.
Format symbols remain in English; symbols that result in text display (for
example, AM and PM) display the string (English or Arabic) associated with
that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666




--

Self taught user of Access 97 – 2003 with 7 years of experience with is part
of 20 years of overall database experience. I'm still learning.

Hi. I need to figure out how to get a 0 in front of my indexed primary
numbers in Access, more specifically in tables. For example, the primary key
number is 02200, however when I enter that it shows up as 2200, which is a
completely different number in our system. I have tried reformatting the
number of decimal places and everything, and I cannot figure out how to get
the 0 to stay in front of the number. Please help!!!!!!!

 

[Douglas J Steele]

A couple of us have given you methods to avoid any rekeying.

In essence, you need to add a new field to your table, making it text
instead of numeric. Then, run an update query to take the "modified" numeric
value and put it in the text field. The "modification" I'm referring to can
be using the Format statement (as SacCourt suggests) or something like
Right("00000" & [OldNumber], 5) (as I suggested). Once you know that the new
text field is properly populated, you can delete the old numeric field and,
if desired, rename the new text field you added.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Is there anyway to avoid having to reenter all of the leading zeros? Is
there anything I can do to do the all at once or do I have to key it in
individually?

Use a text field instead of a number. Consider using

Me.YourFiled = Format(Me.YourField("00000000"))

When you convert to text you will neet to put back your leading zeors.


Format Function


Returns a Variant (String) containing an expression formatted according to
instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. A constant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the
year.



Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday



The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in
the year.
vbFirstFullWeek 3 Start with the first full week of the year.



Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric
formats.
Dates and times Use predefined named date/time formats or create
user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.



If you try to format a number without specifying format, Format provides
functionality similar to the Str function, although it is internationally
aware. However, positive numbers formatted as strings using Format don't
include a leading space reserved for the sign of the value; those converted
using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a
user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date
formatting, the supplied expression must be Gregorian. If the Visual Basic
Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is
unchanged. If the calendar is Hijri, all date format symbols (for example,
dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar.
Format symbols remain in English; symbols that result in text display (for
example, AM and PM) display the string (English or Arabic) associated with
that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666




--

Self taught user of Access 97 - 2003 with 7 years of experience with is part
of 20 years of overall database experience. I'm still learning.

Hi. I need to figure out how to get a 0 in front of my indexed primary
numbers in Access, more specifically in tables. For example, the primary key
number is 02200, however when I enter that it shows up as 2200, which is a
completely different number in our system. I have tried reformatting the
number of decimal places and everything, and I cannot figure out how to get
the 0 to stay in front of the number. Please help!!!!!!!