DAvg is not working in a Text Box Control Control Source linked to an Existing Query

[Gary_R]

Hi all, I am “re-new” to Access. The last time that I worked with ACCESS was in 1999 or so. I am hacking my way through to create a nice pricing tool for our company. So any advice or directions that you can provide is greatly appreciated. Thank you in advance!!!



I guess my questions; Is it proper to use DAvg to group records and average multiple fields within that group? If yes, can I use DAvg on an existing Query?

Let me explain:
I have two text boxes on a form. One text box is a DLookup that uses a Table and works perfect. I uploaded a screenshot and pasted the Control Source statement below. The second text box is a DAvg that uses an existing Query [Sales_Price_Query] but results is #Name?. Notice below and attached that I followed the same format that I used for a DLookup statement.

I am NOT familiar with the DAvg. I am trying to use the DAvg to group on the SKU number and create an Average. Notice in the screenshot [Sales_Price_Query] results attached, some SKUs have multiple records. The reason is that we receive multiple shipments (Lots) of the same SKU.

DAvg NOT WORKING: DAvg statement in a Text Box named “Avg InWhse” and the result is #Name?
=DAvg("[InWhse]","[Sales_Price_Query]","[SKU]=" & [Forms]![Pricing_Decision_Form]![SKU])

DLookup Working Perfect: DLookup statement in a Text Box named Description. Works correctly.
=DLookUp("[Description]","[Product_Info]","[SKU]=" & Forms![Pricing_Decision_Form]!SKU)

[ranman256]

make a query that does the AVG using the sku on the form in the query, you can see the correct Avg in the query.
then make the text box show it via: DLOOKUP()

[CJ_London]

doesn't look like you are using the same criteria in the dlookup that is not working