Using IIF in Query Criteria based on Option Group

[hbaf208]

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?

Thanks,
Jason

[patjones]

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?

Pat

[Jim Doherty]

@hbaf208
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

'First set the main portion of SQL to satisfy a consistent dataset
'for the listbox
Dim MySQL as String
Dim MyCriteria as String
MySQL = "SELECT FieldName1,FieldName2 FROM YourTableName WHERE " & MyCriteria
 
Select Case Me!TimeFrameOption
Case is = 1
MyCriteria="[CallReport].[Date] Between Date()-9999999999 And Date()"
Case is = 2
MyCriteria = "[CallReport].[Date] Between Date()-180 And Date()"
Case is = 3
MyCriteria = "[CallReport].[Date]) Between Date()-365 And Date()"
End Select
 
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.

[Jim Doherty]

@zepphead80
Whoops sorry Pat, didnt see you there obviously in at same time as me :-)

[patjones]

@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.

[hbaf208]

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.

Thanks,
Jason