Help with This Code

[SF]

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

 

[Robert Morley]

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

 

[SF]

It work!. Thank you.

Sf