using values from a simple listbox to create a SQL IN Statement

G

Guest

I have a simple multi select box which I am trying to read the values from to
create an IN statement for a query. It seems that my loop produces the data I
was hoping for.
For example if I have A,B,C,D in my list box
the data field strIN comes out looking like 'A','B','C','D' (Code is Below)

What I can not get to work is putting the IN statement in my query correctly
I have tried to read the field strIN. Something to the form of

in ([forms]![FormA]![strIN]) but that did not work !!

I tried to populate a textbox with the values from strIN and tried to read
the textbox values like below. No sucess.

In ([forms]![WorkCenterandNHAKit]![Text17])

below is the code I use to get the values.

Any Suggestons?


Dim counter As Integer
' create the IN string by looping thru the listbox
' where "lstLender" is the name of the list box on the form

For i = 0 To List0.ListCount - 1
counter = (List0.ListCount - 1)
If (i) = counter Then
strIN = strIN & "'" & List0.Column(0, i) & "'"
Else
strIN = strIN & "'" & List0.Column(0, i) & "',"
End If

Next i

' call and run the SQL statement
DoCmd.OpenQuery "testing"


SQL statement

SELECT [Bradley BOM's].CONTRACT, [Bradley BOM's].END_ITEM, [Bradley
BOM's].NHA_PCC, [Bradley BOM's].NHA_SRCPLT, [Bradley BOM's].ITEM_NO, [Bradley
BOM's].PART, [Bradley BOM's].DESCR, [Bradley BOM's].PCC, [Bradley
BOM's].SOURCEPLT, [Bradley BOM's].QTY_PER, [Bradley BOM's].QPV, [Bradley
BOM's].ISSUE_UOM, [Bradley BOM's].PUR_UOM, [Bradley BOM's].ECN_FROM, [Bradley
BOM's].EFF_FROM, [Bradley BOM's].EFF_THRU, [Bradley BOM's].ECN_THRU, [Bradley
BOM's].WORKCENTER, [Bradley BOM's].OPER, [Bradley BOM's].LTOFFSET, [Bradley
BOM's].POSTDEDUCT, [Bradley BOM's].RTG_PRIOR, [Bradley BOM's].PNCATEGORY,
[Bradley BOM's].FROM_ASSY, [Bradley BOM's].EFFFROM_OV, [Bradley
BOM's].EFFTHRU_OV, [Bradley BOM's].THRU_ASSY, [Bradley BOM's].PLANNER,
[Bradley BOM's].NHA
FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].NHA) In ([forms]![WorkCenterandNHAKit]![Text17]))
AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND ((IIf([forms]![form1]![list72]
Is Null Or [forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley BOM's].END_ITEM;



In the SQL statement have tried putting
In ([forms]![WorkCenterandNHAKit]![Text17]) and this
([forms]![FormA]![strIN])
neither worked. I am guessing I am trying to read the values wrong into the
IN statement incorrectly.

Thanks in advance
 
G

Graham Mandeno

Hi Dave

When you include a reference to some external entity, such as
[forms]![WorkCenterandNHAKit]![Text17], in a SQL statement, it creates an
implicit parameter for that query. A parameter can only be a single value,
not a list of values, so the best you can end up with is something that
looks like this:
WHERE NHA In ("'A','B','C','D'")

Note that the IN list contains only a single string, so this is equivalent
to:
WHERE NHA = "'A','B','C','D'"

The best way to achieve what you want is to remove the entire WHERE clause
(and therefore all the parameters) from your query and build a form (either
datasheet or continuous/tabular) on your query. Then, instead of opening
the query, you open your form.

Now, unlike OpenQuery, the OpenForm method allows you to pass a
WhereCondition string in the 4th argument. So, before you open the form,
you can execute some code to construct a valid WHERE string.

This will also have the advantage that you can avoid messy stuff in your
WHERE clause like:
((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1)

Also, it seems to me that your strIN list will always include every item in
your listbox, whether or not it is selected. I'm sure this is not what you
want!

This code will give you just the selected items:

Dim strIN As String, vItem As Variant
For Each vItem In List0.ItemsSelected
strIN = strIN & "'" & List0.Column(0, vItem) & "',"
Next vItem
' remove the last comma
strIN = Left(strIN, Len(strIN) - 1)

Now, it's easy to construct the rest of your WHERE clause:

strWHERE = "NHA in (" & strIN & ")"
If not IsNull(List72) then
strWHERE = strWHERE & " AND End_Item=" & List72
end if
If not IsNull(List74) then
strWHERE = strWHERE & " AND Contract=" & List74
end if

Then open the form:

DoCmd.OpenForm "Bradley BOMs Form", , , strWHERE

You might need to tweak this by adding quotes around values if you are using
text fields - for example:

strWHERE = strWHERE & " AND End_Item='" & List72 & "'"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


dave said:
I have a simple multi select box which I am trying to read the values from
to
create an IN statement for a query. It seems that my loop produces the
data I
was hoping for.
For example if I have A,B,C,D in my list box
the data field strIN comes out looking like 'A','B','C','D' (Code is
Below)

What I can not get to work is putting the IN statement in my query
correctly
I have tried to read the field strIN. Something to the form of

in ([forms]![FormA]![strIN]) but that did not work !!

I tried to populate a textbox with the values from strIN and tried to read
the textbox values like below. No sucess.

In ([forms]![WorkCenterandNHAKit]![Text17])

below is the code I use to get the values.

Any Suggestons?


Dim counter As Integer
' create the IN string by looping thru the listbox
' where "lstLender" is the name of the list box on the form

For i = 0 To List0.ListCount - 1
counter = (List0.ListCount - 1)
If (i) = counter Then
strIN = strIN & "'" & List0.Column(0, i) & "'"
Else
strIN = strIN & "'" & List0.Column(0, i) & "',"
End If

Next i

' call and run the SQL statement
DoCmd.OpenQuery "testing"


SQL statement

SELECT [Bradley BOM's].CONTRACT, [Bradley BOM's].END_ITEM, [Bradley
BOM's].NHA_PCC, [Bradley BOM's].NHA_SRCPLT, [Bradley BOM's].ITEM_NO,
[Bradley
BOM's].PART, [Bradley BOM's].DESCR, [Bradley BOM's].PCC, [Bradley
BOM's].SOURCEPLT, [Bradley BOM's].QTY_PER, [Bradley BOM's].QPV, [Bradley
BOM's].ISSUE_UOM, [Bradley BOM's].PUR_UOM, [Bradley BOM's].ECN_FROM,
[Bradley
BOM's].EFF_FROM, [Bradley BOM's].EFF_THRU, [Bradley BOM's].ECN_THRU,
[Bradley
BOM's].WORKCENTER, [Bradley BOM's].OPER, [Bradley BOM's].LTOFFSET,
[Bradley
BOM's].POSTDEDUCT, [Bradley BOM's].RTG_PRIOR, [Bradley BOM's].PNCATEGORY,
[Bradley BOM's].FROM_ASSY, [Bradley BOM's].EFFFROM_OV, [Bradley
BOM's].EFFTHRU_OV, [Bradley BOM's].THRU_ASSY, [Bradley BOM's].PLANNER,
[Bradley BOM's].NHA
FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].NHA) In ([forms]![WorkCenterandNHAKit]![Text17]))
AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND
((IIf([forms]![form1]![list72]
Is Null Or [forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley
BOM's].END_ITEM;



In the SQL statement have tried putting
In ([forms]![WorkCenterandNHAKit]![Text17]) and this
([forms]![FormA]![strIN])
neither worked. I am guessing I am trying to read the values wrong into
the
IN statement incorrectly.

Thanks in advance
Click to expand...
 
G

Guest

Graham,

Thanks for the reply. You will have to forgive me here because I am
learning on the fly. I see what you were saying about opening up a form.
The code I actaully pasted is the second part of a Union Query. When I open
up the datasheet form is there somehow I can combine the results into a
datasheet on the form? Can I actually open the form using a Union Query?
Would you know where I could find sample code on how I would go about
combining the two queries into one result sets?

Thanks again for the response!

Graham Mandeno said:
Hi Dave

When you include a reference to some external entity, such as
[forms]![WorkCenterandNHAKit]![Text17], in a SQL statement, it creates an
implicit parameter for that query. A parameter can only be a single value,
not a list of values, so the best you can end up with is something that
looks like this:
WHERE NHA In ("'A','B','C','D'")

Note that the IN list contains only a single string, so this is equivalent
to:
WHERE NHA = "'A','B','C','D'"

The best way to achieve what you want is to remove the entire WHERE clause
(and therefore all the parameters) from your query and build a form (either
datasheet or continuous/tabular) on your query. Then, instead of opening
the query, you open your form.

Now, unlike OpenQuery, the OpenForm method allows you to pass a
WhereCondition string in the 4th argument. So, before you open the form,
you can execute some code to construct a valid WHERE string.

This will also have the advantage that you can avoid messy stuff in your
WHERE clause like:
((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1)

Also, it seems to me that your strIN list will always include every item in
your listbox, whether or not it is selected. I'm sure this is not what you
want!

This code will give you just the selected items:

Dim strIN As String, vItem As Variant
For Each vItem In List0.ItemsSelected
strIN = strIN & "'" & List0.Column(0, vItem) & "',"
Next vItem
' remove the last comma
strIN = Left(strIN, Len(strIN) - 1)

Now, it's easy to construct the rest of your WHERE clause:

strWHERE = "NHA in (" & strIN & ")"
If not IsNull(List72) then
strWHERE = strWHERE & " AND End_Item=" & List72
end if
If not IsNull(List74) then
strWHERE = strWHERE & " AND Contract=" & List74
end if

Then open the form:

DoCmd.OpenForm "Bradley BOMs Form", , , strWHERE

You might need to tweak this by adding quotes around values if you are using
text fields - for example:

strWHERE = strWHERE & " AND End_Item='" & List72 & "'"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


dave said:
I have a simple multi select box which I am trying to read the values from
to
create an IN statement for a query. It seems that my loop produces the
data I
was hoping for.
For example if I have A,B,C,D in my list box
the data field strIN comes out looking like 'A','B','C','D' (Code is
Below)

What I can not get to work is putting the IN statement in my query
correctly
I have tried to read the field strIN. Something to the form of

in ([forms]![FormA]![strIN]) but that did not work !!

I tried to populate a textbox with the values from strIN and tried to read
the textbox values like below. No sucess.

In ([forms]![WorkCenterandNHAKit]![Text17])

below is the code I use to get the values.

Any Suggestons?


Dim counter As Integer
' create the IN string by looping thru the listbox
' where "lstLender" is the name of the list box on the form

For i = 0 To List0.ListCount - 1
counter = (List0.ListCount - 1)
If (i) = counter Then
strIN = strIN & "'" & List0.Column(0, i) & "'"
Else
strIN = strIN & "'" & List0.Column(0, i) & "',"
End If

Next i

' call and run the SQL statement
DoCmd.OpenQuery "testing"


SQL statement

SELECT [Bradley BOM's].CONTRACT, [Bradley BOM's].END_ITEM, [Bradley
BOM's].NHA_PCC, [Bradley BOM's].NHA_SRCPLT, [Bradley BOM's].ITEM_NO,
[Bradley
BOM's].PART, [Bradley BOM's].DESCR, [Bradley BOM's].PCC, [Bradley
BOM's].SOURCEPLT, [Bradley BOM's].QTY_PER, [Bradley BOM's].QPV, [Bradley
BOM's].ISSUE_UOM, [Bradley BOM's].PUR_UOM, [Bradley BOM's].ECN_FROM,
[Bradley
BOM's].EFF_FROM, [Bradley BOM's].EFF_THRU, [Bradley BOM's].ECN_THRU,
[Bradley
BOM's].WORKCENTER, [Bradley BOM's].OPER, [Bradley BOM's].LTOFFSET,
[Bradley
BOM's].POSTDEDUCT, [Bradley BOM's].RTG_PRIOR, [Bradley BOM's].PNCATEGORY,
[Bradley BOM's].FROM_ASSY, [Bradley BOM's].EFFFROM_OV, [Bradley
BOM's].EFFTHRU_OV, [Bradley BOM's].THRU_ASSY, [Bradley BOM's].PLANNER,
[Bradley BOM's].NHA
FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].NHA) In ([forms]![WorkCenterandNHAKit]![Text17]))
AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND
((IIf([forms]![form1]![list72]
Is Null Or [forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley
BOM's].END_ITEM;



In the SQL statement have tried putting
In ([forms]![WorkCenterandNHAKit]![Text17]) and this
([forms]![FormA]![strIN])
neither worked. I am guessing I am trying to read the values wrong into
the
IN statement incorrectly.

Thanks in advance
Click to expand...
Click to expand...
 
G

Graham Mandeno

Hi Dave

Yes, you can certainly base a form on a UNION query - just set the form's
RecordSource to be the name of the query.

There is no special code required to do this.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

dave said:
Graham,

Thanks for the reply. You will have to forgive me here because I am
learning on the fly. I see what you were saying about opening up a form.
The code I actaully pasted is the second part of a Union Query. When I
open
up the datasheet form is there somehow I can combine the results into a
datasheet on the form? Can I actually open the form using a Union Query?
Would you know where I could find sample code on how I would go about
combining the two queries into one result sets?

Thanks again for the response!

Graham Mandeno said:
Hi Dave

When you include a reference to some external entity, such as
[forms]![WorkCenterandNHAKit]![Text17], in a SQL statement, it creates an
implicit parameter for that query. A parameter can only be a single
value,
not a list of values, so the best you can end up with is something that
looks like this:
WHERE NHA In ("'A','B','C','D'")

Note that the IN list contains only a single string, so this is
equivalent
to:
WHERE NHA = "'A','B','C','D'"

The best way to achieve what you want is to remove the entire WHERE
clause
(and therefore all the parameters) from your query and build a form
(either
datasheet or continuous/tabular) on your query. Then, instead of opening
the query, you open your form.

Now, unlike OpenQuery, the OpenForm method allows you to pass a
WhereCondition string in the 4th argument. So, before you open the form,
you can execute some code to construct a valid WHERE string.

This will also have the advantage that you can avoid messy stuff in your
WHERE clause like:
((IIf([forms]![form1]![list72] Is Null Or
[forms]![form1]![list72]=[End_Item],1,0))=1)

Also, it seems to me that your strIN list will always include every item
in
your listbox, whether or not it is selected. I'm sure this is not what
you
want!

This code will give you just the selected items:

Dim strIN As String, vItem As Variant
For Each vItem In List0.ItemsSelected
strIN = strIN & "'" & List0.Column(0, vItem) & "',"
Next vItem
' remove the last comma
strIN = Left(strIN, Len(strIN) - 1)

Now, it's easy to construct the rest of your WHERE clause:

strWHERE = "NHA in (" & strIN & ")"
If not IsNull(List72) then
strWHERE = strWHERE & " AND End_Item=" & List72
end if
If not IsNull(List74) then
strWHERE = strWHERE & " AND Contract=" & List74
end if

Then open the form:

DoCmd.OpenForm "Bradley BOMs Form", , , strWHERE

You might need to tweak this by adding quotes around values if you are
using
text fields - for example:

strWHERE = strWHERE & " AND End_Item='" & List72 & "'"
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


dave said:
I have a simple multi select box which I am trying to read the values
from
to
create an IN statement for a query. It seems that my loop produces the
data I
was hoping for.
For example if I have A,B,C,D in my list box
the data field strIN comes out looking like 'A','B','C','D' (Code is
Below)

What I can not get to work is putting the IN statement in my query
correctly
I have tried to read the field strIN. Something to the form of

in ([forms]![FormA]![strIN]) but that did not work !!

I tried to populate a textbox with the values from strIN and tried to
read
the textbox values like below. No sucess.

In ([forms]![WorkCenterandNHAKit]![Text17])

below is the code I use to get the values.

Any Suggestons?


Dim counter As Integer
' create the IN string by looping thru the listbox
' where "lstLender" is the name of the list box on the form

For i = 0 To List0.ListCount - 1
counter = (List0.ListCount - 1)
If (i) = counter Then
strIN = strIN & "'" & List0.Column(0, i) & "'"
Else
strIN = strIN & "'" & List0.Column(0, i) & "',"
End If

Next i

' call and run the SQL statement
DoCmd.OpenQuery "testing"


SQL statement

SELECT [Bradley BOM's].CONTRACT, [Bradley BOM's].END_ITEM, [Bradley
BOM's].NHA_PCC, [Bradley BOM's].NHA_SRCPLT, [Bradley BOM's].ITEM_NO,
[Bradley
BOM's].PART, [Bradley BOM's].DESCR, [Bradley BOM's].PCC, [Bradley
BOM's].SOURCEPLT, [Bradley BOM's].QTY_PER, [Bradley BOM's].QPV,
[Bradley
BOM's].ISSUE_UOM, [Bradley BOM's].PUR_UOM, [Bradley BOM's].ECN_FROM,
[Bradley
BOM's].EFF_FROM, [Bradley BOM's].EFF_THRU, [Bradley BOM's].ECN_THRU,
[Bradley
BOM's].WORKCENTER, [Bradley BOM's].OPER, [Bradley BOM's].LTOFFSET,
[Bradley
BOM's].POSTDEDUCT, [Bradley BOM's].RTG_PRIOR, [Bradley
BOM's].PNCATEGORY,
[Bradley BOM's].FROM_ASSY, [Bradley BOM's].EFFFROM_OV, [Bradley
BOM's].EFFTHRU_OV, [Bradley BOM's].THRU_ASSY, [Bradley BOM's].PLANNER,
[Bradley BOM's].NHA
FROM [Bradley BOM's]
WHERE ((([Bradley BOM's].NHA) In
([forms]![WorkCenterandNHAKit]![Text17]))
AND (([Bradley BOM's].TOP_BGEXPN) Is Null) AND
((IIf([forms]![form1]![list72]
Is Null Or [forms]![form1]![list72]=[End_Item],1,0))=1) AND
((IIf([forms]![form1]![list74] Is Null Or
[forms]![form1]![list74]=[Contract],1,0))=1))
ORDER BY [Bradley BOM's].PART, [Bradley BOM's].NHA, [Bradley
BOM's].END_ITEM;



In the SQL statement have tried putting
In ([forms]![WorkCenterandNHAKit]![Text17]) and this
([forms]![FormA]![strIN])
neither worked. I am guessing I am trying to read the values wrong
into
the
IN statement incorrectly.

Thanks in advance
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

Pass a control.value to SQL statement?? 4
How to read values from a table? 3
refer to a particular column in a listbox in a query 4
how to run create and run a query in vba 1
Using a Form to create a Report Pt. 2 2
Using SQL Query Code in Form Module to Return Record Count 2
Values from 4 Listboxes to a Query/Report 1
Assigning value to a variable using SQL statement 2

Top