multi-select list

[Karen]

The code I have below uses values from a multi-select
field and puts it in a text box. It works until that
point. If I select a single item, the query works. The
query does not work when I select more than one items in
the multi-select list.

I have searched and found that I could pass the whole
select statement from the code...I have been able to
create the code but am not sure how I would launch the
query.....


Private Sub List14_Exit(Cancel As Integer)
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!DateRangeDialog
Set ctl = frm!List14

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & Chr$(34)
& "," & """"
Next varItem

strSQL = Left$(strSQL, (Len(strSQL) - 3))

Me!Text19 = strSQL
End Sub

 

[Rick Brandt]

The code I have below uses values from a multi-select
field and puts it in a text box. It works until that
point. If I select a single item, the query works. The
query does not work when I select more than one items in
the multi-select list.

What is the SQL of the query?

 

[Guest]

SELECT [Data 04].[Item Code], [Data 04].[Item
Description], [Data 04].[Account Code], [Data 04].[Account
Description], [Data 04].[Pickup Date], [Data 04].[Pickup
User], [Data 04].[Issue Quantity], [Data 04].[Client Price]
FROM [Data 04]
WHERE ((([Data 04].[Account Code]) In ([Forms]!
[DateRangeDialog]![Text30])) AND ((([Data 04].[Pickup
Date]) Between frm![BeginningDate] And frm![EndDate])) AND
([Data 04].[Account Code] IN ('

the rest comes from the multi-select selection.

 

[Rick Brandt]

SELECT [Data 04].[Item Code], [Data 04].[Item
Description], [Data 04].[Account Code], [Data 04].[Account
Description], [Data 04].[Pickup Date], [Data 04].[Pickup
User], [Data 04].[Issue Quantity], [Data 04].[Client Price]
FROM [Data 04]
WHERE ((([Data 04].[Account Code]) In ([Forms]!
[DateRangeDialog]![Text30])) AND ((([Data 04].[Pickup
Date]) Between frm![BeginningDate] And frm![EndDate])) AND
([Data 04].[Account Code] IN ('

the rest comes from the multi-select selection.

Sorry, but "the rest" is what is failing and it is that which we need to
see. Post the entire SQL statement after the "in list" has been added.

 

[Karen]

Rick

The select statement is as follows and looks good. Maybe
I am not calling it right???

SELECT [Data 04].[Item Code], [Data 04].[Item
Description], [Data 04].[Account Code], [Data 04].[Account
Description], [Data 04].[Pickup Date], [Data 04].[Pickup
User], [Data 04].[Issue Quantity], [Data 04].[Client Price]
FROM [Data 04]WHERE ([Data 04].[Pickup Date] Between (frm!
[BeginningDate]) And (frm![EndDate])) AND ([Data 04].
[Account Code] IN ('5-S','5-2');

where 5-S and 5-2 are the account codes selected in the
multi-select list.

 

[Guest]

Rick

I was missing a bracket at the end of the SQL which I
added and am able to run it if I put it in the query
manually. But, if I use the statement...

DoCmd.OpenQuery "strSQL"

I get an error stating that the object "strSQL" cannot be
found. What am I doing or not doing???

Thanks
Karen

 

[Rick Brandt]

Rick

I was missing a bracket at the end of the SQL which I
added and am able to run it if I put it in the query
manually. But, if I use the statement...

DoCmd.OpenQuery "strSQL"

I get an error stating that the object "strSQL" cannot be
found. What am I doing or not doing???

DoCmd.OpenQuery expects the name of a saved query, not a SQL String.

 

[karen]

Going back to my main question....I am trying to pass the
multiple selections from a multi-select list so I could
run a query...

I have created a loop to collect the text...but, when I
pass the text to my query, it does not work.

How do I do it? Please!!!