Report based on check box and list box...

[Andrew Meador]

I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

Thanks for your help!

[frogsteaks]

On Jan 11, 11:08*am, Andrew Meador <[e-mail]:

* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

* *So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

* *Thanks for your help!

Dynamically build your query when the user clicks the button to
generate the report. I do the exact same thing in one of my databases
where the report form has 8 list boxes, several check boxes, and a
hand full of text boxes to enter various date ranges. Pass the sql
you build to the report via code and you have what you want.

[Andrew Meador]

On Jan 11, 12:38*pm, [e-mail] wrote:

On Jan 11, 11:08*am, Andrew Meador <[e-mail]:

* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

* *So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

* *Thanks for your help!

Dynamically build your query when the user clicks the button to
generate the report. *I do the exact same thing in one of my databases
where the report form has 8 list boxes, several check boxes, and a
hand full of text boxes to enter various date ranges. *Pass the sql
you build to the report via code and you have what you want.- Hide quoted text -

- Show quoted text -

Well, my thinking was close. I need to build the filter in code and
then pass it to the report. I assume I could do it in a similar way as
you are talking about as far as passing the whole query to the report.
The problem is I don't know how to do this. If you can give a sample
or refer me to directions on how to do this that would be excellent!

Thanks!

[frogsteaks]

On Jan 11, 12:47*pm, Andrew Meador <[e-mail]:

On Jan 11, 12:38*pm, [e-mail] wrote:

On Jan 11, 11:08*am, Andrew Meador <[e-mail]:

* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

* *So basically it seems like the filter needs to be changed basedon
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

* *Thanks for your help!

Dynamically build your query when the user clicks the button to
generate the report. *I do the exact same thing in one of my databases
where the report form has 8 list boxes, several check boxes, and a
hand full of text boxes to enter various date ranges. *Pass the sql
you build to the report via code and you have what you want.- Hide quoted text -

- Show quoted text -

* *Well, my thinking was close. I need to build the filter in code and
then pass it to the report. I assume I could do it in a similar way as
you are talking about as far as passing the whole query to the report.
The problem is I don't know how to do this. If you can give a sample
or refer me to directions on how to do this that would be excellent!

Thanks!- Hide quoted text -

- Show quoted text -

many ways to accomplish this. One is to declare a global variable as
a string. In the module where user clicks the button to build the SQL
assign the dynamically built SQL to the global string. Then in the
code for the form set the source to that variable.

[Salad]

Andrew Meador wrote:

I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

Thanks for your help!

This might get you started in a direction that'll work for you.

Private Sub CommandReport_Click()
Dim var As Variant
Dim strF As String 'holds report filter

If Me.ListBoxName.ItemsSelected.Count 0 Then

'some items in listbox are selected. Change ListBoxName
'to your name. Assumes the key is the first column in the
'list box. If you have 4 columns, the first is 0, last is 3.
For Each var In Me.ListBoxName.ItemsSelected
strF = strF & Me.ListBoxName.Column(0, var) & ", "
Next

'remove the ", " and surround keys/values in ()
strF = "(" & Left(strF, Len(strHold) - 2) & ")"

'now see if it is "IN" list or "Exclude" list.
strF = "ID " & IIf(Me.CheckBox, "Not ", "") & _
"IN " & strF & " And "

'result so far is "ID (not) IN (1,2,3) And "

'let's pretend there's a text box code. Use single quotes
If Me.Code "" Then
strF = strF & "Code = '" & Me.Code & "' And "
End If

'lets pretend the dollar amt must be value. No quotes
If Me.DollarAmt 0 Then
strF = strF & "DollarAmt >= " & Me.DollarAmt & " And "
End If

'and we have a date range. Use # around dates
If Not IsNull(Me.FromDate) Then
strF = strF & "DateFld >= #" & Me.FromDate & "# And "
End If
If Not IsNull(Me.ToDate) Then
strF = strF & "DateFld <= #" & Me.ToDate & "# And "
End If

'now remove the trailing "And"
strF = Left(strF, Len(strF) - 5)

'now call the report with filter
DoCmd.OpenReport "TestReport", , , strF

Else
MsgBox "Please select some items from listbox."
End If

Exit Sub
Too Long
http://www.youtube.com/watch?v=hb8uv4lGnno

[Andrew Meador]

On Jan 11, 9:37*pm, Salad <[e-mail]:

Andrew Meador wrote:

* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

* *So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

* *Thanks for your help!

This might get you started in a direction that'll work for you.

Private Sub CommandReport_Click()
* * *Dim var As Variant
* * *Dim strF As String *'holds report filter

* * *If Me.ListBoxName.ItemsSelected.Count 0 Then

* * * * *'some items in listbox are selected. *Change ListBoxName
* * * * *'to your name. *Assumes the key is the first column in the
* * * * *'list box. *If you have 4 columns, the first is 0, last is 3.
* * * * *For Each var In Me.ListBoxName.ItemsSelected
* * * * * * *strF = strF & Me.ListBoxName.Column(0, var) &", "
* * * * *Next

* * * * *'remove the ", " and surround keys/values in ()
* * * * *strF = "(" & Left(strF, Len(strHold) - 2) & ")"

* * * * *'now see if it is "IN" list or "Exclude" list.
* * * * *strF = "ID " & IIf(Me.CheckBox, "Not ", "") & _
* * * * * * *"IN " & strF & " And "

* * * * 'result so far is "ID (not) IN (1,2,3) And "

* * * * *'let's pretend there's a text box code. *Use single quotes
* * * * *If Me.Code "" Then
* * * * * * *strF = strF & "Code = '" & Me.Code & "' And"
* * * * *End If

* * * * *'lets pretend the dollar amt must be value. *No quotes
* * * * *If Me.DollarAmt 0 Then
* * * * * * *strF = strF & "DollarAmt >= " & Me.DollarAmt & " And "
* * * * *End If

* * * * *'and we have a date range. *Use # around dates
* * * * *If Not IsNull(Me.FromDate) Then
* * * * * * *strF = strF & "DateFld >= #" & Me.FromDate & "# And "
* * * * *End If
* * * * *If Not IsNull(Me.ToDate) Then
* * * * * * *strF = strF & "DateFld <= #" & Me.ToDate & "# And "
* * * * *End If

* * * * *'now remove the trailing "And"
* * * * *strF = Left(strF, Len(strF) - 5)

* * * * 'now call the report with filter
* * * * *DoCmd.OpenReport "TestReport", , , strF

* * *Else
* * * * *MsgBox "Please select some items from listbox."
* * *End If

Exit Sub

Too Longhttp://www.youtube.com/watch?v=hb8uv4lGnno- Hide quoted text -

- Show quoted text -

Wow! Thanks! That was really helpfull, I'm still fresh to actually
getting hands on coding done as well as working in Access and this was
very enlightening. Not too long for me, gave me a few new ideas!
I appreciate your time and thoroughness!

Andrew