Help with This Code "SF" <ssamnang[ at ]yahoo.com> 21.07.2006 03:07:09 Hi, I am traying to build a criteria string from a table that contain selected ID (eg [ID]=1 or [ID]= 58 or [ID] =74 and so on). I have code below that seem never work Dim Stg As String Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim I As Long Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tmpSelected", dbOpenDynaset) With rst Do While Not .EOF Stg = "[ID] = " & .Fields(0) & " Or [ID] = " .MoveNext Loop End With Debug.Print Stg Could somebody advice SF

Re: Help with This Code "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 21.07.2006 04:31:37 Just a few small changes...changes are commented. Dim Stg As String Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim I As Long Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tmpSelected", dbOpenDynaset) With rst 'Add next line (not absolutely necessary, but good practice Stg = "" Do While Not .EOF 'Change next line to Stg = Stg & " Or ([ID] = " & .Fields(0) & ")" .MoveNext Loop End With 'And add this (strips off the leading " Or ", leaving you with a correctly formatted string) If Stg <> "" Then Stg = Mid$(Stg, 5) Debug.Print Stg Alternately, you can use an "In" statement, which isn't much different to build, but will execute faster than a whole bunch of Or's: Dim Stg As String Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim I As Long Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tmpSelected", dbOpenDynaset) With rst 'Add next line (not absolutely necessary, but good practice Stg = "" Do While Not .EOF 'Change next line to Stg = Stg & "," & .Fields(0) .MoveNext Loop End With 'And add this (strips off the leading " Or ", leaving you with a correctly formatted string) If Stg <> "" Then Stg = "In(" & Mid$(Stg, 2) & ")" Debug.Print Stg

Re: Help with This Code "SF" <ssamnang[ at ]yahoo.com> 21.07.2006 04:54:08 It work!. Thank you. Sf "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message news:%23rU6S6HrGHA.4100[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > Just a few small changes...changes are commented. > > Dim Stg As String > Dim dbs As DAO.Database > Dim rst As DAO.Recordset > Dim I As Long > > Set dbs = CurrentDb > Set rst = dbs.OpenRecordset("tmpSelected", dbOpenDynaset) > With rst > 'Add next line (not absolutely necessary, but good practice > Stg = "" > Do While Not .EOF > 'Change next line to > Stg = Stg & " Or ([ID] = " & .Fields(0) & ")" > .MoveNext > Loop > End With > 'And add this (strips off the leading " Or ", leaving you with a correctly > formatted string) > If Stg <> "" Then Stg = Mid$(Stg, 5) > Debug.Print Stg > > > Alternately, you can use an "In" statement, which isn't much different to > build, but will execute faster than a whole bunch of Or's: > > Dim Stg As String > Dim dbs As DAO.Database > Dim rst As DAO.Recordset > Dim I As Long > > Set dbs = CurrentDb > Set rst = dbs.OpenRecordset("tmpSelected", dbOpenDynaset) > With rst > 'Add next line (not absolutely necessary, but good practice > Stg = "" > Do While Not .EOF > 'Change next line to > Stg = Stg & "," & .Fields(0) > .MoveNext > Loop > End With > 'And add this (strips off the leading " Or ", leaving you with a correctly > formatted string) > If Stg <> "" Then Stg = "In(" & Mid$(Stg, 2) & ")" > Debug.Print Stg >