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 ("")
Answer
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