Pass a parameter to a query via code (A2K3)

Topic

New Reply

You could do something like this. It opens the report with a WhereCondition argument instead of passing the condition to the query.

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

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.

Reply | Quote

Replies

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.

Reply | Quote

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) & “)”?

Reply | Quote

No, you’d need to treat that check box separately, e.g.

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Yes….right as always

Reply | Quote

Edited by HansV to correct stupid mistake

Here is a version of the code I posted earlier expanded to set txtStart and txtEnd:

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

Reply | Quote

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)

Reply | Quote

Oops, of course. Thanks for the correction, I’ll apply it to my previous reply.

Reply | Quote