Access SQL query IIF 3

[SQLScholar]

Hey all,

I am trying to write a query for Access (in SQL) which has something like this in the select

IIF(`table 1`.`feild 1`=10,`table 1`.`field 2` AS `comm`,`table 1`.`feild 3` AS `comm`)

But when i run it, i get a synatax error - too few parametres.

Any ideas,

TIA

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[SQLScholar]

Sorry it actually says missing operator. Actually insted of an example - here is the code

IIF(`Policy Details`.`Class of Policy` = 'igi',`Introducer Details-Scheme`.`LetRes Comm` AS `Comm`,`Introducer Details-Scheme`.`Buildings Comm` AS ` Comm2`)

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Neilll]

If I've understood what you're trying to do correctly,
your bracket looks to be in the wrong place

IIF(`Policy Details`.`Class of Policy` = 'igi',`Introducer Details-Scheme`.`LetRes Comm`) AS `Comm`,
Introducer Details-Scheme`.`Buildings Comm` AS Comm2`

 

[PHV]

A column in a query has an UNIQUE name.
Either add some text in the value of the column:
IIf([Policy Details].[Class of Policy] = 'igi', 'Comm:' & [Introducer Details-Scheme].[LetRes Comm], 'Comm2:' & [Introducer Details-Scheme].[Buildings Comm]) As CommOrComm2
Or create 2 columns:
IIf([Policy Details].[Class of Policy] = 'igi', [Introducer Details-Scheme].[LetRes Comm], '') As Comm,
IIf([Policy Details].[Class of Policy] = 'igi', '', [Introducer Details-Scheme].[Buildings Comm]) As Comm2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

Aha - that was fantastic and fast. I didnt want the `comm:` bit as i wanted them in the same column. Basically it uses another field to chose which field should populate the new column.

So i now have

IIf([Policy Details].[Class of Policy] = 'igi',[Introducer Details-Scheme].[LetRes Comm],[Introducer Details-Scheme].[Buildings Comm]) As CommOrComm2

and it works

Any chance i can do an AND to evalate more things at the start??

Thanks again

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

The 1rst argument of the IIf function is any expression that can be evaluated to True or False.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

I am now trying to get it all working with an OR - so its now

IIf(OR([Policy Details].[Class of Policy] = 'igi', [Policy Details].[Class of Policy] = 'Letrs', [Policy Details].[Class of Policy] = 'link (let res', [Policy Details].[Class of Policy] = 'lglet'), [Introducer Details-Scheme].[LetRes Comm],[Introducer Details-Scheme].[Buildings Comm])) AS `Payment to Agent`

Any ideas whats wrong - i am again getting syntax errors.

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

Try a syntax like this:
IIf(A=1 Or A=2 Or A=3, Value1, Value2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

tried this

IIf([Policy Details].[Class of Policy] = 'igi' OR [Policy Details].[Class of Policy] = 'Letrs' OR [Policy Details].[Class of Policy] = 'link 'let res' OR [Policy Details].[Class of Policy] = 'lglet', [Introducer Details-Scheme].[LetRes Comm], [Introducer Details-Scheme].[Buildings Comm]) AS `Payment to Agent`

Still getting syntax error

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

The syntax error is here:
'link [highlight]'[/highlight]let res'
If you really have a single quote in the tested string, then try this:
'link ''let res'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

AHA - you are an absolute star.. it should have been 'link (let res)'. I read through that so many times and just went over it. Its hard proof reading your own code!

Thanks again

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Davidmc555]

Solved my problem as well which was just a simple Select query.

SELECT [Order] AS OrderNo, DateTime, OrigDate, OrigLoadID, ReqDate, ReqloadID, Reason, Details, WhoBy, IIF([AddTakes]![Addon] = 0, 'Add on', 'Take Off') As Direction

I had it working in SQL but not in access but thanks to the code by PHV above, I now have the solution.

Thanks again.