Prev: Conditional Formatting on Range of Cells
Next: Calculating column A * column B = Results in another column
From: JTurner on 29 Mar 2010 17:58
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??!!!
From: FSt1 on 29 Mar 2010 18:14
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




"JTurner" wrote:

> 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??!!!
From: JTurner on 29 Mar 2010 18:39
I've tried the IIF as well. It keeps returning the error "ORA-00907: missing
right parthensis". Does that help troubleshoot?
From: FSt1 on 29 Mar 2010 19:22
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?
From: Rik_UK on 29 Mar 2010 20:28
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" wrote:

> I've tried the IIF as well. It keeps returning the error "ORA-00907: missing
> right parthensis". Does that help troubleshoot?
 |  Next  |  Last
Pages: 1 2
Prev: Conditional Formatting on Range of Cells
Next: Calculating column A * column B = Results in another column