Results 1 to 10 of 10
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass a parameter to a query via code (A2K3)

    You could do something like this. It opens the report with a WhereCondition argument instead of passing the condition to the query.
    <code>
    Private Sub cmdReport_Click()
    Dim strIn As String
    Dim i As Integer

    For i = 1 To 4
    If Me.Controls("opt" & i) = True Then
    strIn = strIn & ", " & i
    End If
    Next i

    If strIn = "" Then
    MsgBox "Please select at least one quarter!", vbExclamation
    Exit Sub
    End If

    strIn = "Qtr In (" & Mid(strIn, 3) & ")"
    DoCmd.OpenReport "rptSomething", acViewPreview, , strIn
    End Sub
    </code>
    Substitute the correct names where needed.

    BTW I'd use check boxes. Option buttons are intended for mutually exclusive choices, and check boxes for situations where the user may select multiple options.

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pass a parameter to a query via code (A2K3)

    Good Afternoon,

    I have 4 option buttons, opt1, opt2, opt3, and opt4. Each option button represents the Qtr. The user may want to view a report based on a specific QTR so by selecting the qtr (option button) they want then the value needs to be passed to a query.

    I'm not sure on how to build the logic to capture which option (and they can select more than 1) the user selected and I don't know how to pass that value to the query.

    I have the query built, I have the report built, I just need to pass the user's request to the query.

    For instance.

    if user selects option 2 and 3, I need to pass "2 and 3" as a criteria to the query to only pull the data for 2nd and 3rd quarter.
    if user selects option 4, I need to pass "4" as a criteria to the query to only pull the data for 4th quarter.

    Can someone please point me in the right direction.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a parameter to a query via code (A2K3)

    BEAUTIFUL.....Oh thank you Hans.

    What if I wanted to include another checkbox (opt5) and title it "QTD" to pull all current quarters for the year? Would I change For i = 1 To 4 to For i = 1 To 5 and change this strIn = "Qtr In (" & Mid(strIn, 3) & ")" to strIn = "Qtr In (" & Mid(strIn, 4) & ")"?
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass a parameter to a query via code (A2K3)

    No, you'd need to treat that check box separately, e.g.
    <code>
    Private Sub cmdReport_Click()
    Dim strIn As String
    Dim i As Integer

    If Me.QTD = True Then
    DoCmd.OpenReport "rptSomething", acViewPreview
    Exit Sub
    End If

    For i = 1 To 4
    If Me.Controls("opt" & i) = True Then
    strIn = strIn & ", " & i
    End If
    Next i

    If strIn = "" Then
    MsgBox "Please select at least one quarter!", vbExclamation
    Exit Sub
    End If

    strIn = "Qtr In (" & Mid(strIn, 3) & ")"
    DoCmd.OpenReport "rptSomething", acViewPreview, , strIn
    End Sub</code>

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a parameter to a query via code (A2K3)

    Thanks again Hans.....

    Now, can I ask yet another question, please?

    Taking the scenerio I've outlined in this thread and on the code you've provided, is there an effecient way to determine the following:

    Determine the FIRST Date of the first selected quarter (i) and the LAST date of the last selected quarter selected (i)? i.e. Let's assume the user selected opt1 and opt2 (1st and 2nd quarter), txtBegin would be 1/1/CurrentYear and txtEnd would be 5/31/CurrentYear?

    If the user selected opt2, opt3 and opt4 (2nd, 3rd and 4th quarter) then the txtBegin would be 4/1/CurrentYear and txtEnd would be 12/31/CurrentYear.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass a parameter to a query via code (A2K3)

    In your first example, shouldn't txtEnd be 6/30/CurrentYear?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a parameter to a query via code (A2K3)

    Yes....right as always
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass a parameter to a query via code (A2K3)

    Hans,

    You always provide excellent and effecient solutions and I just can't thank you enough. Sincerely.

    I had to make a minor change to the code because txtStart and txtEnd didn't change no matter the selection. In case anyone needs this in the future, the change I made is below.

    Original:
    Me.txtStart = DateSerial(Year(Date), 3 * i - 2, 1)
    Me.txtEnd = DateSerial(Year(Date), 3 * i + 1, 0)
    Change:
    Me.txtStart = DateSerial(Year(Date), 3 * Lo - 2, 1)
    Me.txtEnd = DateSerial(Year(Date), 3 * Hi + 1, 0)
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass a parameter to a query via code (A2K3)

    Oops, of course. <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Thanks for the correction, I'll apply it to my previous reply.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pass a parameter to a query via code (A2K3)

    Edited by HansV to correct stupid mistake

    Here is a version of the code I posted earlier expanded to set txtStart and txtEnd:
    <code>
    Private Sub cmdReport_Click()
    Dim strIn As String
    Dim i As Integer
    Dim Lo As Integer
    Dim Hi As Integer

    If Me.QTD = True Then
    Me.txtStart = DateSerial(Year(Date), 1, 1)
    Me.txtEnd = DateSerial(Year(Date), 12, 31)
    DoCmd.OpenReport "rptSomething", acViewPreview
    Exit Sub
    End If

    Lo = 5
    Hi = 0
    For i = 1 To 4
    If Me.Controls("opt" & i) = True Then
    strIn = strIn & ", " & i
    If i < Lo Then
    Lo = i
    End If
    If i > Hi Then
    Hi = i
    End If
    End If
    Next i

    If strIn = "" Then
    MsgBox "Please select at least one quarter!", vbExclamation
    Exit Sub
    End If

    Me.txtStart = DateSerial(Year(Date), 3 * Lo - 2, 1)
    Me.txtEnd = DateSerial(Year(Date), 3 * Hi + 1, 0)
    strIn = "Qtr In (" & Mid(strIn, 3) & ")"
    DoCmd.OpenReport "rptSomething", acViewPreview, , strIn
    End Sub</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •