placing a variable in CreateQueryDef

[Mcgrco]

I have a table with Containing Controllers names and Costs for the year . I want my code to firstly extract a distinct list of controllers and for each controller query the main table with a filter on the controllers name and create a new query. I can get the first bit to work but when i try to create the queries. It passes the variables difined name instead of the value to the query. In this case BACName instead of the actual name. Here is my code.


Can anyone please help


Function QueryBacLists()
Dim db As Database
Set db = CurrentDb

Dim rs, rs1, rs2 As Recordset
Dim strSql, strSql1, strSql2, BacName As String
Dim fld As Field
Dim Qdf As QueryDef


strSql = " SELECT DISTINCT Name.name FROM Name;" 'Get the List of names


Set rs = db.OpenRecordset(strSql)
rs.MoveFirst
Do While Not rs.EOF
For Each fld In rs.Fields
BacName = fld.Value
strSql1 = " SELECT Name.ISIN, Name.name FROM Name WHERE Name.name = chr(34) & BacName & Chr(34);"

Set Qdf = db.CreateQueryDef("DownLoad for " & BacName, strSql1)



Next
rs.MoveNext
Loop
Set db = Nothing
End Function

 

[Fornatian]

strSql1 = " SELECT Name.ISIN, Name.name FROM Name WHERE Name.name = chr(34) & BacName & Chr(34);"

Should be:

strSql1 = " SELECT Name.ISIN, Name.name FROM Name WHERE Name.name = '" & BacName & ";"

You forget to split you quotes so it interprets the variable as a value rather than including the name.

Ian

 

[Mcgrco]

Thanks