getting NULL value when trying to use DLOOKUP in VBA code on an aggregate query

[klnlsu]

I have created an aggregate query to calculate the mode (or most occurrences) of a discount_pct value in a selection of data. I originally had my criteria set in my query in which it compared the OrderHeaderID to a textbox value on an open form. My SQL code for this query is as follows:

SELECT TOP 1 tblOEOrderDetail.discount_pct
FROM tblOEOrderDetail
GROUP BY tblOEOrderDetail.discount_pct, tblOEOrderDetail.OrderHeaderID
HAVING (((tblOEOrderDetail.OrderHeaderID)=[forms]![frmOrderHeaderEntry]![OrderHeaderIDTxt]))
ORDER BY Count(tblOEOrderDetail.discount_pct) DESC;

I then stored a value in a recordset field by using this query in a dlookup statement in my VBA code as follows:

rsOEORDHDR("discount_pct") = DLookup("discount_pct", "qryModeOfDiscountPct")

This worked.

I now don't have a form field to base my criteria value on, so I removed the criteria from the query itself. The SQL code for my revised query is as follows:

SELECT TOP 1 tblOEOrderDetail.discount_pct, tblOEOrderDetail.OrderHeaderID
FROM tblOEOrderDetail
GROUP BY tblOEOrderDetail.discount_pct, tblOEOrderDetail.OrderHeaderID
ORDER BY Count(tblOEOrderDetail.discount_pct) DESC;

I then tried to pass the criteria from a variable (ProcessOrderID, which is numeric) in my VBA subroutine to the query. My revised dlookup statement is as follows:

rsOEORDHDR("discount_pct") = DLookup("discount_pct", "qryModeOfDiscountPct", "OrderHeaderID = " & ProcessOrderID)

I am getting a NULL value in my recordset field. I can set the criteria in the query to the value that the variable field should be passing to it and I get the correct query value, so I think the problem is my dlookup statement.

Can I pass criteria to an aggregate query this way? If so, what am I doing wrong?

Thanks.

[aytee111]

Have you done a debug thru this code and hovered over ProcessOrderID to confirm that it has the correct value?

[klnlsu]

Have you done a debug thru this code and hovered over ProcessOrderID to confirm that it has the correct value?

Yes, I have.

[aytee111]

It all looks ok. Try changing the dlookup to read direct from the table, see if that works.
DLookup("Max(discount_pct)","tblOEOrderDetail","Or derHeaderID = " & ProcessOrderID)

[Micron]

SELECT TOP 1 tblOEOrderDetail.discount_pct, tblOEOrderDetail.OrderHeaderID
FROM tblOEOrderDetail
GROUP BY tblOEOrderDetail.discount_pct, tblOEOrderDetail.OrderHeaderID
ORDER BY Count(tblOEOrderDetail.discount_pct) DESC;

If you dump this into a new query, does it return any records? If not, that should explain it. But, it looks like your criteria was one thing on a certain field
[forms]![frmOrderHeaderEntry]![OrderHeaderIDTxt] but now you are trying to pass a number to a different field
"OrderHeaderID = " & ProcessOrderID

[klnlsu]

I think I have figured it out...Apparently Access runs the query first and then applies the filter I passed to it. I had to remove the SELECT TOP 1 from my query and then call it with DFIRST instead of DLOOKUP.

Updated SQL for query:

SELECT tblOEOrderDetail.OrderHeaderID, tblOEOrderDetail.discount_pct, Count(tblOEOrderDetail.discount_pct) AS CountOfdiscount_pct
FROM tblOEOrderDetail
GROUP BY tblOEOrderDetail.OrderHeaderID, tblOEOrderDetail.discount_pct
ORDER BY Count(tblOEOrderDetail.discount_pct) DESC;

Updated call from VBA:

rsOEORDHDR("discount_pct") = DFirst("discount_pct", "qryModeOfDiscountPct", "OrderHeaderID = " & ProcessOrderID)

This seems to be working.

Thanks for y'all's help!