Pass Parameter from Form to Query

[trickshot1126]

Good afternoon,

I have searched high an low and tried as many workarounds that i can think of... I am still in the same place as i was at 8 Am yesterday morning.

I am attempting to run a query that is filtered by dates. I have a form that i have created with an option group to provide options for "Single date", "Weekly", "Monthly" and "Custom" date ranges. In the query i have the date field and table along with where in the total section. For the criteria i am trying to use a nested "Iif" that uses the option group result (1-4) to pin point the info that actually filters the date properly based on the users selection.

The "Iif" statement is:

=IIf([Forms]![tblRptMn]![Frame14]=1,[Forms]![tblRptMn]![SglDate],IIf([Forms]![tblRptMn]![Frame14]=4,"between " & [Forms]![tblRptMn]![BegDate] & " and " & [Forms]![tblRptMn]![EndDate],IIf([Forms]![tblRptMn]![Frame14]=3,"DatePart('m',[close_time])=" & [Forms]![tblRptMn]![MonthSel] & " And Year([close_time])=" & [Forms]![tblRptMn]![cboYr],IIf([Forms]![tblRptMn]![Frame14]=2,"DatePart('ww',[close_time])=" & Right([Forms]![tblRptMn]![CmbWkly],2) & " And Year([close_time])=" & Left([Forms]![tblRptMn]![CmbWkly],4),[Date]))))

I can confirm that each date format works by itself in the criteria field... However, once it is placed inside the "Iif" statement the code always goes to "[date]" which would be the false portion of the "Iif."

I have also attempted to place the info in an unbound text box on the form and reference the textbox in the criteria field with the same result. I attempted switch and was not able to get the result needed either.

Any help would be greatly appreciated!!

Rob

 

[dhookom]

You can't place operators like "between" inside the IIf() expression. I typically use a couple text boxes that I fill using code in the after update based on the option group selection.

Duane
Hook'D on Access
MS Access MVP

 

[trickshot1126]

Thanks dhookom for the reply.

When i placed the iif in the criteria field i removed the quotations around between and other fields. I had tried using an unbound textbox on the form that included the iif statement and filled the text box with text depending on the option group selection. However, when i placed the form reference in the criteria field i received an error that stated that the code was either incorrect or to complicated to run. But if i copied the code for that selection say the month option and placed it directly in the criteria field of my query it ran with no issue.

 

[dhookom]

Okay, let me try again. You can't include " and ", "Between", and other similar stuff inside the IIf(). In addition IMO nesting any more than two IIf()s is beyond readability and maintainability. I would use two text boxes to your form, BegDate and EndDate. Use code in the after update event of the option group to set the values of the text boxes. You can also allow the user to enter any values in the text boxes. Your code might look something like:

Private Sub Frame14_AfterUpdate()
    Select Case Me.Frame14
        Case 1  [COLOR=#4E9A06]'Add comments here[/color]
            Me.BegDate = Date - 7
            Me.EndDate = Date
        Case 2  [COLOR=#4E9A06]'Add comments here[/color]
        
        Case 3  [COLOR=#4E9A06]'Add comments here[/color]
        
        Case 4  [COLOR=#4E9A06]'Add comments here[/color]
        
    End Select
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[trickshot1126]

Ahhh Ok... I understand.

That's a great idea! Thank you!

Rob