dlookup with groupby in vba query (1 Viewer)

benjamin.weizmann · Oct 10, 2017

Hi friends,

I'm begging to solve it:
how to write the part of the dlookup correctly

thanks you!!
Ben

Me.Graph38.RowSource = "SELECT graph.[numoutt], Sum(graph.[quantity_pro]) AS SumOfquantity_pro, DLookup('yellow_range', 'machine', 'IDnum=' & [graph]![machinenum] & '') AS g " _
& " FROM graph" _
& " GROUP BY graph.[numoutt], graph.ID,graph.[machinenum], g" _
& " HAVING (((graph.[machinenum])=" & Me.List2.Value & "));"

Minty · Oct 10, 2017

Don't use a d Lookup in a query - use a join to get the table in.
Much more efficient.

Check the links in my signature for the syntax. you are mixing quotes types.

arnelgp · Oct 10, 2017

Me.Graph38.RowSource = _
"SELECT graph.[numoutt]," &
"Sum(graph.quantity_pro) As SumOfquantity_pro," & _
"machine.yellow_range As g_ " & _
"FROM graph LEFT JOIN machine ON machine.machinenum = graph.IDNum " & _
"GROUP BY " & _
"graph.[numoutt],machine.yellow_range " & _
"HAVING graph.machinenum = " & Me.list.Value

***
change the HAVING clause if graph.machinenum is String:

...
...
...
"HAVING graph.machinenum = " & Chr(34) Me.List.Value & Chr(34)

benjamin.weizmann · Oct 10, 2017

arnelgp said:
Me.Graph38.RowSource = _
"SELECT graph.[numoutt]," &
"Sum(graph.quantity_pro) As SumOfquantity_pro," & _
"machine.yellow_range As g_ " & _
"FROM graph LEFT JOIN machine ON machine.machinenum = graph.IDNum " & _
"GROUP BY " & _
"graph.[numoutt],machine.yellow_range " & _
"HAVING graph.machinenum = " & Me.list.Value

***
change the HAVING clause if graph.machinenum is String:

...
...
...
"HAVING graph.machinenum = " & Chr(34) Me.List.Value & Chr(34)

thanks u so much

dlookup with groupby in vba query (1 Viewer)

benjamin.weizmann

Registered User.

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

benjamin.weizmann

Registered User.

Similar threads

Users who are viewing this thread