Jet SQL Translation

[kermitforney]

Having issues with Jet SQL and an Access database that has already been developed. I have the basics down but need help with a few parts that I don't understand.

This is an Append query from the database mentioned:

INSERT INTO stblControlOpsPrivs ( upNo, upForm, upSubForm, upInquire, upEdit, upAdd, upDelete, upReports, upOpt1, upOpt2, upOpt3, upOpenMode, upDateUpdated )

SELECT stblControlOps.opNo, ctblSysPrivs.spForm, ctblSysPrivs.spSubForm, IIf([spMgmtDefaults]=True,IIf([opMgmt]=True Or [opSuperUser]=True,True,False),True) AS Expr1, IIf([opMgmt]=True Or [opSuperUser]=True Or [opEdit]=True,IIf([spEditOk]=True And [spMgmtDefaults]=False,True,IIf([opMgmt]=True,True,False)),False) AS Expr2, IIf([opMgmt]=True Or [opSuperUser]=True Or [opEdit]=True,IIf([spAddOk]=True And [spMgmtDefaults]=False,True,False),False) AS Expr3, IIf([opMgmt]=True Or [opSuperUser]=True Or [opDelete]=True,IIf([spDelOk]=True And [spMgmtDefaults]=False,True,False),False) AS Expr4, Yes AS Expr5, No AS Expr6, No AS Expr7, No AS Expr8, IIf([spSubForm]="*","Inquire","N/A") AS Expr9, Now() AS Expr10

FROM ctblSysPrivs, stblControlOps

WHERE (((stblControlOps.opNo)=[Forms]![sfrmControlOps]![txtopNo]));

I understand the basic SELECT, FROM, and WHERE clauses but some of the other stuff I have never sen before. I will list them numbered below.

1.) IIf

2.) Exp1 (an expression I am assuming)

From what little I do understand this is or may be a conditional append query (If, Then). Just having trouble interpreting it.

Any links to helpful sites would be greatly appreciated. Thanks in advance guys. )

 

[Golom]

An IIF statement has the form

IIF (SomeCondition, TRUE Result, FALSE Result)

It returns the TRUE Result if SomeCondition evaluates to TRUE and FALSE Result if SomeCondition is FALSE.

and you can embed them as in

IIF ( Condition1,
   IIF (Condition2,
      IIF (Condition3, True3, False3), False2), False1)

Exp1 is called a field alias and it is the name that will be used for the field that results from the evaluation of the IIF statement in the results of the query.