ListBox Data to QueryDef

[LB79]

Hello,

Im having a problem sending multiple items from a lstbox to a query.
The query is QueryDef, and the SQL is below.
Criteria is the data from the lstbox.
I've put a msgbox it which shows my lstbox (Criteria) giving me 48 or 49 Or 50 (which is what I expect).
When I run the query, it creates as normal with Ref = 48, but the 49 and 50 are being added as column headers and <>false as criteria.

Can anyone see what im doing wrong?

SQL = "SELECT tbl_Table.Ref FROM tbl_Table HAVING (((tbl_Table.Ref)=" & Criteria & "));"

Thanks

 

[JANR]

Making sence of listbox, check out this link: http://www.fontstuff.com/access/acctut11.htm

JR

 

[LB79]

Thanks for the link - thats were I was looking already... but I get a problem.

 

[DCrake]

SQL = "SELECT Ref FROM tbl_Table WHERE Ref In(" & Criteria & ");"

Criteria = "48","49","50"

SQL = "SELECT Ref FROM tbl_Table WHERE Ref In("48","49","50")

 

[LB79]

Thanks DCrake.
If i include " or in this case ' as its SQl in VBA, it debugs with 3270 property not found. Where as if I just have the numbers it makes the query with the odd columns...

 

[DCrake]

I could answer your question more clearly if I saw your actual SQL statement

 

[LB79]

Thanks DCrake... this is the SQL currently with no '. This transfers to QRY but with odd columns. If I add ' then it debugs.

Dim DB As DAO.Database
Dim QDF As DAO.QueryDef
Dim Item As Variant
Dim SAICriteria As String
Dim SAISQL As String
Set DB = CurrentDb()
Set QDF = DB.QueryDefs("qry_REF_GenericExport")
For Each Item In Me!SAI_lst4.ItemsSelected
SAICriteria = SAICriteria & " or " & Me!SAI_lst4.ItemData(Item)
Next Item
SAICriteria = Right(SAICriteria, Len(SAICriteria) - 3)
SAISQL = "SELECT tbl_REF_TradePort.Port, tbl_REF_TradePort.Ref FROM tbl_REF_TradePort WHERE (((tbl_REF_TradePort.Ref)=" & SAICriteria & "));"
MsgBox SAICriteria
QDF.SQL = SAISQL
DoCmd.OpenQuery "qry_REF_GenericExport"
Set DB = Nothing
Set QDF = Nothing
End Sub

 

[DCrake]

When using "Or" you need to say when x = 1 OR x = 2 Or x = 3. The purpose of using the In() function is to combat this. The In() statement is doing that for you. It is an implied OR so there is no need to change it.

 

[LB79]

Thanks DCrake.
Im still having problems with this though
Now I get Run Time 3075 - Syntax Error, missing operator (in my WHERE clause)
My latest code is below... Im getting myself confused with this.

Private Sub SAI_cmd0_Click()
Dim DB As DAO.Database
Dim QDF As DAO.QueryDef
Dim Item As Variant
Dim SAICriteria As String
Dim SAISQL As String
Set DB = CurrentDb()
Set QDF = DB.QueryDefs("qry_REF_GenericExport")
For Each Item In Me!SAI_lst4.ItemsSelected
SAICriteria = SAICriteria & ",'" & Me!SAI_lst4.ItemData(Item) & "'"
Next Item
If Len(SAICriteria) = 0 Then
MsgBox "ATMT did not find any ports.", vbExclamation, "ATMT"
Exit Sub
End If
SAICriteria = Right(SAICriteria, Len(SAICriteria) - 1)
SAISQL = "SELECT tbl_REF_TradePort.Port, tbl_REF_TradePort.Ref"
SAISQL = SAISQL & " FROM tbl_REF_TradePort"
SAISQL = SAISQL & " WHERE tbl_REF_TradePort.Ref = & In(" & SAICriteria & ");"
MsgBox SAICriteria
QDF.SQL = SAISQL
DoCmd.OpenQuery "qry_REF_GenericExport"
Set DB = Nothing
Set QDF = Nothing
Exit Sub
End Sub

 

[DCrake]

SAICriteria = Right(SAICriteria, Len(SAICriteria) - 1)
SAISQL = "SELECT tbl_REF_TradePort.Port, tbl_REF_TradePort.Ref"
SAISQL = SAISQL & " FROM tbl_REF_TradePort"
SAISQL = SAISQL & " WHERE tbl_REF_TradePort.Ref = & In(" & SAICriteria & ");"
MsgBox SAICriteria
QDF.SQL = SAISQL
DoCmd.OpenQuery "qry_REF_GenericExport"

What does SAISQL look like?
What does SAICriteria look like?

You cannot use Openquery on a select query. It needs to be an Action type query (Insert,Delete,Update,etc) NOT Select.