ISNull Problem 1

[hdgirl]

I have two parameter boxes that ask for start date and end date and they both work fine but i want the user to be able to hit return with no dates inserted and get all records. I have tried Or IsNull but this gives no records at all.What am i doing wrong CJB

 

[hermanlaksko]

There are 2 ways to handle this problem.
1. In your code stop the code when you have your SQL statement, check this code in a query I think that you will find that you have an "OR" statement somewhere.

2. If Dates To/From isnull then me.Recordsource = "Select * From YrTbl"

 

[Steve101]

Here's a simple example that will work within the query window, when you hit return without entering a value for a parameter.

SELECT *
FROM tblYourTable
WHERE IIf(IsNull([Enter SampleId]),[SampleId],[Enter SampleId])

This will return all records if no value is supplied to the parameter.

For your particular example, with two dates, you could use the same approach, accept that you need to accomodate the range handling, and also the situation where one, or the other or both parameters are left null. Try the following:

This accomodates the different combinations and the range handling by using 'low' and 'high' dates to replace the null entered values, and using >= and <= operators.

Note the approach that I'm presenting is suitable for handling running interactive queries through the query window. If you're processing querydefs via recordsets, there are alternative and better ways of handling this.

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

This is the seconds query that I referred to in my last post. It illustrates using a range of dates, and allows hitting the return key, instead of entering one or both of the respective date values:

   PARAMETERS [Enter Date From] DateTime, [Enter Date To] DateTime;
   SELECT *
   FROM Table1
   WHERE DateFrom >=IIf(IsNull([Enter Date From]),#1/1/1900#,[Enter Date From]) 
   AND   DateTo   <=IIf(IsNull([Enter Date To]),#12/31/2099#,[Enter Date To]);

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[hdgirl]

Steve,

I put this what you had in where into the criteria of my date field and it brought back no records if i inserted a date or just hit the return key what did i do wrong CJB

 

[Steve101]

CJB,

If you have just a single date field (as opposed to a range of dates), then it may be a bit trickier. If there is no time component then the first form as per my first post should work; ie.

PARAMETERS [Enter Date] DateTime
SELECT *
FROM Table1
WHERE YourDate =IIf(IsNull([Enter Date]),YourDate,[Enter Date])

Let me know if this works and is what you want, otherwise we'll have to 'refine' it Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[hdgirl]

NoSteve i am still not getting it...think i must be placing it in the wrong place, i am entering YourDate =IIf(IsNull([Enter Date]),YourDate,[Enter Date]) into the criteria of the date field and i am still getting nothing back......i am using 2000 CJB

 

[Sawatzky]

dear cbj,

perhaps your parameter boxes do not return NULL but &quot;&quot;.

HTH

regards Astrid

 

[mike777]

Here's my two cents' worth:
Ahem...

My personal favorite way of dealing with null values is with the nz function.
select *
from mytable
where name=nz(name,&quot;*&quot;

This worked well for me with text fields.
Date fields were a different experience. None of the suggestinos above worked out for me (I use 2000 also). I am inclined to say that you can't really do what you want to do through the query window. Have to go to code on this one, I believe.

-Mike

 

[CosmoKramer]

Here's two more cents worth:

Set code like this as your date field criteria, of course changing to your field/table/query names:

Between IIf(IsNull([Enter beginning date]),DMin("[YourDateField]","YourTable"),[Enter beginning date]) And IIf(IsNull([Enter end date]),DMax("[YourDateField]","YourTable"),[Enter end date])

If the beginning date is left blank, the earliest date from your table will be substituted, and if the end date is blank, the most recent date will be substituted. If both are left blank........all records will be returned.....

 

[Steve101]

CJB,

You should only be entering the =IIf(IsNull([Enter Date]),YourDate,[Enter Date]) bit into the criteria area of the date field, in the query grid.

Otherwise work in SQL view (view, SQL View options), and cut and paste and then modify my code as required.
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[hdgirl]

I tried all responses and was able to get the success from CosmoKramer so thanks very much cosmo and thanks everone for their help CJB

 

[mike777]

has to be the name
unfair advantage

 

[CosmoKramer]

I can't make a living off of Movie Phone forever.......