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

Using IIF in Query Criteria based on Option Group

P: 7
I have an option group (TimeFrameOption) with three options: All, Last 6 months, and Last 12 Months. When an option is selected, I want to query dates in a listbox (CallReport) to display all dates (I used Date() - 9999999999 days for all), dates within the last six months, or dates within the last year.

I placed this formula in the criteria in the SQL query builder for the listbox.

IIf([timeframeoption]=1,[CallReport].[Date] Between Date()-9999999999 And Date(),IIf([timeframeoption]=2,[CallReport].[Date] Between Date()-180 And Date(),([CallReport].[Date]) Between Date()-365 And Date()))

I used only the between and statements and Access added [CallReport].[Date] to the syntax.

I tested the date formulas invididually and they all work fine outside of the IIF statement. For instance, Between Date() and Date() -180 shows all records for the last 6 months.

Any suggestions on the syntax of the IIF statement?

May 14 '10 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 931
So, are you saying that one of the columns in the listbox contains the date, and you want to narrow down the items that appear in the listbox based upon what the user selects in the option group? If this is the case what are you using as the record source for the listbox?

May 14 '10 #2

Jim Doherty
Expert 100+
P: 897
Reconsider the logic sequence Jason instead of using an IIIF formula (which looks unwieldy and tiresome) Change it to the AfterUpdate event for the option group so that, whatever changes are made in the option group will populate the listbox commensurate with the value selected in the option group 1,2,or 3

The VBA code for the Afterupdate event will look like this

Expand|Select|Wrap|Line Numbers
  1. 'First set the main portion of SQL to satisfy a consistent dataset
  2. 'for the listbox
  3. Dim MySQL as String
  4. Dim MyCriteria as String
  5. MySQL = "SELECT FieldName1,FieldName2 FROM YourTableName WHERE " & MyCriteria
  7. Select Case Me!TimeFrameOption
  8. Case is = 1
  9. MyCriteria="[CallReport].[Date] Between Date()-9999999999 And Date()"
  10. Case is = 2
  11. MyCriteria = "[CallReport].[Date] Between Date()-180 And Date()"
  12. Case is = 3
  13. MyCriteria = "[CallReport].[Date]) Between Date()-365 And Date()"
  14. End Select
  16. Me!YourListboxName.rowsource=MySQL & MyCriteria
As you can see you can keep adding extra bits to this Select Case code should you need to add more elements to your option group frame and not
continually be confused wondering if something falls correctly on the side of a True/False IFF statement.

I dont know how familar you are with VBA but the above method is a much better way of handling this type of listbox population. As you can see the SQL to support the rowsource property is built on the fly and passed to the rowsource of the listbox at runtime only after someone selects a value from the option group.
May 14 '10 #3

Jim Doherty
Expert 100+
P: 897
Whoops sorry Pat, didnt see you there obviously in at same time as me :-)
May 14 '10 #4

Expert 100+
P: 931
@Jim Doherty
Don't worry about it...I was going to suggest something similar to you but wanted to make sure I understood what the OP was trying to do with the listbox.
May 14 '10 #5

P: 7
Worked very well after I added the other criteria to the SQL statement that I had in the query builder. If someone else uses this code, they will want to remove the ) in Case 3 after [CallReport].[Date].

I very much appreciate the assistance.

May 14 '10 #6

Post your reply

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