We help IT Professionals succeed at work.

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?

Comment
Watch Question

Co

Commented:
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 ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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???????
Commented:
nested iif are very dangerous or atleast complicated. So use a function to build the criteria from the form's provided values. What you can do is assign a tag to each field. cbo1 is refering to field 1 so the tag will be
sfield1 where s is string.
n for number/integer
d1 for [from date]
d2 for [To Date]
so at runtime the
string will be build as this

for i = 1 to fieldcounts of form
if left(fields(i).tag),1) = "s" then
  criteria =  fields(i).tag & "'= " & me!cbo1 & "'"
end if
next

you can do this for all fields and data types and also do not forget if criteria has somthing assigned then you want to use criteria = criteria &......




Author

Commented:
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 -

Author

Commented:
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!!

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.