Using IIF statements

  • Thread starter Thread starter Pete Provencher
  • Start date Start date
P

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
 
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
Cheryl Fischer said:
Hello Pete,

Give the following a try:

Format([MyNumberField], "000")

hth,

--

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


Pete Provencher said:
Access 2000

I'm attempting to create a new field with a number that can have from 1
Click to expand...
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,:
Click to expand...
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
Click to expand...
Click to expand...
 
Try this:

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