Complicated sql string?

S

S Jackson

I have a dialog form that allows to a user to select a "region" criteria
from a drop down list and then print a report based on the "region"
selected.

I want to add another criteria selection in addition to the "region"
selection. This critieria is called "action."

The form has three List boxes:
RegListBx (user selects the "region" criteria)
RptGroupListBx (user selects a report group)
RptListBx (unbound list box that is populated based on the selection user
makes in the RptGroupListBx).

The code to print the report for the "regions" selected looks like this:
Dim stLinkCriteria As String
Dim varItem As Variant

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

How do I add in that I want to also include the critieria selected in my new
combo box, "ActionListBx" to the stLinkCriteria string? Can this be done?

TIA
S. Jackson
 
D

Douglas J. Steele

Dim stLinkCriteria As String
Dim varItem As Variant

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"
stLinkCriteria = stLinkCriteria & " AND [tblCaseInfo]![Action] IN ("
For Each varItem In Me.ActionListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.ActionListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

This assumes that the actions in ActionListBx are numbers. If they're text,
try:

stLinkCriteria = stLinkCriteria & " AND [tblCaseInfo]![Action] IN ("
For Each varItem In Me.ActionListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Chr$(34) & _
Me.ActionListBx.ItemData(varItem) & _
Chr$(34) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

How do I add in that I want to also include the critieria selected in my new
combo box, "ActionListBx" to the stLinkCriteria string? Can this be done?
Click to expand...


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



S Jackson said:
I have a dialog form that allows to a user to select a "region" criteria
from a drop down list and then print a report based on the "region"
selected.

I want to add another criteria selection in addition to the "region"
selection. This critieria is called "action."

The form has three List boxes:
RegListBx (user selects the "region" criteria)
RptGroupListBx (user selects a report group)
RptListBx (unbound list box that is populated based on the selection user
makes in the RptGroupListBx).

The code to print the report for the "regions" selected looks like this:
Dim stLinkCriteria As String
Dim varItem As Variant

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

How do I add in that I want to also include the critieria selected in my new
combo box, "ActionListBx" to the stLinkCriteria string? Can this be done?

TIA
S. Jackson
Click to expand...
 
S

S Jackson

Thanks so much! It worked (btw, the list box contains text items).

However, one more catch: What is the user does not select any items in the
ActionListBx? I get an error if the user does not. I tried writing this
into the code before the code you wrote for me:

If Me.ActionListBx.ListCount > 0 Then
{I inserted the code you gave me here}

But that didn't work. Any ideas?

TIA!
S. Jackson

Douglas J. Steele said:
Dim stLinkCriteria As String
Dim varItem As Variant

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"
stLinkCriteria = stLinkCriteria & " AND [tblCaseInfo]![Action] IN ("
For Each varItem In Me.ActionListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.ActionListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

This assumes that the actions in ActionListBx are numbers. If they're text,
try:

stLinkCriteria = stLinkCriteria & " AND [tblCaseInfo]![Action] IN ("
For Each varItem In Me.ActionListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Chr$(34) & _
Me.ActionListBx.ItemData(varItem) & _
Chr$(34) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

How do I add in that I want to also include the critieria selected in my new
combo box, "ActionListBx" to the stLinkCriteria string? Can this be done?
Click to expand...


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



S Jackson said:
I have a dialog form that allows to a user to select a "region" criteria
from a drop down list and then print a report based on the "region"
selected.

I want to add another criteria selection in addition to the "region"
selection. This critieria is called "action."

The form has three List boxes:
RegListBx (user selects the "region" criteria)
RptGroupListBx (user selects a report group)
RptListBx (unbound list box that is populated based on the selection user
makes in the RptGroupListBx).

The code to print the report for the "regions" selected looks like this:
Dim stLinkCriteria As String
Dim varItem As Variant

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

How do I add in that I want to also include the critieria selected in my new
combo box, "ActionListBx" to the stLinkCriteria string? Can this be done?

TIA
S. Jackson
Click to expand...
Click to expand...
 
S

S Jackson

It is

If Me.ActionListBx.ItemsSelected.Count > 0 Then

Thanks again for your help!!!!

S. Jackson

S Jackson said:
Thanks so much! It worked (btw, the list box contains text items).

However, one more catch: What is the user does not select any items in the
ActionListBx? I get an error if the user does not. I tried writing this
into the code before the code you wrote for me:

If Me.ActionListBx.ListCount > 0 Then
{I inserted the code you gave me here}

But that didn't work. Any ideas?

TIA!
S. Jackson

Douglas J. Steele said:
Dim stLinkCriteria As String
Dim varItem As Variant

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"
stLinkCriteria = stLinkCriteria & " AND [tblCaseInfo]![Action] IN ("
For Each varItem In Me.ActionListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.ActionListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

This assumes that the actions in ActionListBx are numbers. If they're text,
try:

stLinkCriteria = stLinkCriteria & " AND [tblCaseInfo]![Action] IN ("
For Each varItem In Me.ActionListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Chr$(34) & _
Me.ActionListBx.ItemData(varItem) & _
Chr$(34) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

How do I add in that I want to also include the critieria selected in
Click to expand...
my
new
combo box, "ActionListBx" to the stLinkCriteria string? Can this be done?
Click to expand...


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



S Jackson said:
I have a dialog form that allows to a user to select a "region" criteria
from a drop down list and then print a report based on the "region"
selected.

I want to add another criteria selection in addition to the "region"
selection. This critieria is called "action."

The form has three List boxes:
RegListBx (user selects the "region" criteria)
RptGroupListBx (user selects a report group)
RptListBx (unbound list box that is populated based on the selection user
makes in the RptGroupListBx).

The code to print the report for the "regions" selected looks like this:
Dim stLinkCriteria As String
Dim varItem As Variant

stLinkCriteria = "[tblCaseInfo]![Region] IN ("
For Each varItem In Me.RegListBx.ItemsSelected
stLinkCriteria = stLinkCriteria & _
Me.RegListBx.ItemData(varItem) & ","
Next varItem
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria) - 1) & ")"

How do I add in that I want to also include the critieria selected in
Click to expand...
my
new
combo box, "ActionListBx" to the stLinkCriteria string? Can this be done?

TIA
S. Jackson
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

List Box - stLinkCriteria 2
stlinkcriteria syntax. multiple fields and wildcards 5
Brackets in A Query Criteria From a Combo Box/Text 2
Open form "Client Details" by clicking "CientName" txtBox 3
filter on combo box possible? 1
Invalid Character 6
Combo Box Selection 1
Combo Box Menu Selection Problem 0

Top