Writing an IF statement in Microsoft Query

JTurner

unread,

Mar 29, 2010, 5:58:01 PM3/29/10

to

I'm querying our Oracle data to Excel and can't seem to get a formula return
as a new field.

I need the proper way to write this sequence:

IF(RM.RM_STD= "LOCKER",50,RM.RM_AREA*RMSTD.COST_OF_SPACE)

Which would basically say that if the room standard is a locker, we charge
$50 - otherwise, take the room area and multiply it by the cost per square
foot. But it doesn't matter how I write it...it's not working! HELP??!!!

FSt1

unread,

Mar 29, 2010, 6:14:01 PM3/29/10

to

hi
MS Query uses SQL and in SQL, you have to use the SQL IIf instead of the vb
IF.
and the IIf statement goes in the MSQ SQL statement ie
SELECT name, Address, ect,
IIF(RM.RM_STD= "LOCKER",50,RM.RM_AREA*RMSTD.COST_OF_SPACE) from somedatabase.

i cannot test but your IIf statement "looks" ok so good luck.

regards
FSt1

FSt1

unread,

Mar 29, 2010, 7:22:01 PM3/29/10

to

hi
i am suddenly having major problem with MSQ. trying to edit a query locks up
excel. just started so i've go some major troubleshooting of my own now.
i can't peek at my SQL stuff so i'm going on memory...
i said you IIf statement looks ok but i may have crossed some wires in my
brain.
i think table names should be in brackets ie...
SELECT name, Address, ect,
IIF([RM.RM_STD]= "LOCKER",50,[RM.RM_AREA]*[RMSTD.COST_OF_SPACE]) from
somedatabase.

can any SQL experts confirm this?

Regards
FSt1

"JTurner" wrote:

> I've tried the IIF as well. It keeps returning the error "ORA-00907: missing
> right parthensis". Does that help troubleshoot?

Rik_UK

unread,

Mar 29, 2010, 8:28:01 PM3/29/10

to

Hi

just to check, have you declared a field for the data in the select
statement of the SQL syntax? Might be worth posting the whole SQL statement
so it can be checked, as the iif looked OK.

JTurner

unread,

Mar 29, 2010, 9:50:01 PM3/29/10

to

If I have this query, I get the "missing right parenthesis" error:

SELECT RM.BL_ID, RM.FL_ID, RM.RM_ID, RM.AREA, RM.COUNT_EM, DP.NAME,
RM.DP_ID, DV.NAME, RM.DV_ID, RM.RM_CAT, RM.RM_STD, RM.RM_TYPE, RM.RM_USE,
RM.OPTION1, RMSTD.COST_OF_SPACE,
IIF(RM.RM_STD="LOCKER",RMSTD.COST_OF_SPACE,RM.AREA*RMSTD.COST_OF_SPACE)
FROM AFM.DP DP, AFM.DV DV, AFM.RM RM, AFM.RMSTD RMSTD
WHERE RM.DV_ID = DV.DV_ID AND RM.DP_ID = DP.DP_ID AND DP.DV_ID = DV.DV_ID
AND RM.RM_STD = RMSTD.RM_STD

If I write it this way, I get "Incorrect Column Expression":

SELECT RM.BL_ID, RM.FL_ID, RM.RM_ID, RM.AREA, RM.COUNT_EM, DP.NAME,
RM.DP_ID, DV.NAME, RM.DV_ID, RM.RM_CAT, RM.RM_STD, RM.RM_TYPE, RM.RM_USE,
RM.OPTION1, RMSTD.COST_OF_SPACE,
IIF([RM].[RM_STD]="LOCKER",[RMSTD].[COST_OF_SPACE],[RM].[AREA]*[RMSTD].[COST_OF_SPACE])
FROM AFM.DP DP, AFM.DV DV, AFM.RM RM, AFM.RMSTD RMSTD
WHERE RM.DV_ID = DV.DV_ID AND RM.DP_ID = DP.DP_ID AND DP.DV_ID = DV.DV_ID
AND RM.RM_STD = RMSTD.RM_STD

And if I write it this way, I get an "ORA-00936: missing expression" error:

SELECT RM.BL_ID, RM.FL_ID, RM.RM_ID, RM.AREA, RM.COUNT_EM, DP.NAME,
RM.DP_ID, DV.NAME, RM.DV_ID, RM.RM_CAT, RM.RM_STD, RM.RM_TYPE, RM.RM_USE,
RM.OPTION1, RMSTD.COST_OF_SPACE,
IIF([RM.RM_STD]="LOCKER",[RMSTD.COST_OF_SPACE],[RM.AREA]*[RMSTD.COST_OF_SPACE])
FROM AFM.DP DP, AFM.DV DV, AFM.RM RM, AFM.RMSTD RMSTD
WHERE RM.DV_ID = DV.DV_ID AND RM.DP_ID = DP.DP_ID AND DP.DV_ID = DV.DV_ID
AND RM.RM_STD = RMSTD.RM_STD

JTurner

unread,

Mar 29, 2010, 9:55:02 PM3/29/10

to

Also, for Rik_UK, I don't know how to "declare" a field. In my Excel search,
I found that I can't select something "AS" and that you would caption the
expression in the options (and turn off field names). So I'm probably missing
something...