Using IIF statements

[Pete Provencher]

Access 2000

I'm attempting to create a new field with a number that can have from 1 to 3
digits. If it has one digit I want to add two zeros to the beginning of the
number. If it has 2 digits I want to add 1 zero to the beginning of the
number and if it has 3 digits I want to leave it alone.

Here is the formula that I have tried with no success,:

IIf(Len([parcelnumber])>2,[parcelnumber]),(IIf(Len([parcelnumber])>1),"0" &
[parcelnumber]), IIf(Len([parcelnumber])>0,"00" & [parcelnumber]))

Hope someone can show me the errors of my way.

Pete Provencher

 

[Cheryl Fischer]

Hello Pete,

Give the following a try:

Format([MyNumberField], "000")

hth,

 

[Pete Provencher]

Worked great. You sure made life easier. Wish I hadn't spent so much time
trying to get my formula to work.. Thanks again.

Pete Provencher

Hello Pete,

Give the following a try:

Format([MyNumberField], "000")

hth,

--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Access 2000

I'm attempting to create a new field with a number that can have from 1

to

3
digits. If it has one digit I want to add two zeros to the beginning of the
number. If it has 2 digits I want to add 1 zero to the beginning of the
number and if it has 3 digits I want to leave it alone.

Here is the formula that I have tried with no success,:

IIf(Len([parcelnumber])>2,[parcelnumber]),(IIf(Len([parcelnumber])>1),"0"

&
[parcelnumber]), IIf(Len([parcelnumber])>0,"00" & [parcelnumber]))

Hope someone can show me the errors of my way.

Pete Provencher

 

[finster26]

Try this:

IIf(Len([parcelnumber])=1,"00" & [parcelnumber],IIf(Len
([parcelnumber])=2,"0" & [parcelnumber],[parcelnumber]))