Select   gl.ID1 as ID1,
         gl.ID2 as ID2,
         field1 as alias1,
         field2 AS EIN, field3 as alias2, 
         gl.field4 as aliasDate1, 
         case gl.field5 
            When '1/1/1900' then 
                '12/31/2099' 
            else gl.field5 
         end as aliasDate2, 
         glc.field6 as aliasDate3,
         glc.field7 as aliasDate4,
         field8 as alias3,
         field9 as alias4 
from tblMaster gl inner join tblDetail1 glc on
         gl.ID1 = glc.ID1 and gl.ID2 = glc.ID2
inner join tblDetail2 glp on
         glp.ID1 = gl.ID1 and glp.ID2 = gl.ID2
Where    @VBBuildCriteria
Order by gl.ID1, gl.ID2, glc.field6, glc.field7

quote:

---

Originally posted by mwjdavidson

You don't have to use dynamic SQL. You can do something along these lines:

SELECT Field1, Field2, etc.
FROM MyView
WHERE (Field1 = @Field1 OR @Field1 IS NULL)
AND (Field2 = @Field2 OR @Field2 IS NULL)
etc.



Mark

---

WHERE (Field1 = @Field1 OR Field1 IS NULL)
AND (Field2 = @Field2 OR Field2 IS NULL)

WHERE (Field1 = @Field1 OR @Field1 IS NULL)
AND (Field2 = @Field2 OR @Field2 IS NULL)

/fieldCode=fieldValue/fieldCode=fieldValue/fieldCode=fieldValue

'***Each text box control contains in it's tag property the corresponding 
'   fieldCode and = sign preceded by the /
'***Criteria will be created concatenating tag and text
'   of each text box control in the form that is not empty
    For Each ctl In Me
        If TypeOf ctl Is TextBox Then
            If ctl.Text <> "" Then
                criteria = criteria & ctl.Tag & ctl.Text
            End If
        End If
    Next
'***Retrieve results from database
    conn.Open cnSTR
    Set rec1 = conn.excecute "mySP " & criteria