A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Criteria to return all records if selection from form is null



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 04:44 AM posted to microsoft.public.access.queries
Sue Pari
external usenet poster
 
Posts: 2
Default Criteria to return all records if selection from form is null

Hi,

I'm trying to pass multiple query criteria from form controls. I'm using
the following type of syntax for the criteria:

Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

And this works fine for the fields where there are no Nulls in the data, but
if there are Nulls in this field I don't get those records. Quite
understandable since Like "*" doesn't return Nulls.

What I'm trying to do is to return all records when there is nothing chosen
in the selection box on the form. I've tried a bunch of different IIfs with
various use of quote marks, on the order of:

IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is Null
Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])

but they either don't get all the records or they kick a "too complex"
error.

I'm sure this must be easier than I'm making it. Could anyone give me some
help?

Thanks in advance,

Sue

  #2  
Old May 27th, 2010, 05:08 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default Criteria to return all records if selection from form is null

Sue Pari wrote:
Hi,

I'm trying to pass multiple query criteria from form controls. I'm using
the following type of syntax for the criteria:

Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

And this works fine for the fields where there are no Nulls in the data, but
if there are Nulls in this field I don't get those records. Quite
understandable since Like "*" doesn't return Nulls.

What I'm trying to do is to return all records when there is nothing chosen
in the selection box on the form. I've tried a bunch of different IIfs with
various use of quote marks, on the order of:

IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is Null
Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])

but they either don't get all the records or they kick a "too complex"
error.

I'm sure this must be easier than I'm making it. Could anyone give me some
help?

Thanks in advance,

Sue


I would probably build the filter on the fly (in the click event of a button)
and then pass the filter you build to a form/report when you open it. There
are some examples of filtering reports by using listbox controls etc on
accessweb. http://www.mvps.org/access/forms/frm0007.htm

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201005/1

  #3  
Old May 27th, 2010, 10:15 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default Criteria to return all records if selection from form is null

It is possible to craft the WHERE clause of the query to it evaluates to
TRUE if the control on the form is null.

Switch the query to SQL View.
Locate the WHERE clause. It will be something like this:
WHERE SomeField = [Forms]![ViewEdit Completed Procedures]![SelectOBy]
Change it like this:
WHERE (([Forms]![ViewEdit Completed Procedures]![SelectOBy] Is Null)
OR ([SomeField] Like [Forms]![ViewEdit Completed
Procedures]![SelectOBy]))

Be careful with the bracketing when mixing ANDs and ORs.

This approach quickly gets unwieldy as you add more options.
It is much more efficient to build a filter string dynamically.
This article explains how to do that:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Includes a downloadable sample file (free.)

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


"Sue Pari" wrote in message
...
Hi,

I'm trying to pass multiple query criteria from form controls. I'm using
the following type of syntax for the criteria:

Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

And this works fine for the fields where there are no Nulls in the data,
but if there are Nulls in this field I don't get those records. Quite
understandable since Like "*" doesn't return Nulls.

What I'm trying to do is to return all records when there is nothing
chosen in the selection box on the form. I've tried a bunch of different
IIfs with various use of quote marks, on the order of:

IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is
Null Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])

but they either don't get all the records or they kick a "too complex"
error.

I'm sure this must be easier than I'm making it. Could anyone give me
some help?

Thanks in advance,

Sue


  #4  
Old May 27th, 2010, 01:27 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Criteria to return all records if selection from form is null

Sue,

One additional possibility if your table is fairly small OR if the field is
not indexed. Add a zero-length string to the field. You can do this even if
the field is indexed, it just means that the index won't be used and the query
will take a bit longer to run.

Field: MySearchField: [Name of Field] & ""
Criteria: Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

You can experiment with this and see if the performance hit is acceptable.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Sue Pari wrote:
Hi,

I'm trying to pass multiple query criteria from form controls. I'm
using the following type of syntax for the criteria:

Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

And this works fine for the fields where there are no Nulls in the data,
but if there are Nulls in this field I don't get those records. Quite
understandable since Like "*" doesn't return Nulls.

What I'm trying to do is to return all records when there is nothing
chosen in the selection box on the form. I've tried a bunch of
different IIfs with various use of quote marks, on the order of:

IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is
Null Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])

but they either don't get all the records or they kick a "too complex"
error.

I'm sure this must be easier than I'm making it. Could anyone give me
some help?

Thanks in advance,

Sue

  #5  
Old May 27th, 2010, 08:51 PM posted to microsoft.public.access.queries
Sue Pari
external usenet poster
 
Posts: 2
Default Criteria to return all records if selection from form is null

Thanks to you all for the excellent advice! I'll work through the different
approaches and see what I can do. I really appreciate the help.

Sue


"Sue Pari" wrote in message
...
Hi,

I'm trying to pass multiple query criteria from form controls. I'm using
the following type of syntax for the criteria:

Like Nz([Forms]![ViewEdit Completed Procedures]![SelectOBy],"*")

And this works fine for the fields where there are no Nulls in the data,
but if there are Nulls in this field I don't get those records. Quite
understandable since Like "*" doesn't return Nulls.

What I'm trying to do is to return all records when there is nothing
chosen in the selection box on the form. I've tried a bunch of different
IIfs with various use of quote marks, on the order of:

IIf(IsNull([Forms]![ViewEdit Completed Procedures]![SelectOBy]),"Not Is
Null Or Is Null",[Forms]![ViewEdit Completed Procedures]![SelectOBy])

but they either don't get all the records or they kick a "too complex"
error.

I'm sure this must be easier than I'm making it. Could anyone give me
some help?

Thanks in advance,

Sue


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.