Refering to data in subform

[dtutton]

Im using a textbox to do a calculation based on on a field in a subform.

The subform is in dataview format displaying several rows of data.

Currently I use:

=[Commodity Quotations].[Form]![price]

as the control

This addresses the price in the first row. How Can I address the second row or do I need to set up a query.

David

 

[ghubbell]

Hi David,
Access can't recognize one from the other...well not exactly...it should always display the records value where your subform cursor sits. If you can live with this then you're ok...otherwise...off to the Queries! Gord
ghubbell@total.net

 

[dtutton]

Thanks Gord,

Looks like a beer from the fridge and a long evening..

David

 

[ghubbell]

Achtung! Go to sleep and start afresh tomorrow! Most things look better in the morning. (Ok just leave that one alone.)

Have a nice weekend David! ;-) Gord
ghubbell@total.net

 

[dtutton]

Gord,

I think your right.

I set up the query fine. Im trying to get six month average of average quotes and the query does exactly that.

However...Im using a Aggregate function in the query to get the average and when I try to bring this back to the form with a dlookup I get an error (flickering). I note from Help! that dlookup cannot return aggregate functions.

A nite of dreams of dlookups...

David

 

[ghubbell]

8:50 PM here (shhhhh) Dave is a sleep as its Saturday 3:50 AM in Germany... Dreaming dreams of Dlookup functions....


Morning Dave!
How about a textbox (TxtAvgPrice) in the form footer of your subform (or wherever) with a control source like this:

=Avg([Price]) averaging all the "prices" displayed in the subform?

Need this number somewhere else? "TxtTest" control source:

=15+[txtavgprice]

Would something like this do what you need to do? Finish your coffee first! Gord
ghubbell@total.net

 

[dtutton]

Gord,

Thanks for suggestion.

What I have is various quotes on prices over the last 6 years and Im wanting to average the last over the previous 6 months relative to todays date. Therefore this may be the average of one quote or maybe 15 quotes.

Therefore I dont think, the avg on the subform will work as it averages a specific number of prices rather than period.

What I have is a Query set up as follows:

Code - Grouped and ascending
6mth: IIf(

>Date()-187,1,0) - Grouped and descending
Quote - Count
Price - Ave

where Quote is the date.

This produces exactly what I want as a Query with the two rows per Code, the firts of which has the average over the 6 month period.

Where I have difficulty is getting the AveofPrice back into the form. Im trying:

=DLookUp("Price","6mths","Code='" &

 & "'")

where 6mths is the Query name

All I get is a flickering error and I think that that dlookup cannot return aggregate functions.

Any other ideas.

Have a good weekend (whats left)

David

 

[BoxHead]

David,

I'm not much with SubForms, but would Gord's TxtAveragePrice work for you with a filter applied?

Unsure exactly how far you want to take this, but it seems you would be able to modify the filter to show other periods of time over the last six years as well. Since you have the data for such a long period, you should be able to look for trends in price quotes that could make short-term projections more meaningful.

Eg: Looking at 60 or 90-day period averages over the last six years could tell you if your vendors' prices are fluctuating with inflation, the stock market, construction starts, the season, etc. Does Vendor ABC show a significant price increase every 24 months?

Could a SelectDateRange control on your form be set to a 'last-six-months' default and be the criteria for your subform filter? Changing the date range would reset the filter and update your average price textbox.

I really haven't worked with subforms enough to know if this is helpful or not. If the queries will work for you, that's great. However, I would urge you to consider creating queries to show more of the data you have.

Good luck.

Boxhead

 

[ghubbell]

Good Morning oops Good Afternoon David!
I think I know where the problem is:
Run your query on its own. For this to work it should only return 1 record. If you have more than 1 record showing Dlookup will get all upset trying to figure which one you want. You may have to strip out some unnecessary fields or switch to Unique Values/Records (testing your results as you go.) Let me know... Gord
ghubbell@total.net

 

[dtutton]

Gord,

I modified the query to give a unique value but problem with dlookup remains. I assume it must be that dlookup cannot refer to an aggregate function.

I like the combination of your and Boxhead's ideas and will get on that now.

Thanks David

 

[ghubbell]

Dave! Adjust your query! I have a similar one running here but what I'm doing instead is setting criteria under the "Code" column in the query like: Forms!MyForm.MyField

My Dlookup on the form is =DLookUp("AvgOfUnitPrice","Query1&quot
This is built in the Northwinds Sample Db and returns the value correctly. I think this would work for you! Gord
ghubbell@total.net

 

[dtutton]

Gord,

Im was one step back. Your and Boxhead's ideas give a good solution and I have put in via an option box. Works fine. Thanks.

Now Ive tried your other approach - perfect !

I had given up to early because I read in help for Dlookup "In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function" and I thought Avg was an aggregate function.

I guess the answer is "trust the forum rather that the documentation".

Thanks again,

David

 

[ghubbell]

Glad to see your sucess! (That was a little devil!) Take care and come back soon! Gord
ghubbell@total.net