dlookup with groupby in vba query

[benjamin.weizmann]

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]

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]

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]

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