[AccessD] Re: SQL ANDs

Martin,

Create 2 identical queries. Suffix one with a 0 (zero) and the other with a 
1.  The 0 query is your base query.  Then use the following code:

dim qdf as dao.querydef
set qdf=currentdb.querydefs("Query_0")
dim strSQL as string
set strsql = mid(qdf.sql,1,len(qdf.sql)-3)
' removes the ; at the end and 3 is the right number
dim strWhere as string
strwhere = " WHERE "
if not isnull(me!textbox1) then
     strwhere = "YourColumn = " & me!TextBox1 & " and "
end if
if not isnull(me!textbox2) then
     strwhere = "YourColumn = " & me!TextBox2 & " and "
end if
if not isnull(me!textbox3) then
     strwhere = "YourColumn = " & me!TextBox3 & " and "
end if
' remove trailing and
strwhere = mid(strwhere,1,len(strwhere)-5) & ";"
set qdf=currentdb.querydefs("Query_1")
qdf.sql = strsql & strwhere
qdf.close
docmd.openreport "YourReportName"

Query_1 is always going to be the source for the report.

This method can be used for all reporting.

Robert


At 03:43 AM 01/07/2004 -0500, you wrote:
>Date: Thu, 1 Jul 2004 08:30:12 +1000
>From: "Martin Caro" <mcaro at bigpond.net.au>
>Subject: [AccessD] SQL ANDs
>To: "Access Developers discussion and problem solving"
>         <accessd at databaseadvisors.com>
>Message-ID: <002b01c45ef1$cf262790$0100000a at mitmaster>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>Hi Folks
>
>I have a report query that has a WHERE followered by one or a number of 
>ANDs. The components of the ANDs reference text boxes on a form each of 
>which may or may not be populated. How can I get it to negate one or a 
>number of the ANDs if the target box is Null? At the moment if any one of 
>the AND components is Null no records are returned. I've played around 
>with IIF to test each box first but no luck so far....
>
>Thanks
>
>Martin