Using an expression in a query to search for a specified date range

[anb001]

I have q query, where one of the fields is a date (ranging from approx. 1880-1998).

I have previously received some assistance from 'John Big Booty' on this area, however I'm completely stock. When trying to use below nested IIF expression, I only get 3 results in the listbox, where the data is shown. All from December 1899.

CODE:Iif (IsNull([Forms]![FrmSearch]![StartDate]),<=Date() And <= [Forms]![FrmSearch]![EndDate], Iif(IsNull([Forms]![FrmSearch]![EndDate]), >=[Forms]![FrmSearch]![StartDate],Iif(IsNull([Forms]![FrmSearch]![StartDate]) And IsNull([Forms]![FrmSearch]![EndDate]), < Date() And > Date(), >=[Forms]![FrmSearch]![StartDate] And <= [Forms]![FrmSearch]![EndDate])))

The 4 parts (3 true and 1 false) it is checking for should be these:
1. Textbox 'StartDate' = Null & Textbox 'EndDate' = Not null
2. Textbox 'EndDate' = Null & Textbox 'StartDate' = Not null
3. Textbox 'StartDate' = Null & Textbox 'EndDate' = Null
4. Textbox 'StartDate' = Not null & Textbox 'EndDate' = Not null

If I use below expressions (one at the time), it works fine.

1. < [Forms]![frmSearch]![EndDate]
2. > [Forms]![frmSearch]![StartDate]
3. < Date()
4. Between [Forms]![frmSearch]![StartDate] And [Forms]![frmSearch]![EndDate]

I have also tried inserting specific dates in the nested IIF expression, and then it works fine, but when I use the nested IIF expression, a things go wrong, and I simply can't see why.

I hope that someone can give me a push in the right direction.

Thanks. /Anders

 

[darwin25]

You got your nested IIF all wrong.

CODE:Iif (IsNull([Forms]![FrmSearch]![StartDate]),<=Date() And <= [Forms]![FrmSearch]![EndDate], Iif(IsNull([Forms]![FrmSearch]![EndDate]), >=[Forms]![FrmSearch]![StartDate],Iif(IsNull([Forms]![FrmSearch]![StartDate]) And IsNull([Forms]![FrmSearch]![EndDate]), < Date() And > Date(), >=[Forms]![FrmSearch]![StartDate] And <= [Forms]![FrmSearch]![EndDate])))


Those in red should be the THEN clause of your statement, not the condition clause. In using Nested IIF, if you want to use your THEN or ELSE clause to validate conditions, you must use it in a IIF statement hence, the NESTED IIF

 

[namliam]

In addition when you have complex IIfs like this it is a good idea to indent and space them:

CODE:
Iif (IsNull([Forms]![FrmSearch]![StartDate])
  , <=Date() And <= [Forms]![FrmSearch]![EndDate]
  , Iif(IsNull([Forms]![FrmSearch]![EndDate])
    , >=[Forms]![FrmSearch]![StartDate]
    , Iif(IsNull([Forms]![FrmSearch]![StartDate]) And IsNull([Forms]![FrmSearch]![EndDate])
      , < Date() And > Date()
      , >=[Forms]![FrmSearch]![StartDate] And <= [Forms]![FrmSearch]![EndDate])))

 

[anb001]

Darwin25,

Thank you for your input, however I'm quite confused now. I actually thought that the THEN and ELSE part were correct.

Roughly "translated" (just to illustrate my intention more simplified)::

IF isNull(StartDate) THEN <=Date() AND <= EndDate ELSEIF IsNull(EndDate) THEN >= StartDate ELSEIF IsNull(StartDate) AND IsNull(EndDate) THEN < Date() And > Date() ELSE >= StartDate AND <= EndDate

The idea is that the criteria should be according to the state of two date formatted text boxes.

I would appreciate if you could elaborate a little bit more, perhaps give a small example.

Thanks.

/Anders

 

[namliam]

The syntax for this is:
IIF( logical statement , what to do if true, what to do if false )

You cannot parse other "logical" statements back to the query neither... What you want is not an IIF, I dont think so...

What you are looking for is a simple query with OR statements

 

[anb001]

Namlian,

I have actually already tried with simple approach, with using 'OR' between each criteria (or just writing the criterias in each of their own line), but that doesnlt work.

When I open the form in question, the two text boxes both have 'null' values, and since one of the criterias is to show all dates in that case, same is also shown in the listbox. However, when I enter dates in the text boxes, nothin happens. It looks like it keeps on using the initial criteria.

Actually, I was of teh impression that nested IIF functions could be used in the expression buider, just as if you were doing so with the IF function in Excel, or with IF ... THEN ... ELSEIF etc, in VBA.

I would highly appreciate if someone could should be an example on how and what to use. Unfortunately I can't attached and example of my database, as I'm not able to upload/download files from where I am (a ship), but I should be able to do so soon, if required.

Thanks.

/Anders

 

[Brianwarnock]

I had to do this a long time ago so nolonger have the code, but what i did was wrote a function that returned Yes or No meaning select or not, I did all of the testing in the function where you can use Block Ifs .
query would have a field
Required:myfunction([mydatefield])
and criteria "YES"


Brian

 

[Brianwarnock]

Namlian,

I have actually already tried with simple approach, with using 'OR' between each criteria (or just writing the criterias in each of their own line), but that doesnlt work.

When I open the form in question, the two text boxes both have 'null' values, and since one of the criterias is to show all dates in that case, same is also shown in the listbox. However, when I enter dates in the text boxes, nothin happens. It looks like it keeps on using the initial criteria.


/Anders

Did you rerun the query, from a command button on the dates form preferably

Brian

 

[namliam]

If you are experienced with excel IFs, you should be able to work this one. They work exactly the same...

Iif (IsNull([Forms]![FrmSearch]![StartDate])
  , <=Date() And <= [Forms]![FrmSearch]![EndDate]
  , Iif(IsNull([Forms]![FrmSearch]![EndDate])
    , >=[Forms]![FrmSearch]![StartDate]
    , Iif(IsNull([Forms]![FrmSearch]![StartDate]) And IsNull([Forms]![FrmSearch]![EndDate])
      , < Date() And > Date()
      , >=[Forms]![FrmSearch]![StartDate] And <= [Forms]![FrmSearch]![EndDate])))

Probably would turn out something like:

Iif (IsNull([Forms]![FrmSearch]![StartDate])
  , "YES" 
  , IIF ( [Forms]![FrmSearch]![StartDate] <=Date() And [Forms]![FrmSearch]![StartDate] <= [Forms]![FrmSearch]![EndDate]
    , "YES" 
    , "NO"
    )
  )

Really sql WHERE and using the OR is definatly the way to go, the fact that you cannot seem to get it working doesnt mean it isnt the right way.

IIFs are a fall back, last ditch method, any other method by far is preferable. If only for the sake of maintainability

 

[Brianwarnock]

I prefer to use a function it is simple and clear, I'm a simple guy.
I wrote this for a little test DB
The advantage of keeping the SQL Where clause simple is that it is easy to combine with other criteria
eg
WHERE (((table1a.id)<9) AND ((frequired([mydate]))="YES"))

Function frequired(mydate As Date) As String

If IsNull(Forms!frmdates!startdate) And IsNull(Forms!frmdates!enddate) Then
frequired = "YES"
Exit Function
End If

If IsNull(Forms!frmdates!startdate) Then
    If Forms!frmdates!enddate >= mydate Then
    frequired = "YES"
    Else: frequired = "NO"
    End If
Exit Function
End If

If IsNull(Forms!frmdates!enddate) Then
    If Forms!frmdates!startdate <= mydate Then
    frequired = "YES"
    Else: frequired = "NO"
    End If
Exit Function
End If

If mydate >= Forms!frmdates!startdate And mydate <= Forms!frmdates!enddate Then
frequired = "YES"
Else: frequired = "NO"
End If

End Function

Brian