By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,346 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,346 IT Pros & Developers. It's quick & easy.

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

P: 10
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!
Dec 28 '06 #1
Share this Question
Share on Google+
8 Replies


P: 76
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)))
Dec 28 '06 #2

P: 10
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.
Dec 28 '06 #3

P: 10
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])))
Dec 28 '06 #4

P: 10
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?
Dec 29 '06 #5

P: 76
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]
Dec 29 '06 #6

P: 10
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.
Dec 29 '06 #7

P: 76
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:
Expand|Select|Wrap|Line Numbers
  1. Me.ComboName.Rowsource = strWhereClause()
  2.  
Make sure you have your Row Source Type on the combobox set to "Table/Query." Then make the function:
Expand|Select|Wrap|Line Numbers
  1. Public Function strWhereClause
  2. strWhereClause = "SELECT [Date] FROM [qryName] "
  3.  
  4. Select Case IsNull(Me.BegDate.Value)
  5.  
  6. Case True
  7. If IsNull(Me.EndDate.Value) = True then 
  8. Exit Function
  9. Else
  10. strWhereClause = strWhereClause & "WHERE [Date] <= " & me.EndDate.Value
  11. End If
  12.  
  13. Case False
  14. If IsNull(me.EndDate.Value) = True Then 
  15. strWhereClause = strWhereClause & "WHERE [Date] >= " & Me.BegDate.Value
  16. Else
  17. strWhereClause = strWhereClause & "WHERE [Date] BETWEEN " & Me.Begdate.Value & " AND " & Me.EndDate.Value
  18. End If
  19.  
  20. End Select
  21. End Function
  22.  
Dec 29 '06 #8

NeoPa
Expert Mod 15k+
P: 31,494
Does this get you anywhere?
Expand|Select|Wrap|Line Numbers
  1. Between Nz(Forms!frm_CamasData.BegDate,#1/1/1900#) 
  2.     And NZ(Forms!frm_CamasData.EndDate,#12/31/9999#)
Dec 30 '06 #9

Post your reply

Sign in to post your reply or Sign up for a free account.