trying to pass and avg function to a listbox

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

Listbox1.cell(1,1), does your ListBox have 2 Rows and 2 Columns ?

if not use
Listbox1.cell(0,0)
or
Listbox1.cell(ListBox1.LastIndex,0)

[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)

or str with a format string

there no XOJO variable called “totalhoursaverage”

its a field in the recordset so you need to access it through the reocrdset

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.

thanks, stating the double value worked.