asked on

Query Design

I have a query that has two fields that get the selection criteria from combo?s on a form. Criteria for field one is ?Like[forms].[frmOne].[cboOne]? (i.e. 1,2,3,4,5). Criteria for field two is ?Like[forms].[frmOne].[cboTwo]? (i.e. A,B,C,D,E). This works fine. However, there is data in field one that is not shown in cboOne (don?t want it shown). So, if cboTwo = E, I want the selection criteria for field one to be cboOne or 6. How do I accomplish this in the query design?

You can use an IIf in the criteria of cboOne, like this:

IIf(forms].[frmOne].[cboTwo] = 'E';[forms].[frmOne].[cboOne] or 6;[forms].[frmOne].[cboOne])

That might do the trick.

HTH,

Co

Ryan Chong

Hi,

Get the ListIndex of [forms].[frmOne].[cboOne] (Or using its Value Property) , then compare it using a Select .. Case Statement ? :

Example:

myIndex = [forms].[frmOne].[cboOne].ListIndex

'Or myValue = [forms].[frmOne].[cboOne].Value

Select Case myIndex 'myValue
Case 0:
Case 1:
Case 2:
.
.
.
Case 6:
Case Else:
End Select

regards.

mardon

ASKER

ok - I tried that and I geet an error. Lets go one step farther, here is the resulting query:

SELECT TOP 20 Sum(MasterRSC.NMC) AS NMC, MasterRSC.WUC7, MasterRSC.Nomenclature, Sum(MasterRSC.NMCM) AS NMCM, Sum(MasterRSC.NMCS) AS NMCS, Sum(MasterRSC.DMMH) AS DMMH, Sum(MasterRSC.[CANN ACT]) AS CANNS, Sum(MasterRSC.BCM) AS BCMs, Sum(MasterRSC.[Items ML1]) AS ML1, Sum(MasterRSC.[Items ML2]) AS ML2, Sum(MasterRSC.RFI) AS RFI, WUC_BY_LEVEL.LEVEL_2
FROM MasterRSC INNER JOIN WUC_BY_LEVEL ON MasterRSC.WUC7 = WUC_BY_LEVEL.WUC
WHERE (((MasterRSC.TEC)=IIf([forms].[frmRSCParmWUC].[cboLevelTwo]="P&P",(MasterRSC.TEC)=[forms].[frmRSCParmWUC].[cboSelTec] Or (MasterRSC.TEC)="TXA-",[forms].[frmRSCParmWUC].[cboSelTec])) AND ((MasterRSC.yrqtr) Between [forms].[frmRSCParmwuc].[cboBeginDate] And [forms].[frmRSCParmwuc].[cboenddate]))
GROUP BY MasterRSC.WUC7, MasterRSC.Nomenclature, WUC_BY_LEVEL.LEVEL_2
HAVING (((Sum(MasterRSC.NMC))>0) AND ((MasterRSC.Nomenclature) Not Like "*Not found*" And (MasterRSC.Nomenclature) Not Like "*noc*") AND ((WUC_BY_LEVEL.LEVEL_2) Like [forms].[frmRSCParmWUC].[cboLevelTwo]))
ORDER BY Sum(MasterRSC.NMC) DESC
WITH OWNERACCESS OPTION;

Is it fixable???????

ASKER CERTIFIED SOLUTION

devtha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mardon

ASKER

I didn't do it exactly the way you describe, but from your comment found that the nested if was the problem. I used some code on the form and checked-changed the values where necessary. It works now, however crude! Thanks -

mardon

ASKER

Sorry about the delay - I intended my previous comment as accepting your comment; "I didn't do it exactly the way you describe, but from your comment found that the nested if was the problem. I used some code on the form and checked-changed the values where necessary. It works now,
however crude! Thanks - "

Thanks again!!

devtha

Cheers.. The function I have mentioned would have been a object oriented approach as you can call it for any form to build a criteria.