Excel statement that I want to replicate in MS Access as a Query

[Gary F Shelton]

Here is the excel statement that works just fine. Now I want to do this
statement in Access as a query. Any ideas?

=IF(G2="A",O2/30.3*21,IF(G2="A-CH",O2/30.3*21,IF(G2="B",O2/30.3*28,IF(G2="B-CH",O2/30.3*28,IF(G2="C",O2/30.3*35,IF(G2="C-CH",O2/30.3*35,IF(G2="D",O2/30.3*42,IF(G2="D-CH",O2/30.3*42))))))))

 

[Lotto]

Here is the excel statement that works just fine. Now I want to do this
statement in Access as a query. Any ideas?

=IF(G2="A",O2/30.3*21,IF(G2="A-CH",O2/30.3*21,IF(G2="B",O2/30.3*28,IF(G2="B­-CH",O2/30.3*28,IF(G2="C",O2/30.3*35,IF(G2="C-CH",O2/30.3*35,IF(G2="D",O2/3­0.3*42,IF(G2="D-CH",O2/30.3*42))))))))

I think a switch function would work, but I'm far from the level of
guru's here.

 

[Roger Carlson]

I'm not sure what you mean that you want to do this "as a query". However,
this can be used in an Access query by converting it to an Immediate IF
statement. Do this by changing IF to IIF and replacing G2 with a field
name. It will, however, be remarkably inefficient.

There are better ways to do this, but without additional information, I
can't begin to explain.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

You can use the Switch function. Change G2 to the field in your table you
want to compare on.

= 02/30.3 * Switch(G2="A",21,G2=
"A-CH",21,G2="B",28,G2="B-CH",28,G2="C",35,G2="C-CH",35,G2="D",42,"G2="D-CH",42)

 

[Gary Shelton]

It worked... Here is the statement I wrote:

Deployment: [On
Hand]/30.3*Switch([Tier]="A",21,[Tier]="A-CH",21,[Tier]="B",28,[Tier]="B-CH",28,[Tier]="C",35,[Tier]="C-CH",35,[Tier]="D",42,[Tier]="D-CH",42)

 

[David W. Fenton]

=?Utf-8?B?R2FyeSBGIFNoZWx0b24=?=

Here is the excel statement that works just fine. Now I want to do
this statement in Access as a query. Any ideas?

=IF(G2="A",O2/30.3*21,IF(G2="A-CH",O2/30.3*21,IF(G2="B",O2/30.3*28,I
F(G2="B-CH",O2/30.3*28,IF(G2="C",O2/30.3*35,IF(G2="C-CH",O2/30.3*35,
IF(G2="D",O2/30.3*42,IF(G2="D-CH",O2/30.3*42))))))))

If you are storing your data in a database, you should eliminate
this entirely and replace it with a lookup table. That will perform
much more quickly.