INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Pass Parameter from Form to Query

Pass Parameter from Form to Query

(OP)
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:

CODE -->

=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

RE: Pass Parameter from Form to Query

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

RE: Pass Parameter from Form to Query

(OP)
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.

RE: Pass Parameter from Form to Query

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:

CODE --> vba

Private Sub Frame14_AfterUpdate()
    Select Case Me.Frame14
        Case 1  'Add comments here
            Me.BegDate = Date - 7
            Me.EndDate = Date
        Case 2  'Add comments here
        
        Case 3  'Add comments here
        
        Case 4  'Add comments here
        
    End Select
End Sub 

Duane
Hook'D on Access
MS Access MVP

RE: Pass Parameter from Form to Query

(OP)
Ahhh Ok... I understand.

That's a great idea! Thank you!

Rob

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close