Muliple conditional statements in a query?

[NJudson]

I'm having a brain fart now and I'm unable to find anything that is helping me. I'm using Access 2k and I'm trying to design a create table query where I'm thinking I need multiple or nested if statments but don't know how to do them. Below is a example of the Field in the table that I'm working with:

Field1
0X
11Y
13
22Z

I'm looking for a way to replace where if Field1 contains an X as rightmost character or if Field1 is completely numeric then
Field1 Field1(new)
0X 0.1
13 13.1

If rightmost character is a "Y" then
Field1 Field1(new)
11Y 11.2

And if rightmost character is a "Z" then
Field1 Field1(new)
22Z 22.3

I'm thinking of something like an IIF statement but I have 4 conditions rather than 2. Can you nest IIF statements? I've very little experience using IIF statments but I think I understand them ok to use them. Thank you for any help.

 

[namliam]

Yes you can nest Iif's

iif(condition1;<true1>iif(condition2;<True2>;<False2>);<False2>))

You can nest all you want.....

Be carefull tho, it is precise work...

Regards

 

[Pat Hartman]

I would use a Case statement.

Select Case Right(YourField,1)
     Case "Y"
        NewField = Val(YourField) + .2
     Case "Z"
        NewField = Val(YourField) + .3
    Case else
        NewField = Val(YourField) + .1
End Select

I do not recommend (nor does Microsoft) using the IIf() in VBA. The reason has to do with how it is evaluated and in certain cases can cause errors especially if you are using it to avoid dividing by zero. The statement is evaluated differently in a query so the problem does not exist there. In VBA, the recommend solution is If Then Else or Select Case.

 

[namliam]

Pat,

To qoute the title of the post: "in a query?"

BR

 

[Pat Hartman]

Picky, picky, picky If I read all the words in a question, I would never get through as many as I do.

 

[NJudson]

Thank you both for you time and suggestions. I'll post back if I have any troubles.

 

[namliam]

Quantity vs Qaulity the everlasting question ... *tsk tsk tsk*

 

[Pat Hartman]

Don't pick on me, I'm in a class and trying to type quietly so no one hears me.

 

[namliam]

LOL i am at work... trying to make ppl notice me.....