Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of (VBA) For loop and DLookup (MS ACCESS) without wasting too much if your time.
The question is published on by Tutorial Guruji team.
The question is published on by Tutorial Guruji team.
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
We are here to answer your question about (VBA) For loop and DLookup (MS ACCESS) - If you find the proper solution, please don't forgot to share this with your team members.