SQL syntax - Help please!

[mikehoot]

I am using the following code to concantenate one field from all records from the recordset, spaced with a comma.

Dim db As Database
Dim rs As Recordset
Dim strName As String
Dim intCnt As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryDrugItemString&quot


intCnt = 0

Do Until rs.EOF
intCnt = intCnt + 1


If intCnt = 1 Then 'See if this is first rec
strName = rs!StringCont
Else 'If not first rec add a comma
strName = strName & ", " & rs!StringCont
End If

rs.MoveNext

Loop

Forms!frmDrugItemSUBFORM!StringCont = strName

End Sub


I would like to restrict these records by amemding the SELECT query for the recordset to include WHERE and AND parameters (taking values from current form) but I am not sure of the syntax to use.

This is what I would like (roughly!)

WHERE qryDrugItemSUBFORM.DrugMainID = Forms!FormName!ControlName AND qryDrugItemsSUBFORM.AdminRouteID = Forms!FormName!ControlName

If anyone can help out it will be much appreciated. B-)

 

[THoey]

Ithink you are confusing which side to mention the form on:

SELECT *
FROM MyTable
WHERE MyTable.DrugMainID = Forms!FormName!SUBFORM!ControlName.Value AND
MyTable.AdminRouteID = Forms!FormName!SUBFORM!ControlName.Value

I think this is right, but am not a wizard at it. Terry M. Hoey

 

[Sawatzky]

dear mikehood,

make a normal query, then in criteria right-click and select 'build... ' then you have a window pooping up where you can reference to everything you want.

after that you change to sql-view of the query, copy and paste into code , and your syntax will be fine.

regards astrid