is null and Asterisk wildcard

[eddie.holder]

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

[Allen Browne]

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.

--
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.

<ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ r2g2000cwb.goog legroups.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

[Eddie Holder]

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.

--
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.

<ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ r2g2000cwb.goog legroups.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

[Allen Browne]

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" <ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ f6g2000cwb.goog legroups.com...

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.

<ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ r2g2000cwb.goog legroups.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

[Eddie Holder]

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:

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" <ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ f6g2000cwb.goog legroups.com...

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.

<ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ r2g2000cwb.goog legroups.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

[Eddie Holder]

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:

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:

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" <ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ f6g2000cwb.goog legroups.com...

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.
>
> <ed**********@m ousetraining.co m> wrote in message
> news:11******** **************@ r2g2000cwb.goog legroups.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

[Allen Browne]

The brackets matter when you mix AND and OR:

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] & "*"));

--
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" <ed**********@m ousetraining.co m> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .

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?

[ginnyk]

You can also check out the Solutions.mdb. Look under "Work with combo
boxes, list boxes, subforms, and subreports." for "Add (All) to a
list"