Need Advice on Proper SQL Solution

[Cheryl3D]

Hi:

I am in the process of converting a Access MDB to an Access ADP. I'm using an Access 2000 project (ADP) with a SQL Server 2000 backend.

I would like to get any suggestions on how to resolve an issue that involves properly converting a SQL statement from Access 2000 to SQL Server 2000 -- that heavily uses immediate if (IIF) statements within the WHERE clause.

I need help on converting the immediate IIF statements used to generate the a report. (Especially the IIF statements with Forms!form_name!form_object syntax.)

The IIF is used throughout this query (see below) to determine whether or not the checkbox on the form checked off or not (e.g. (IIf([Forms]![FrmAgreement]![ab]=-1, [qryAnnex]![RCost]>0,...)

Can I use this same query in conjunction with SQL Server 2000 to run the report? Will I need to use a view or can I use a stored procedure? Which is best in this situation?

Bottom line of my question is how do I convert this kind of SQL query from an Access 2000 MDB environment to a SQL Server 2000 environment?

I have a form that is full of check boxes for generating an ad hoc report. The form is bound to the following query in the Access 2000 MDB called QryAgreement:

SELECT QryUnionBase.[Host Base], QryUnionBase.[Office Symbol], QryUnionBase.CertStat, QryUnionBase.CertPOC, QryUnionBase.Comptroller, QryUnionBase.[ID #], QryUnionBase.SRAN, QryUnionBase.JULIAN, QryUnionBase.Receiver, QryUnionBase.Supplier, QryUnionBase.OBANOAC, QryUnionBase.Receiver_Name, QryUnionBase.Receiver_Phone, QryUnionBase.Receive_Email, QryUnionBase.Location, QryUnionBase.ExeComptroller, QryUnionBase.Agreement, QryUnionBase.Department, QryUnionBase.SR, QryUnionBase.Officers, QryUnionBase.Enlisted, QryUnionBase.Civilians, QryUnionBase.Contractors, QryUnionBase.[Analyst POC], QryUnionBase.Reimbursable, QryUnionBase.Reimburse, QryUnionBase.NONrbs, QryUnionBase.Reimb_Positions, QryUnionBase.Reimb_Notes, QryUnionBase.[SAM _POC], QryUnionBase.Status, QryUnionBase.[Rec'd_Coord], QryUnionBase.Sent_to, QryUnionBase.Date_Sent_for_Coord, QryUnionBase.Suspense_for_Coord, QryUnionBase.Coord_Complete, QryUnionBase.[Rec'd_for_Annex], QryUnionBase.Suspense_for_Annex, QryUnionBase.Annex_Complete, QryUnionBase.[Rec'd_for_Sign], QryUnionBase.Sig_Complete, QryUnionBase.[Pending Status], QryUnionBase.[Original_ISA_ Start_ Date], QryUnionBase.TriAnnual_Complete, QryUnionBase.Annual_Review_Complete, QryUnionBase.Terminated_Date, QryUnionBase.[BH], qryAnnex.TabSupplier.[ID #], qryAnnex.TabAnnex.[ID #], qryAnnex.Receiver, qryAnnex.WorkyearCost, TabOrg.Org, qryAnnex.Categories, qryAnnex.SubCategory, qryAnnex.Support, qryAnnex.RDescription, qryAnnex.RUnits, qryAnnex.RUnitCost, qryAnnex.NRManpower, qryAnnex.NRDescription, qryAnnex.NRUnits, qryAnnex.NRUnitCost, qryAnnex.NRWorkyearCost, qryAnnex.RCost, qryAnnex.NRCost, qryAnnex.Document, qryAnnex.Billed, qryAnnex.Coll
FROM ((QryUnionBase LEFT JOIN qryAnnex ON (QryUnionBase.Receiver = qryAnnex.Receiver) AND (QryUnionBase.[ID #] = qryAnnex.TabSupplier.[ID #])) LEFT JOIN QryCategories ON qryAnnex.Categories = QryCategories.Categories) LEFT JOIN TabOrg ON QryCategories.Org = TabOrg.Org
WHERE (((QryUnionBase.SR)=IIf([Forms]![FrmAgreement]!<sup>=-1,&quot;1&quot Or ((QryUnionBase.SR)=IIf([Forms]![FrmAgreement]![REC]=-1,&quot;2&quot Or (((QryUnionBase.SR)=IIf([Forms]![FrmAgreement]![MOAMOUS]=-1,&quot;3&quot Or (QryUnionBase.SR)=(IIf([Forms]![FrmAgreement]![MOAMOUR]=-1,&quot;4&quot))))) And
(((QryUnionBase.BH)=IIf([Forms]![FrmAgreement]![BOL]=-1, &quot;1&quot Or (QryUnionBase.BH)=(IIf([Forms]![FrmAgreement]![HQ]=-1, &quot;2&quot))))
And (IIF([Forms]![FrmAgreement]![SID] is Null, [QryUnionBase]![ID #] Not Like &quot;Luc*&quot; Or [QryUnionBase]![ID #] Is Null,[QryUnionBase]![ID #]=[Forms]![FrmAgreement]![SID])) And (IIf([Forms]![FrmAgreement]![a]=-1, [qryAnnex]![RCost]>0,[qryAnnex]![RCost]=0 or [qryAnnex]![RCost]>0)) And (((IIf([Forms]![FrmAgreement]![e]=-1,[QryUnionBase]![Department]=&quot;10&quot) Or (IIf([Forms]![FrmAgreement]![f]=-1,[QryUnionBase]![Department]=&quot;20&quot) Or (IIf([Forms]![FrmAgreement]![g]=-1,[QryUnionBase]![Department]=&quot;30&quot) Or (IIf([Forms]![FrmAgreement]![h]=-1,[QryUnionBase]![Department]=&quot;40&quot) Or (IIf([Forms]![FrmAgreement]![ik]=-1,[QryUnionBase]![Department]=&quot;50&quot) Or (IIf([Forms]![FrmAgreement]![j]=-1,[QryUnionBase]![Department]=&quot;60&quot)) Or (IIf([Forms]![FrmAgreement]![e]=0 And [Forms]![FrmAgreement]![f]=0 And [Forms]![FrmAgreement]![g]=0 And [Forms]![FrmAgreement]![h]=0 And [Forms]![FrmAgreement]!=0 And ...;


Thanks.
Any help is greatly appreciated.

Cheryl</sup>

 

[SQLSister]

IIF does not work in SQL Server. The equivalent command is Case. You should look this up in Books Online to get the proper syntax. Have fun converting all thoses IIF statements.

 

[Cheryl3D]

Thanks SQLSister:

I need to know in more detail how the CASE statement in a SQL Server would work on an Access 2000 form. I know that I cannot use a CASE statement in a view, so that I would have to use it in a stored procedure.

To actually get the reponse from the object on the form wouldn't I need a parameter?


Thanks,
Cheryl

 

[SQLSister]

Yes a stored procedure would work. There is no way I know of for a view to pull input parameters from an Access form. You would set up input parameters in the stored procedure for each object you want to pull a value from in the form. Then set each onject = to a vba variable in your code and use those variables as the input parameters when calling the sotred procedures. More detail on this would probably be best found onthe Access vba programming forum. I haven't done this in almost two years, so I don't rememember the vba code right offhand.