Can you 'nest' a WHERE?

[Amadea]

Hello all. Just wondering. I've included a SQL statement that has a compound WHERE. Now I want to add an additional required condition. Can I nest it by placing parens before and after the current WHERE and add my added condition with an AND before the parens? Like this:

"WHERE [COLOR=red yellow][AllSections].[A]="" AND [/color]([AllSections].[Instr1] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "' OR [AllSections].[Instr2] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "' OR [AllSections].[Instr3] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "' OR [AllSections].[Instr4] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "') " & _

    strSQL2 = "SELECT [AllSections].[A], [AllSections].[Master], [AllSections].[Sect], [AllSections].[CourseID], [AllSections].[BeginTime1] AS BeginTime, [AllSections].[EndTime1] AS EndTime, [AllSections].[Days1] AS Days, [AllSections].[Room1] AS Room, [AllSections].[BeginDate], [AllSections].[Wks], [AllSections].[MaxSeats] " & _
        "FROM AllSections " & _
        "WHERE [AllSections].[Instr1] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "' OR [AllSections].[Instr2] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "' OR [AllSections].[Instr3] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "' OR [AllSections].[Instr4] = '" & Me!cboInstrName & "' AND [AllSections].[DivNum]= '" & Me!txtDivNum & "' " & _
        "ORDER BY [AllSections].[Master], [AllSections].[CourseID];"

Amadea

 

[hneal98]

you can not nest a where clause, but you can change how the items in the where clause are looked at. For example look at the following:

Say I have A, B, and C

You can do something like this:

where
A=1 and
(B=1 or
C=3)

Hope that helps.

 

[Amadea]

Yes, I see. And this helps very much. I see now how I have to state the criteria line. I'll still have to repeat a phrase but not in the format I thought was correct.
Thanks!!!

Amadea

 

[PHV]

Be careful when mixing AND & OR operators !
A and B or C and D is not the same as
A and (B or C) and D which is not the same as
(A and B) or (C and D)

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