Access Average DateDiff with a range to a Textbox

Question

I have an unbound textbox that I would like to get the average datediff for a set range of dates. I have a table (tblOrders), with three fields (Material, StartDate, EndDate). I would like the text box to return the average date difference for a material (say '123456789') that has an EndDate between '01/01/2013' and '05/31/2013'. I am stumped on how to achieve. Thanks for the assist everyone.

Answer 1

The form contains general information about materials and it's primarily DLookup, DSum and DAvg for the textboxes on the form.

.......

Would it be wise to calculate the Date Difference into the initial table, then do the Average for the Row Source?

  Do you have an actual record source for the form other than the individual text box's DLookup, DSum and DAvg?  If so then post.

Answer 2

Karl,

The form contains general information about materials and it's primarily DLookup, DSum and DAvg for the textboxes on the form. It's a tool that the sales team uses to quote times to the customer on how long it will take to standard items. The textbox in question, I need to link to the source table to get the average time (DateDiff) between when we start and when we end a items manufacturing. The SQL statement I used to get the result is...

SELECT tblOrders.Material, Avg(DateDiff("d", StartDate, EndDate)) AS Average

FROM tblOrders

WHERE  Material='123456789' AND tblOrders.EndDate BETWEEN  #01/01/2013# AND #05/31/2013#

It returns the correct value, but I need that value to be in the textbox on the form, running when they change the combobox (cmbMaterial). I know how to set the RowSource, just not how to return the Average DateDiff for a specific date range and material.

I apologize, I deal with Excel 99.9% of the day, what is the record source of the SQL form? This was a blank form I created that is mainly producing totals for date ranges via VBA because the data comes from multiple company sources.

Would it be wise to calculate the Date Difference into the initial table, then do the Average for the Row Source?

Answer 3

What else will be displayed on the form?   Post the SQL of the record source of your form.

Answer 4

Karl,

I absolutely appreciate the response. I can get the answer from an SQL query, but I need the answer to be passed into a text box on a userform. I haven't been able to figure out how to pass a SQL result to a textbox, minus passing the answer to a table and doing a dlookup off a table. Is there a way to get the same result, but by passing it to the control source of the textbox? Regrettably upper management has decided it must be presented to a team in the userform as the entire team requested this specific field. Thank you again for trying to assist!

Answer 5

Try this --

SELECT tblOrders.Material, Avg(DateDiff("d", StartDate, EndDate)) AS AvgDateDiff

FROM tblOrders

WHERE  tblOrders.EndDate Between  #01/01/2013# and #05/31/2013#

GROUP BY tblOrders.Material;