(VBA) For loop and DLookup (MS ACCESS)

Question

I have a query named TimeQuery. I want MsgBox will show all [start_hour] values where [id]=1 then 2 etc. ... I have a problem. When i will use:

a = DLookup("Hour([start_hour])", "TimeQuery", "[id]=1")

it works good, but when use [id]=counter it doesn't show it. I have a MsgBox after that For Loop too and when [id]=counter it doesn't show that MsgBox too. What's wrong?

For counter = 1 To 3
Dim a As Variant
a = DLookup("Hour([start_hour])", "TimeQuery", "[id]=counter")
MsgBox (counter)
Next
Debug.Print ("")

score 2 · Accepted Answer · answered Dec 24 '17 at 15:14

You need to concatenate your variable to a string if you want to use this, like so:

For counter = 1 To 3
    Dim a As Variant

    a = DLookup("Hour([start_hour])", "TimeQuery", "[id]=" & counter)
    MsgBox (counter)

Next

Debug.Print ("")

However, if you want to do this properly, use recordsets

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset ("SELECT Hour([start_hour]) As hr FROM TimeQuery WHERE [id] <=3 ORDER BY ID ASC")
Do While Not rs.EOF
   MsgBox rs!hr
   rs.MoveNext
Loop

(VBA) For loop and DLookup (MS ACCESS)

1 Answers1