>Hi. I have a form with a grid. The grid shows products. I want to add a column to show the average days in the warehouse. My warehouse data contains a date so I want the SQL to be:
>select cpartno, avg(date()-indate) as avgdays from whcuinvbin group by 1 having cpartno = THISFORM.THISGRID.COLUMN1.txtPart.Value
>
You could use a UDF as your controlsource, but be warned, this could be slow if you have a lot of data. Put (Thisform.CalcAvg(cPartNo)) in the controlsource for the textbox (you need to have the outer parentheses, and create a form method called CalcAvg as follows:
function CalcAvg
lparameter tcPartNo
if type('tcPartNo') <> 'C'
messagebox('Invalid parameter')
return
endif
select cpartno, avg(date()-indate) as avgdays ;
from whcuinvbin ;
where cPartno = tcPartNo
return AvgDays
Since you're only pulling records for the one part number, you don't need a group by clause.