iff 102 then change it to 0102

[TJVFree]

What would be the easyest why for this statment to be layed out

=IIf(([org_code]) = 102,"0102" ([org_code]), 0)

Thank you for your time and help

 

[ICCIIT]

=IIf ([org_code]=102,"0102",0)

is [org_code] numeric or string?

 

[TJVFree]

text

 

[TJVFree]

This is the message I get:

The expression you entered has an invalid. (dot) or ! operator or invalid parentheses

You may have entered an invalid identifier or typed parentheses following the null constant

I'm not sure what that means

 

[ICCIIT]

In that case try

IIf ([org_code]="102","0102","somthing else")

 

[ICCIIT]

or to keep the original value as is...

IIf ([org_code]="102","0102",[org_code])

 

[Golom]

Your original IIF is invalid because

=IIf(([org_code]) = 102,[red]"0102"[COLOR=black yellow] [/color]([org_code])[/red], 0)

There is no operator between "0102" and ([org_code])

Since [org_code] is text, you may need

=IIf([org_code] = "102", "0102", [org_code])

 

[TJVFree]

for some reson, this still isnt working. it still shows 102, and Ideas?

 

[Andrzejek]

Just a guess here:

=IIf([blue]Val([org_code]) > 0[/blue], [red]Format([org_code], "000")[/red], [green]"0"[/green])

You may test separately [blue]BLUE[/blue] and [red]RED[/red] parts of this code to make sure it works the way you want.


Have fun.

---- Andy

 

[randy700]

CoreyVI,

Where are you trying to enter this statement?
Please provide the entire code.


Randy

 

[SkipVought]

Why not simply...

Format(CInt([org_code]), "0000")

assuming that ALL the ID strings need to be 4 digits long.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TJVFree]

some are five and six. but thank you for this information

Coery

 

[Andrzejek]

You still did not answer Randy's request:

Where are you trying to enter this statement?
Please provide the entire code.

Have fun.

---- Andy

 

[GunVi]

This might work:

IIf(Val([Org_Code])>0,'0' & [Org_Code],[Org_Code])

 

[dhookom]

Lots of assumptions and guesses in this thread.

CoreyVl,
Can you provide some information regarding the scope of this question? Is this only for Org_Codes with a value of "102" or do you wish to transform many different values?

If you want to transform many different values, please provide a "rule" and a variety of values with the expected results.

Also, is this in a code module, control source, query, ...?

Do you want to update a value or simply display the transformed format?


Duane
Hook'D on Access
MS Access MVP