Pass multiple records from one table into a SQL statment

Question

I am trying to take many values (which always change) from a table in access and put them inside a limit of a SQL statement.

I have Dlookup() but as we know it is pulling only 1 value.

How can I loop this to pull all the value?

related code:

Dim Value as string
Value = DLookup("Values", "value")
sSQL = ......... WHERE AL.LIMIT '" & value & "'" &_

score 0 · Answer 1 · answered Aug 06 '13 at 20:20

I have no idea what you're trying to do, your question is about as clear as mud. However, I'm gonna take a stab at it.

Try something like this:

Dim db as Database
Dim rec as Recordset

Db = CurrentDB
'This assumes you're pulling one specific record from a table with many records
rec = db.OpenRecordSet("Select * from YourTableName WHERE Something = SomethingElse")

Do While rec.EOF = False
  MyFirstValue = rec(0)
  MySecondValue = rec(1)
  MyThirdValue = rec(2)
  ...ect
Loop

sSQL = "Select * from Whatever WHERE FirstParam = '" & MyFirstValue & "' AND SecondParam = '" & MySecondValue & "' AND ThirdParam = '" & MyThirdValue & "'"

That is gonna need a lot of editing, but if I understood correctly, it should be a good starting point.

Up-Arrows and checkmarks are a nice way to say, "Thank You!". ;o) — Johnny Bones, Aug 07 '13 at 13:51
The only difference is I am pulling all records from one table. — user2276367, Aug 07 '13 at 15:25

Pass multiple records from one table into a SQL statment

1 Answers1