Hi guys,
i am trying to pass the an average function value to a listbox, and keep getting an error on the ad part.
Can someone give me a second set of eyes.
dim db as new DatabaseRecord
dim sql as String
sql = “select avg(totalhours) as totalhoursaverage from t_projectinfo where size = ‘small’ and complex = ‘simple’”
dim rs as RecordSet = APP.SBA.SQLSelect(sql)
if rs= nil then
msgbox “No data”
uom.listbox1.addrow
uom.listbox1.cell(1,1) = “0”
else
if rs.recordcount>0 then
listbox1.addrow
Listbox1.cell(1,1)= totalhoursaverage // here is the error through debug
[quote=293250:@Cory Hefner]Hi guys,
i am trying to pass the an average function value to a listbox, and keep getting an error on the ad part.
Can someone give me a second set of eyes.
dim db as new DatabaseRecord
dim sql as String
sql = “select avg(totalhours) as totalhoursaverage from t_projectinfo where size = ‘small’ and complex = ‘simple’”
dim rs as RecordSet = APP.SBA.SQLSelect(sql)
if rs= nil then
msgbox “No data”
uom.listbox1.addrow
uom.listbox1.cell(1,1) = “0”
else
if rs.recordcount>0 then
listbox1.addrow
Listbox1.cell(1,1)= totalhoursaverage <<<<<<<<<<<<< // here is the error through debug
end if
end if
rs.close[/quote]
that line should be Listbox1.cell(1,1)= rs.field(“totalhoursaverage”).doublevalue
EDIT : whoops !
cant shove a double directly into a listbox
that line should be
Listbox1.cell(1,1)= format(rs.field(“totalhoursaverage”).doublevalue, “#.00”)
or
Listbox1.cell(1,1)= str(rs.field(“totalhoursaverage”).doublevalue)
Plus your record set will never be NIL (assuming there are no database errors)
An average of ZERO records is NULL, but the recordset WILL have at least one record even if the source table is empty
if you want to be sure to NOT return a NULL value, then do this
select IFNULL(avg(totalhours),0) as totalhoursaverage ..... etc.. etc...
anytime your SELECT statement returns an aggregate function you will get at least ONE record (SUM, MIN, MAX, AVG etc)
Listbox1.cell(1,1)= Format(rs.field("totalhoursaverage").doublevalue, "#.00") will work even better. Also ensure you have a list box with at least 2 rows & 2 columns or you’ll get an out of bounds error.