[AccessD] SQL ANDs

Thanks Arthur.... I'll have a play and let you known how it goes. I'm
currently looking at building the form's Filter on the fly depending on the
txtbBoxes with data (a bit like Filter by Form) but without using that
functionality.. will look at several options.

Martin

----- Original Message -----
From: "Arthur Fuller" <artful at rogers.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Thursday, July 01, 2004 11:34 PM
Subject: RE: [AccessD] SQL ANDs


> First of all, put some code in the open event that declares a few
> variables and assigns the form's controls' values to them, so that your
> query is testable and printable. Something like this:
>
> Dim x, y, z
> x = Forms!frm!txtBox1
> y = Forms!frm!txtBox2
> Etc.
>
> Then plug these vars into your query, so it looks something like:
>
> SELECT * FROM SomeTables
> WHERE
> (fld1 = x OR x IS NULL)
> AND
> (fld2 = y OR y IS NULL)
> AND   ... etc
>
> Note that I'm removing the IsNull() tests out of the code and replacing
> them with SQL "IS NULL" tests.
>
> Now you can test the query on its own without worrying about the form at
> all. I think that the syntax above will give you what you want, but I
> wrote it in the Outlook compiler... :)
>
> HTH,
> Arthur
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Martin Caro
> Sent: Wednesday, June 30, 2004 10:07 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] SQL ANDs
>
>
> Thanks for the suggestion Stephen but I tried inserting that syntax as
> criteria for each field  but with no luck, it looked promising. Even
> when I tried just IsNull(Forms!frm!txtBox1) I got zero records when
> txtBox1 was empty.
>
> Any other thoughts?
>
> Martin
>
> ----- Original Message -----
> From: "Stephen Bond" <stephen at bondsoftware.co.nz>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Thursday, July 01, 2004 11:27 AM
> Subject: RE: [AccessD] SQL ANDs
>
>
> > How about ...
> >
> > WHERE
> > (fld1 = Forms!frm!txtBox1 OR IsNull(Forms!frm!txtBox1))
> > AND
> > (fld2 = Forms!frm!txtBox2 OR IsNull(Forms!frm!txtBox2))
> > AND   ... etc
> >
> > Stephen Bond
> >
> > > -----Original Message-----
> > > From: Martin Caro [mailto:mcaro at bigpond.net.au]
> > > Sent: Thursday, 1 July 2004 10:30 a.m.
> > > To: Access Developers discussion and problem solving
> > > Subject: [AccessD] SQL ANDs
> > >
> > >
> > > 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
> > > --
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com