Exclude one field from output 2

[alexisb]

I created a query def that uses an existing query but selects records based on data selected in a multi-select box. The logic works fine. However, I just realized that the field being used as the selection criteria should not appear in the output so "select *" can't be used.

strSQL = "select * from qryExport where listname in (" & Me.txtFilter & ")"
Set qdf = dbs.CreateQueryDef("TempExportQry", strSQL)
DoCmd.OpenQuery "TempExportQry", acViewNormal
dbs.QueryDefs.Delete "TempExportQry"
Set qdf = Nothing
Set dbs = Nothing

I tried pasting the SQL code from View SQL of the query so I could just omit the one field from the select but it is very complicated with many quote marks and it isn't working.

Is there a way to exclude one field from the output but still use it as selection criteria?

Thanks,
Alexis

 

[Remou]

You are using an existing query, so why not list the fields, skipping the chosen field. Roughly:
[tt]For Each fld in rs.Fields
If fld.Name <> "TheOneChosen" Then
fldlist=fld.Name & ", "
End If
Next[/tt]

 

[JerryKlmns]

Remou's

should be

Dim fld as DAO.Field 
dim fldlist As String
Set rs = CurrentDB.OpenRecordset("select * from qryExport where listname='KOYKOY'", dbOpenForwardOnly)
For Each fld in rs.Fields
   If fld.Name <> "listname" Then fldlist = [b]fldlist[/b] & ", " & fld.Name
Next
rs.Close
Set rs = Nothing
fldlist = Mid(fldlist, 2)  & " " 

strSQL = "SELECT" & fldlist & "FROM qryExport WHERE  listname in ('" & Me.txtFilter & "')"

 

[alexisb]

Thanks to both of you. This will work perfectly. I hadn't thought of the logic this way.

Alexis