Ok, one last thing, this is what my statement looks like at this stage.
It works fine for searching on model, but not make????
WHERE (((([Forms]![QueryData]![MakeName]) Is Null)) OR (((Cars.MAKE)
Like [Forms]![QueryData]![MakeName] & "*")) AND
(([Forms]![QueryData]![ModelName]) Is Null)) OR (((Cars.MODEL) Like
[Forms]![QueryData]![ModelName] & "*"));
Any ideas where I am going wrong?
Eddie
Eddie Holder wrote:[color=blue]
> Thanks again Allen, its a great example of a search form, but requires
> VB. I will give it a bash doing the sql statement and see where I end
> up on. Appreciate your help.
>
> Regards,
> Eddie Holder
>
>
> Allen Browne wrote:[color=green]
> > Provided you are carefull with the bracketing, you can extend that idea:
> > WHERE ((xx Is Null) OR ([Field1 = xx))
> > AND ((yy Is Null) OR (Field2 = yy))
> > AND ...
> >
> > But this does get messy to maintain and inefficient to execute. A better
> > idea is to build the Filter for your form, or the WhereCondition for your
> > OpenReport (or even the SQL property for your QueryDef) from only those
> > boxes that have a value.
> > To see how it's done, download this small sample database:
> >
http://allenbrowne.com/unlinked/Search2000.zip
> > Requires Access 2000 or later.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users -
http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> > "Eddie Holder" <eddie.holder@mousetraining.com> wrote in message
> > news:1150462843.214486.280780@f6g2000cwb.googlegro ups.com...[color=darkred]
> > > Hi Allen
> > > Thanks a million for your response, it worked perfectly! What would
> > > the statement look like if I created more serach fields, for example I
> > > needed to be able to search using 5 textboxes, all individual, or if
> > > some was completed partially, it uses the them as criteria AND
> > > statement?
> > >
> > > Thanks for the reply, most appreciated! Thanks, Eddie Holder
> > >
> > >
> > >
> > > Allen Browne wrote:
> > >> Set up the WHERE clause of the query like this:
> > >> WHERE (([forms].[formname].[texboxname] Is Null)
> > >> OR ([MyField] Like [forms].[formname].[texboxname] & "*"))
> > >>
> > >> If the text box on the form is null, the first part of the expression
> > >> returns True, so the condition is True for all records. If the first part
> > >> is
> > >> not true (i.e. the text box has something in it), only fields that match
> > >> are
> > >> returned.
> > >>
> > >> <eddie.holder@mousetraining.com> wrote in message
> > >> news:1150450734.982634.280580@r2g2000cwb.googlegro ups.com...
> > >> > Hi ladies and gents. I'm hoping anyone will be able to help me in a
> > >> > small access problem I am having. Let me try to explain:
> > >> >
> > >> > I have a form with textboxes which I use as criteria for a query. The
> > >> > form is used to allow my users to search for specific company names or
> > >> > if left blank, all the company names, so I built my criteria as
> > >> > follows: Like [forms].[formname].[texboxname] & chr(42)
> > >> >
> > >> > The form works ok if I am looking for a spesific company name, but when
> > >> > left blank, the results excludes Is Null records, as the wildcard wants
> > >> > to have a record with any characters in it.
> > >> >
> > >> > Heres my Question. How could I build the criteria to say if the form
> > >> > field is left blank, bring back all records including null values.
> > >> >
> > >> > I hope anyone can be of any help or guidance. - Regards, Eddie Holder[/color][/color][/color]
