Re: is null and Asterisk wildcard
- From: "Eddie Holder" <eddie.holder@xxxxxxxxxxxxxxxxx>
- Date: 16 Jun 2006 06:48:03 -0700
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" <eddie.holder@xxxxxxxxxxxxxxxxx> wrote in message
news:1150462843.214486.280780@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxx> wrote in message
news:1150450734.982634.280580@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
.
- Follow-Ups:
- Re: is null and Asterisk wildcard
- From: Allen Browne
- Re: is null and Asterisk wildcard
- References:
- is null and Asterisk wildcard
- From: eddie . holder
- Re: is null and Asterisk wildcard
- From: Allen Browne
- Re: is null and Asterisk wildcard
- From: Eddie Holder
- Re: is null and Asterisk wildcard
- From: Allen Browne
- Re: is null and Asterisk wildcard
- From: Eddie Holder
- is null and Asterisk wildcard
- Prev by Date: High Volume Data over LAN/WAN/VPN etc...
- Next by Date: Re: I received a 3072 error from ms access 97
- Previous by thread: Re: is null and Asterisk wildcard
- Next by thread: Re: is null and Asterisk wildcard
- Index(es):
Relevant Pages
|
