Query from Mulit-Selection List Box (2000)

Topic

New Reply

There is no way to do this from within the query. You will have to build a WHERE clause in the form from the user’s selections in the listbox, then use that as one of the criteria when you open the Report using the Docmd.OpenReport method.

Reply | Quote

Replies

I have an Access 2000 database where there is a REPORTS form.

On this form the user can input a date range & has a few other optional criteria selections. After the user makes their selections, there is a list of reports (presented as forms).
The user would then select the report they want by clicking a button. The form for the selected report will then appear. This form has an underlying query that uses the criteria information entered on the Reports form. The reports are presented in forms instead of reports as the user then has the ability to drill into the summary data presented on the form.

Currently 2 of those selections are in Combo Boxes.

In my query I reference these combo boxes using the NZ function so if the user does not select any options, it is not filtered.
Here is an example from my query – Like nz([forms]![frm_Reports]![Combo29],”*”)
This works great. If the user makes no selections, nothing is filtered here. If they make a selection in that combo box, it is filtered on that selection.

What my user really wants to do is to be able to select none, 1 or more codes from the list.

I can change from using a Combo Box to a List Box & have the list box Multi-selectable, but I do not how to have the query limited by the mulit-selections.

I have not found any good examples yet looking through the Microsoft Knowledge Base. Article Microsoft Knowledge Base Article 210203 sort of touches on this, but now how I need to use this.

Does anybody have any good examples or ideas on how to approach this?

TIA!

Reply | Quote

I have a multiselect listbox “lstSelectClients” that lists each of our clients. The user can select multiple clients from this list. There is an “After Update” event on this list box that calls this procedure:
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’
‘for any form where users need to select multiple clients from a multiselect list box. This functions returns a WHERE statement that can be used in queries, report filters, or other procedures.

‘returned string looks like this:
‘ “[CLIENT]= ‘11124’ Or [CLIENT]= ‘11128’ Or [CLIENT]= ‘11134’”
‘
‘
Public Function SelectedClients() As String
‘create filter for selected records
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intStringLength As Integer
Dim strAster As String
Dim strQuote As String
strQuote = Chr(34)
strAster = Chr(42)
‘put your form name here.
Set ctlSource = Forms![frm_Your_Form_Here]!lstSelectClients

‘evaluate number of items on list to make the filter query
‘processes more than one item
For intCurrentRow = 0 To ctlSource.ListCount – 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & “[CLIENT]= ” & “‘” & ctlSource.Column(0, _
intCurrentRow) & “‘” & ” Or ”
End If
Next intCurrentRow

‘remove the last “Or” from the search string
intStringLength = Len(strItems)

If intStringLength = 0 Then
‘If user does not select any clients from the list, the WHERE clause returns a wildcard
strItems = “[CLIENT] Like ” & “‘” & strAster & “‘”
Else
strItems = Left(strItems, (intStringLength – 4))
End If
‘pass value to function
SelectedClients = strItems

‘Reset destination control’s RowSource property.
Set ctlSource = Nothing

End Function
””””””””””””””””””””””””’
In your query, put SelectedClients() as the parameter for selection.
There are probably other ways to skin this cat but this works for me. Hope this helps.

Reply | Quote

AAP2, that looks promising.

Let me play with that in a bit.

Thanks!

Reply | Quote

AAP2, that looks promising.

Let me play with that in a bit.

Thanks!

Reply | Quote

In your form, how are you calling this procedure in the After Update Event?

In a new module I have created a new Public Function by basically copying your code & modifying it slightly:

Public Function SelectedDocType() As String
‘create filter for selected records
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intStringLength As Integer
Dim strAster As String
Dim strQuote As String
strQuote = Chr(34)
strAster = Chr(42)
‘put your form name here.
Set ctlSource = Forms![frm_Reports]!List36

‘evaluate number of items on list to make the filter query
‘processes more than one item
For intCurrentRow = 0 To ctlSource.ListCount – 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & “[Document Type]= ” & “‘” & ctlSource.Column(0, intCurrentRow) & “‘” & ” Or ”
End If
Next intCurrentRow

‘remove the last “Or” from the search string
intStringLength = Len(strItems)

If intStringLength = 0 Then
‘If user does not select any clients from the list, the WHERE clause returns a wildcard
strItems = “[Document Type] Like ” & “‘” & strAster & “‘”
Else
strItems = Left(strItems, (intStringLength – 4))
End If
‘pass value to function
SelectedDocType = strItems

‘Reset destination control’s RowSource property.
Set ctlSource = Nothing

End Function

By having SelectedDocType() as the criteria is not working, so that is why I am asking what you are doing in your AfterUpdate event on the listbox.

Thanks again!

Reply | Quote

There is a button on my form “cmdOpenReport” that does this:

Private Sub cmdOpenReport_Click()
On Error Resume Next

dim mstrRpt as string
mstrRpt = “rptClientInventorySummary”

DoCmd.OpenReport mstrRpt, acViewPreview, , SelectedClients()

End Sub

The report gets its data from the query “qryClientInventory” that selects, among other things, Client Number ([CLIENT]), Client Name, Inventory details…
The public function SelectedClients() returns a WHERE clause used in the DoCmd.OpenReport mstrRpt statement above. The WHERE clause looks like this
“[CLIENT]= ‘11124’ Or [CLIENT]= ‘11128’ Or [CLIENT]= ‘11134’”
SelectedClients() returns data type string.

Reply | Quote

There is a button on my form “cmdOpenReport” that does this:

Private Sub cmdOpenReport_Click()
On Error Resume Next

dim mstrRpt as string
mstrRpt = “rptClientInventorySummary”

DoCmd.OpenReport mstrRpt, acViewPreview, , SelectedClients()

End Sub

The report gets its data from the query “qryClientInventory” that selects, among other things, Client Number ([CLIENT]), Client Name, Inventory details…
The public function SelectedClients() returns a WHERE clause used in the DoCmd.OpenReport mstrRpt statement above. The WHERE clause looks like this
“[CLIENT]= ‘11124’ Or [CLIENT]= ‘11128’ Or [CLIENT]= ‘11134’”
SelectedClients() returns data type string.

Reply | Quote

I suspect, but am not sure, that the problem may be the rowsource of your list box. The rowsource for the listbox on my form has two text fields, [CLIENT], [Client Name] The users selections are put into a text string and the report that is generated is filtered by a text string. If you are trying to build the WHERE clause with something other than text, you need to modify the SelectClients() function slightly to accomodate the data type of your bound field.
Hope this helps.

Reply | Quote

I suspect, but am not sure, that the problem may be the rowsource of your list box. The rowsource for the listbox on my form has two text fields, [CLIENT], [Client Name] The users selections are put into a text string and the report that is generated is filtered by a text string. If you are trying to build the WHERE clause with something other than text, you need to modify the SelectClients() function slightly to accomodate the data type of your bound field.
Hope this helps.

Reply | Quote

In your form, how are you calling this procedure in the After Update Event?

In a new module I have created a new Public Function by basically copying your code & modifying it slightly:

Public Function SelectedDocType() As String
‘create filter for selected records
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intStringLength As Integer
Dim strAster As String
Dim strQuote As String
strQuote = Chr(34)
strAster = Chr(42)
‘put your form name here.
Set ctlSource = Forms![frm_Reports]!List36

‘evaluate number of items on list to make the filter query
‘processes more than one item
For intCurrentRow = 0 To ctlSource.ListCount – 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & “[Document Type]= ” & “‘” & ctlSource.Column(0, intCurrentRow) & “‘” & ” Or ”
End If
Next intCurrentRow

‘remove the last “Or” from the search string
intStringLength = Len(strItems)

If intStringLength = 0 Then
‘If user does not select any clients from the list, the WHERE clause returns a wildcard
strItems = “[Document Type] Like ” & “‘” & strAster & “‘”
Else
strItems = Left(strItems, (intStringLength – 4))
End If
‘pass value to function
SelectedDocType = strItems

‘Reset destination control’s RowSource property.
Set ctlSource = Nothing

End Function

By having SelectedDocType() as the criteria is not working, so that is why I am asking what you are doing in your AfterUpdate event on the listbox.

Thanks again!

Reply | Quote

I have a multiselect listbox “lstSelectClients” that lists each of our clients. The user can select multiple clients from this list. There is an “After Update” event on this list box that calls this procedure:
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””’
‘for any form where users need to select multiple clients from a multiselect list box. This functions returns a WHERE statement that can be used in queries, report filters, or other procedures.

‘returned string looks like this:
‘ “[CLIENT]= ‘11124’ Or [CLIENT]= ‘11128’ Or [CLIENT]= ‘11134’”
‘
‘
Public Function SelectedClients() As String
‘create filter for selected records
Dim ctlSource As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim intStringLength As Integer
Dim strAster As String
Dim strQuote As String
strQuote = Chr(34)
strAster = Chr(42)
‘put your form name here.
Set ctlSource = Forms![frm_Your_Form_Here]!lstSelectClients

‘evaluate number of items on list to make the filter query
‘processes more than one item
For intCurrentRow = 0 To ctlSource.ListCount – 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & “[CLIENT]= ” & “‘” & ctlSource.Column(0, _
intCurrentRow) & “‘” & ” Or ”
End If
Next intCurrentRow

‘remove the last “Or” from the search string
intStringLength = Len(strItems)

If intStringLength = 0 Then
‘If user does not select any clients from the list, the WHERE clause returns a wildcard
strItems = “[CLIENT] Like ” & “‘” & strAster & “‘”
Else
strItems = Left(strItems, (intStringLength – 4))
End If
‘pass value to function
SelectedClients = strItems

‘Reset destination control’s RowSource property.
Set ctlSource = Nothing

End Function
””””””””””””””””””””””””’
In your query, put SelectedClients() as the parameter for selection.
There are probably other ways to skin this cat but this works for me. Hope this helps.

Reply | Quote

I have an Access 2000 database where there is a REPORTS form.

On this form the user can input a date range & has a few other optional criteria selections. After the user makes their selections, there is a list of reports (presented as forms).
The user would then select the report they want by clicking a button. The form for the selected report will then appear. This form has an underlying query that uses the criteria information entered on the Reports form. The reports are presented in forms instead of reports as the user then has the ability to drill into the summary data presented on the form.

Currently 2 of those selections are in Combo Boxes.

In my query I reference these combo boxes using the NZ function so if the user does not select any options, it is not filtered.
Here is an example from my query – Like nz([forms]![frm_Reports]![Combo29],”*”)
This works great. If the user makes no selections, nothing is filtered here. If they make a selection in that combo box, it is filtered on that selection.

What my user really wants to do is to be able to select none, 1 or more codes from the list.

I can change from using a Combo Box to a List Box & have the list box Multi-selectable, but I do not how to have the query limited by the mulit-selections.

I have not found any good examples yet looking through the Microsoft Knowledge Base. Article Microsoft Knowledge Base Article 210203 sort of touches on this, but now how I need to use this.

Does anybody have any good examples or ideas on how to approach this?

TIA!

Reply | Quote

Let me clarify things a bit. The “report” is actually a form, not a report. This form report is a summary report listing several areas of summarized data with the ability to drill into the detail for each section.

On the Reports form, the On CLick action does:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “Frm-Qry_Return Orders – Summary Values by Order Reason 1”
DoCmd.OpenForm stDocName, , , stLinkCriteria

That form has a record source of Qry_Return Orders – Summary Values by Order Reason 1

That query ties together 8 other queries. Most of those queries are either summary queries of other queries or have this filtering applied in those queries.

I am looking at a different post (post 287626) to see if I can incorperate some of what is being discussed there.

Here is the SQL from one of my inital queries:
SELECT [Return Orders].*, [Forms]![Frm_Reports]![FromDate] AS D1, [Forms]![Frm_Reports]![ToDate] AS D2
FROM [Return Orders]
WHERE ((([Return Orders].[SAP Add Date]) Between [forms]![Frm_Reports]![FromDate] And [forms]![Frm_Reports].[ToDate]) AND (([Return Orders].[Item Rejection]) Is Null) AND (([Return Orders].[Direct-Indirect]) Like nz([forms]![frm_Reports]![D-I-M-A],”*”)) AND (([Return Orders].[Document Type]) Like nz([forms]![frm_Reports]![Combo29],”*”)) AND (([Return Orders].[Order Reason]) Like nz([forms]![frm_Reports]![Combo32],”*”)) AND (([Return Orders].[Qty Open])>0));

So what I would like to change somehow is Like nz([forms]![frm_Reports]![Combo29],”*”)
to something along what is being described in the post I mentioned above so when the various queries that need this filter applied are executed, I can use that snip of SQL in those queries instead of generating the SQL by VB.

This would have to take into account if nothing is selected also, so I may need to enclose that SQL within a Like nz( ).

Reply | Quote

Whether this query is in a report or a form, the fact remains that you can’t reference the selected values of a multiselect listbox from a query.

Reply | Quote

Thanks Mark & AAP2 for your input & comments on my task.

Unfortunatly what I think I need can not be met with AAP2s example. (I can take & modify the output to appear in the query, but not be part of the selection criteria)

My users want to be able to use 2 mulit-select lists (at least 2 more will be added).

With the design of some of these summary reports, there are several queries that would need these lists as part of their criteria. (the final summary report ties 7 queries together. Many of those queries are summary queries of other selection queries measuring Order & line item counts, qtys & $ amounts of orders placed during a date range, how many are open that were placed during that date range & how many were closed during that date range, regardless of when placed.)

What I think I need to do is add a macro to the OnClick events for when the user clicks on the button to execute the report that creates or fills a table (or 2) with the selections from each mulit-select list box.

Then in my queries that need the filtering, do a left join to these tables (which could have 0 records if the user does not want to zero in on a few of the 60 possible selections) & then filter as needed.

So, if anybody has or could point me to example code of filling a table from a multi-select list, that would be appreciated!

Also if someone has a better idea, that would be great also.

Reply | Quote

To be honest, I think you are making this far too complicated. In my experience, users often claim that they need to be able to make very detailed selections, but if you make this possible, they hardly know what to do with them.

Anyway, if you want to populate a table with items selected in a multi-select list box, you will need to work with a shared back end with the data tables and individual front ends for each user, otherwise you’ll get into trouble if multiple users start populating tables (you could give the tables unique names in a shared front end., but I fear you’d run into corruption sooner rather than later).

For what it’s worth, here is code that will clear, then populate the MyNum field in a table tblNums (which must already exist) with items selected in a list box lbx1 on a form. It’s implemented as the On Click event procedure of a command button on the form, but that is not essential.

Private Sub cmdSomething_Click()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim varItem As Variant

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
cnn.Execute “DELETE FROM tblNums”, , adCmdText

rst.Open “tblNums”, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

For Each varItem In Me.lbx1.ItemsSelected
rst.AddNew
rst!MyNum = Me.lbx1.ItemData(varItem)
rst.Update
Next varItem

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Thank you Hans for your reply & information.

Unfortunatly this DB does need to have the ability to filter on more then 1 selection as several codes in the list may be related to a more general order type. I am also hoping that this will be a 1 user DB after this is completed. I have created a few DB’s in the past with a single backend DB on a network server & the front end on each user’s PC. Trying to keep everybody up to date can be a real pain when there are changes to the front end. (or when Access corrupts itself!)

As far as using your code, I need to determine what Reference Library I am missing as my Access 2K does not like ADODB.Connection or ADODB.Recordset.

Hopefully this DB will be short lived until something similar can be developed in the data host system instead of extracting that data into Access, but that is a few months away.

John

Reply | Quote

If ADODB.Connection is not recognized, select Tools | References… (in the Visual Basic Editor). Locate and tick the Microsoft ActiveX Data Objects 2.n Library; select the highest version number (2.7 on my PC, but it might be different on yours), then click OK.

Reply | Quote

Thanks again! Saved me a bit of looking, trial & error.

Doing a right join to the table that gets loaded with the items selected & then using “Is Not Null” for that table/field works great.

I am also populating a text box with the values that was selected so I can reference that list on the report form (showing what filters was applied) using AAP2’s code, but modified a bit.

I am a bit brain dead at the moment doing data validation on a diferent project. Hopefully I can clear my head enough to create an IIF statement in my query looking to the form to see if it contains “ALL” (nothing was selected so I want everything) or shows a diferent value, then filter the query. (I can also change that to have nothing if no items are selected in the list box) This text box will be hidden.

So I am thinking of using something along the lines of iif(forms!form_name!field_name = “ALL”, do nothing, is not null) in my criteria statement in the querie(s) under the right joined table.

Back to data validation 🙁

Reply | Quote

If this is a question about a different project, I would suggest starting a new thread. This makes it easier for others to follow the discussion. Thanks.

Reply | Quote

This is the same project. Guess I should not ramble on when I am tired. :-)~

So, yes I am able to get your code to delete current contents from a table & add the selected items from the list box to that table.

The last step I need to do is use that data to limit a query, or not limit it if no records are in the table.

As I am generating a list of what was selected & displaying that in the reports selection form, I was thinking of looking to that field in the criteria of the query & using an IIF statement to apply the filter.

I am thinking of adding the new table that will either be blank or have a few records of what was selected from the multi-select list box as a right join (the type where the arrow points to the 2nd table).

In the criteria for the 1 field on that new table I was thinking of using an IIF to look at the reports form to see if any filtering was applied. If not, then do no filtering. If something was selected, then basically use IS NOT NULL as the criteria.

Reply | Quote

OK, I think I have it!

By using the code suggested by Hans, I have a 1 field table that gets cleared & updated with the selections made in my combo-list box.
By Modifying the code suggested by AAP2 I have a hidden field that gets populated with a list of the selected items. If none are chosed, the text box displays “No Filters Applied”. I also reference this field on my report to show what filters were applied.

In my query I have the new 1 field table joined to my main table via a right join (where the join arrow points to the new table from the main table).

I then created a calculated field in the query:
UseMeDocType: IIf([forms]![frm_Reports]![Text38]=”No Filter Applied”,”Y”,IIf([Return Orders].[Document Type]=[TblSelDocType].[DocType],”Y”,”N”))

In the Criteria for this field I have a Y.

So if no items were selected from the combo-list box, all records are returned. If 1 or more items are selected, the query is limited to those document types that were selected.

Seems to work just fine.

Now to apply this same style of code to also look at a 2nd mulit-list box.

Reply | Quote

OK, I think I have it!

By using the code suggested by Hans, I have a 1 field table that gets cleared & updated with the selections made in my combo-list box.
By Modifying the code suggested by AAP2 I have a hidden field that gets populated with a list of the selected items. If none are chosed, the text box displays “No Filters Applied”. I also reference this field on my report to show what filters were applied.

In my query I have the new 1 field table joined to my main table via a right join (where the join arrow points to the new table from the main table).

I then created a calculated field in the query:
UseMeDocType: IIf([forms]![frm_Reports]![Text38]=”No Filter Applied”,”Y”,IIf([Return Orders].[Document Type]=[TblSelDocType].[DocType],”Y”,”N”))

In the Criteria for this field I have a Y.

So if no items were selected from the combo-list box, all records are returned. If 1 or more items are selected, the query is limited to those document types that were selected.

Seems to work just fine.

Now to apply this same style of code to also look at a 2nd mulit-list box.

Reply | Quote

This is the same project. Guess I should not ramble on when I am tired. :-)~

So, yes I am able to get your code to delete current contents from a table & add the selected items from the list box to that table.

The last step I need to do is use that data to limit a query, or not limit it if no records are in the table.

As I am generating a list of what was selected & displaying that in the reports selection form, I was thinking of looking to that field in the criteria of the query & using an IIF statement to apply the filter.

I am thinking of adding the new table that will either be blank or have a few records of what was selected from the multi-select list box as a right join (the type where the arrow points to the 2nd table).

In the criteria for the 1 field on that new table I was thinking of using an IIF to look at the reports form to see if any filtering was applied. If not, then do no filtering. If something was selected, then basically use IS NOT NULL as the criteria.

Reply | Quote

If this is a question about a different project, I would suggest starting a new thread. This makes it easier for others to follow the discussion. Thanks.

Reply | Quote

Thanks again! Saved me a bit of looking, trial & error.

Doing a right join to the table that gets loaded with the items selected & then using “Is Not Null” for that table/field works great.

I am also populating a text box with the values that was selected so I can reference that list on the report form (showing what filters was applied) using AAP2’s code, but modified a bit.

I am a bit brain dead at the moment doing data validation on a diferent project. Hopefully I can clear my head enough to create an IIF statement in my query looking to the form to see if it contains “ALL” (nothing was selected so I want everything) or shows a diferent value, then filter the query. (I can also change that to have nothing if no items are selected in the list box) This text box will be hidden.

So I am thinking of using something along the lines of iif(forms!form_name!field_name = “ALL”, do nothing, is not null) in my criteria statement in the querie(s) under the right joined table.

Back to data validation 🙁

Reply | Quote

If ADODB.Connection is not recognized, select Tools | References… (in the Visual Basic Editor). Locate and tick the Microsoft ActiveX Data Objects 2.n Library; select the highest version number (2.7 on my PC, but it might be different on yours), then click OK.

Reply | Quote

To be honest, I think you are making this far too complicated. In my experience, users often claim that they need to be able to make very detailed selections, but if you make this possible, they hardly know what to do with them.

Anyway, if you want to populate a table with items selected in a multi-select list box, you will need to work with a shared back end with the data tables and individual front ends for each user, otherwise you’ll get into trouble if multiple users start populating tables (you could give the tables unique names in a shared front end., but I fear you’d run into corruption sooner rather than later).

For what it’s worth, here is code that will clear, then populate the MyNum field in a table tblNums (which must already exist) with items selected in a list box lbx1 on a form. It’s implemented as the On Click event procedure of a command button on the form, but that is not essential.

Private Sub cmdSomething_Click()
Dim cnn As ADODB.Connection
Dim rst As New ADODB.Recordset
Dim varItem As Variant

On Error GoTo ErrHandler

Set cnn = CurrentProject.Connection
cnn.Execute “DELETE FROM tblNums”, , adCmdText

rst.Open “tblNums”, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

For Each varItem In Me.lbx1.ItemsSelected
rst.AddNew
rst!MyNum = Me.lbx1.ItemData(varItem)
rst.Update
Next varItem

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Thanks Mark & AAP2 for your input & comments on my task.

Unfortunatly what I think I need can not be met with AAP2s example. (I can take & modify the output to appear in the query, but not be part of the selection criteria)

My users want to be able to use 2 mulit-select lists (at least 2 more will be added).

With the design of some of these summary reports, there are several queries that would need these lists as part of their criteria. (the final summary report ties 7 queries together. Many of those queries are summary queries of other selection queries measuring Order & line item counts, qtys & $ amounts of orders placed during a date range, how many are open that were placed during that date range & how many were closed during that date range, regardless of when placed.)

What I think I need to do is add a macro to the OnClick events for when the user clicks on the button to execute the report that creates or fills a table (or 2) with the selections from each mulit-select list box.

Then in my queries that need the filtering, do a left join to these tables (which could have 0 records if the user does not want to zero in on a few of the 60 possible selections) & then filter as needed.

So, if anybody has or could point me to example code of filling a table from a multi-select list, that would be appreciated!

Also if someone has a better idea, that would be great also.

Reply | Quote

Whether this query is in a report or a form, the fact remains that you can’t reference the selected values of a multiselect listbox from a query.

Reply | Quote

Let me clarify things a bit. The “report” is actually a form, not a report. This form report is a summary report listing several areas of summarized data with the ability to drill into the detail for each section.

On the Reports form, the On CLick action does:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “Frm-Qry_Return Orders – Summary Values by Order Reason 1”
DoCmd.OpenForm stDocName, , , stLinkCriteria

That form has a record source of Qry_Return Orders – Summary Values by Order Reason 1

That query ties together 8 other queries. Most of those queries are either summary queries of other queries or have this filtering applied in those queries.

I am looking at a different post (post 287626) to see if I can incorperate some of what is being discussed there.

Here is the SQL from one of my inital queries:
SELECT [Return Orders].*, [Forms]![Frm_Reports]![FromDate] AS D1, [Forms]![Frm_Reports]![ToDate] AS D2
FROM [Return Orders]
WHERE ((([Return Orders].[SAP Add Date]) Between [forms]![Frm_Reports]![FromDate] And [forms]![Frm_Reports].[ToDate]) AND (([Return Orders].[Item Rejection]) Is Null) AND (([Return Orders].[Direct-Indirect]) Like nz([forms]![frm_Reports]![D-I-M-A],”*”)) AND (([Return Orders].[Document Type]) Like nz([forms]![frm_Reports]![Combo29],”*”)) AND (([Return Orders].[Order Reason]) Like nz([forms]![frm_Reports]![Combo32],”*”)) AND (([Return Orders].[Qty Open])>0));

So what I would like to change somehow is Like nz([forms]![frm_Reports]![Combo29],”*”)
to something along what is being described in the post I mentioned above so when the various queries that need this filter applied are executed, I can use that snip of SQL in those queries instead of generating the SQL by VB.

This would have to take into account if nothing is selected also, so I may need to enclose that SQL within a Like nz( ).

Reply | Quote