Query criteria for Filtering Date fields: IIF Nested Operators won't work

[Ragbrai]

Howdy All,

I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains the combo box. The fields I am working with are date fields that are formated as Short Date. I have written an IIF statement and placed it within the Criteria section of the Date field that I am trying to filter by. The current IIF statement works ONLY when both the BegDate field and the EndDate fields (these are the unbound fields on the form) are NULL, it does not work when the date fields contain values. Here is the partially working IIF statement:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate]),IIf(IsNull([Forms]![frm_CamasData]![EndDate]),"*", IIf(IsNull([Forms]![frm_CamasData]![EndDate]),([qry_Add_Events_frm].[Date])>=[Forms]![frm_CamasData]![BegDate],([qry_Add_Events_frm].[Date]) Like IIf(IsNull([Forms]![frm_CamasData]![BegDate]),([qry_Add_Events_frm].[Date])<=[Forms]![frm_CamasData]![EndDate],([qry_Add_Events_frm].[Date]) Between [Forms]![frm_CamasData]![BegDate] And [Forms]![frm_CamasData]![EndDate]))))

If I take parts of the nested statements and use them on their own they work, but they don't when they are part of this statment. For example, if I replace the above statement with:

Between [Forms]![frm_CamasData]![BegDate] And [Forms]![frm_CamasData]![EndDate]

The results for the combo box work fine when I put dates into both the BegDate and EndDate fields.

The same is true for other parts of the original IIF, another example that works fine when its a stand alone statment is:

>=[Forms]![frm_CamasData]![BegDate]

This works as well if I only put in a BegDate value in the unbound BegDate field.

Thus it seems the parts of my IIF statement are correct but they will not work together in the version I have.

Thanks in advance for your help!

[jpatchak]

Howdy All,

I have a query that is used for filtering results to be used in a combo box. The query needs to test fields from both a table and then unbound text boxes on the form that also contains the combo box. The fields I am working with are date fields that are formated as Short Date. I have written an IIF statement and placed it within the Criteria section of the Date field that I am trying to filter by. The current IIF statement works ONLY when both the BegDate field and the EndDate fields (these are the unbound fields on the form) are NULL, it does not work when the date fields contain values. Here is the partially working IIF statement:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate]),IIf(IsNull([Forms]![frm_CamasData]![EndDate]),"*", IIf(IsNull([Forms]![frm_CamasData]![EndDate]),([qry_Add_Events_frm].[Date])>=[Forms]![frm_CamasData]![BegDate],([qry_Add_Events_frm].[Date]) Like IIf(IsNull([Forms]![frm_CamasData]![BegDate]),([qry_Add_Events_frm].[Date])<=[Forms]![frm_CamasData]![EndDate],([qry_Add_Events_frm].[Date]) Between [Forms]![frm_CamasData]![BegDate] And [Forms]![frm_CamasData]![EndDate]))))

If I take parts of the nested statements and use them on their own they work, but they don't when they are part of this statment. For example, if I replace the above statement with:

Between [Forms]![frm_CamasData]![BegDate] And [Forms]![frm_CamasData]![EndDate]

The results for the combo box work fine when I put dates into both the BegDate and EndDate fields.

The same is true for other parts of the original IIF, another example that works fine when its a stand alone statment is:

>=[Forms]![frm_CamasData]![BegDate]

This works as well if I only put in a BegDate value in the unbound BegDate field.

Thus it seems the parts of my IIF statement are correct but they will not work together in the version I have.

Thanks in advance for your help!

It's difficult to tell what you're trying to do here. There does appear to be a problem with your 2nd nested conditional IIF(ISNULL([EndDate])... You're saying in that 2nd conditional: If the end date is null then output *, otherwise, if the end date is null then...

It looks like this is at least one problem with your conditionals. If you want to continue to use nested conditionals here, I would do something like:

Like IIF(IsNull([BegDate]) AND IsNull([EndDate]), "*", IIF(IsNull([EndDate]), Whatever happens when EndDate is null and BegDate isn't, IIF(IsNull([BegDate]), Whatever happens when BegDate is Null and EndDate isn't, Whatever happens when they're both not null)))

[Ragbrai]

Thanks for the feedback. I have tried your suggestion and put the two IsNull statements together, however when I do this no records are returned. This is the code that combines them:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate]) and IsNull([Forms]![frm_CamasData]![EndDate]),"*",IIf(IsNull([Forms]![frm_CamasData]![EndDate]),([qry_Add_Events_frm].[Date])>=[Forms]![frm_CamasData]![BegDate],IIf(IsNull([Forms]![frm_CamasData]![BegDate]),([qry_Add_Events_frm].[Date])<=[Forms]![frm_CamasData]![EndDate],([qry_Add_Events_frm].[Date]) Between [Forms]![frm_CamasData]![BegDate] And [Forms]![frm_CamasData]![EndDate])))

Seems like the above (using your suggestion) should work but it doesn't, in fact after changing the code and then updating the form the code is changed by Access to what you see below:

Like IIf(([qry_Add_Events_frm].[Date])=IsNull([Forms]![frm_CamasData]![BegDate]) And ([qry_Add_Events_frm].[Date])=IsNull([Forms]![frm_CamasData]![EndDate]),"*",IIf(IsNull([Forms]![frm_CamasData]![EndDate]),([qry_Add_Events_frm].[Date])>=[Forms]![frm_CamasData]![BegDate],IIf(IsNull([Forms]![frm_CamasData]![BegDate]),([qry_Add_Events_frm].[Date])<=[Forms]![frm_CamasData]![EndDate],([qry_Add_Events_frm].[Date]) Between [Forms]![frm_CamasData]![BegDate] And [Forms]![frm_CamasData]![EndDate])))

As you can see Access is changing my reference from the unbound BegDate and EndDate Fields to the bound DATE field, which is not at all what I want to happen. I can't seem to figure out a way to tell it not to do this.

A little more details may help. On the form I'm working with (frm_CamasData) there are two unbound date fields, one is BegDate and one is EndDate. I want to use these two fields as the basis of the filter criteria for the combo box. What happens is the user inputs date information into either one or both of these fields, then they have the option to select one of three command buttons that Filter the record set based on their input, If only BegDate data is entered then the filter returns all records with dates >= the BegDate value and the EndDate value is set as NULL, If EndDate data is entered but no BegDate then the Filter returns all records with a date value <= EndDate value and sets the BegDate value to NULL. If both Beg and End Date values are entered then the filter returns the records whose date values is between the Beg and End Values. All of these described filters work fine on the form. The problem is that I also want a combo box that can be used for navigation and that ONLY displays the records that have been filtered as described. Getting the correct values to display in the combo box is where the problem is.

This may be a bit complex but I am hopeful that someone out there has some ideas.

[Ragbrai]

Well I figured out how to get the combined IsNull part of the statement to work:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",...

However everything after that part won't return any records still, here is a copy of the complete statement but it only works when BOTH the BegDate and EndDate fields are null.

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",IIf(IsNull([Forms]![frm_CamasData]![EndDate])=True,([qry_Add_Events_frm].[Date])>=[Forms]![frm_CamasData]![BegDate],IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True,([qry_Add_Events_frm].[Date])<=[Forms]![frm_CamasData]![EndDate],([qry_Add_Events_frm].[Date]) Between [Forms]![frm_CamasData]![BegDate] And [Forms]![frm_CamasData]![EndDate])))

[Ragbrai]

One more discovery, I think if we can figure this out then I can use it to fix the above.

If I take the IF statement above and simplify for testing purposes to:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",[Forms]![frm_CamasData]![BegDate])

It works but only returns the exact date of the BegDate field if both BegDate and EndDate are Null. If I modify it to:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",>=[Forms]![frm_CamasData]![BegDate])

It no longer works for the dates when the Beg and End Date values are not both NULL, and the computer changes the expression to:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",([qry_Add_Events_frm].[Date])>=[Forms]![frm_CamasData]![BegDate])

So it seems the problem is how I am dealing with the >=, <=, and Between operators as they are the items that are not working correctly. What should this syntex look like?

[jpatchak]

One more discovery, I think if we can figure this out then I can use it to fix the above.

If I take the IF statement above and simplify for testing purposes to:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",[Forms]![frm_CamasData]![BegDate])

It works but only returns the exact date of the BegDate field if both BegDate and EndDate are Null. If I modify it to:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",>=[Forms]![frm_CamasData]![BegDate])

It no longer works for the dates when the Beg and End Date values are not both NULL, and the computer changes the expression to:

Like IIf(IsNull([Forms]![frm_CamasData]![BegDate])=True And IsNull([Forms]![frm_CamasData]![EndDate])=True,"*",([qry_Add_Events_frm].[Date])>=[Forms]![frm_CamasData]![BegDate])

So it seems the problem is how I am dealing with the >=, <=, and Between operators as they are the items that are not working correctly. What should this syntex look like?

Just a thought, but one problem might be with the "IsNull" function, Instead of IsNull(<expr>) = true, try <expr> IS NULL.

I think it's likely that you're having problems because Access sometimes has trouble evaluating expressions of a time/date data type. For the >= piece, try using the syntax > <expr> + 1.

Like IIf([Forms]![frm_CamasData]![BegDate] IS NULL And [Forms]![frm_CamasData]![EndDate] IS NULL,"*",>[Forms]![frm_CamasData]![BegDate]-1

If that doesn't work, move 'Like' inside -

IIf([Forms]![frm_CamasData]![BegDate] IS NULL And [Forms]![frm_CamasData]![EndDate] IS NULL,"Like '*'",>=[Forms]![frm_CamasData]![BegDate]

[Ragbrai]

Thanks for the suggestions but no luck.

The issue really seems to be focused on the operators that I'm using with the IIF statement (between, >=,<=) since if they are not used then thing work (but not how I need them too.

[jpatchak]

Thanks for the suggestions but no luck.

The issue really seems to be focused on the operators that I'm using with the IIF statement (between, >=,<=) since if they are not used then thing work (but not how I need them too.

I guess if you can't get it to work with the nested IIF operators, set the rowsource of your combo manually with vba in the after update events of your text boxes. In the after update event of the text box, have it call a use this function like this:

Me.ComboName.Rowsource = strWhereClause()
 

Make sure you have your Row Source Type on the combobox set to "Table/Query." Then make the function:

Public Function strWhereClause
strWhereClause = "SELECT [Date] FROM [qryName] "
 
Select Case IsNull(Me.BegDate.Value)
 
Case True
If IsNull(Me.EndDate.Value) = True then 
Exit Function
Else
strWhereClause = strWhereClause & "WHERE [Date] <= " & me.EndDate.Value
End If
 
Case False
If IsNull(me.EndDate.Value) = True Then 
strWhereClause = strWhereClause & "WHERE [Date] >= " & Me.BegDate.Value
Else
strWhereClause = strWhereClause & "WHERE [Date] BETWEEN " & Me.Begdate.Value & " AND " & Me.EndDate.Value
End If
 
End Select
End Function
 

[NeoPa]

Does this get you anywhere?

Between Nz(Forms!frm_CamasData.BegDate,#1/1/1900#) 
    And NZ(Forms!frm_CamasData.EndDate,#12/31/9999#)