OpenOffice.org Forum :: [SOLVED] How to sum up the value return by function??

[Home] [FAQ] [Search] [Memberlist] [Usergroups] [Register]

[Profile] [Log in to check your private messages] [Log in]

[SOLVED] How to sum up the value return by function??

OOoForum.org Forum Index -> OpenOffice.org Macros and API

View previous topic :: View next topic

Author

Message

T3rr3nc3
General User

Joined: 17 Sep 2009
Posts: 39
Location: Malaysia

Posted: Sun Aug 01, 2010 7:23 pm Post subject: [SOLVED] How to sum up the value return by function??

Dear all,

I've wriiten some function to get value from database and return it in the spreadsheet.

Code:

Function getData(freq,startdate,starttime,col,rptid,rpttablename)
Dim query As String
Dim startdt As Date
Dim enddt As Date
Dim colid
Dim dbConn As Object
Dim oresult As Object
Dim isodate As String

colid = getColId(col,rptid)
startdt = startdate + TimeValue(starttime)

Select Case freq
   Case "D":
         enddt = startdt + TimeValue("01:00:00")

         'format: 01:00:00 hh:mimi:ss
         sqldateformat = "convert(varchar(8), datetime,108)"

Case "M":
   isodate = CDateToIso(DateAdd("d",1,startdt))
   enddt = CDateFromIso(isoDate)

'format : 20080512 ccyymmdd
         sqldateformat = "convert(varchar(8), datetime,112)"

case "Y":
   isodate = CDateToIso(DateAdd("m",1,startdt))
   enddt = CDateFromIso(isoDate)
         sqldateformat = "convert(varchar(8), datetime,112)"
End Select

query = "Select currvalue, " + sqldateformat + " from " + rpttablename + " where colid = " + colid + " and datetime >= '" + startdt + "'" + " and datetime < '" + enddt + "'"
getData() = getValue(query)
End Function

I invoke this self defined function like invoking those built in formula function. When i try to sum up those return value, the result is zero. I know this is because i sum up the formula not the value.

I sum up the value by using =SUM(Value(A1);Value(A2);Value(A3);....)
Is there any way i can sum up the value in easier way instead of using this long method?

Last edited by T3rr3nc3 on Mon Aug 02, 2010 6:23 pm; edited 1 time in total

B Marcelly
Super User

Joined: 12 May 2004
Posts: 1414
Location: France

Posted: Sun Aug 01, 2010 10:13 pm Post subject: Re: How to sum up the value return by function??

Hi,

evil715 wrote:

When i try to sum up those return value, the result is zero. I know this is because i sum up the formula not the value.

I sum up the value by using =SUM(Value(A1);Value(A2);Value(A3);....)

I think your function does not return a number, but a text. That's why using VALUE() works, it converts a text to a number.
You should specify the return type of your function, so Basic will convert to a number.

Code:

Function getData(freq,startdate,starttime,col,rptid,rpttablename) As Double

_________________
Bernard

OpenOffice.org 1.1.5 fr / OpenOffice.org 3.4.1 en-US + langpacks, MS-Windows XP Home SP3
This forum is unusable, use instead Apache OpenOffice forums

T3rr3nc3
General User

Joined: 17 Sep 2009
Posts: 39
Location: Malaysia

Posted: Sun Aug 01, 2010 10:26 pm Post subject: Re: How to sum up the value return by function??

Hi B Marcelly,

B Marcelly wrote:

Hi,
I think your function does not return a number, but a text. That's why using VALUE() works, it converts a text to a number.
You should specify the return type of your function, so Basic will convert to a number.

Code:

Function getData(freq,startdate,starttime,col,rptid,rpttablename) As Double

You are right. I did a silly mistake. Thanks ~

Display posts from previous:

	OOoForum.org Forum Index -> OpenOffice.org Macros and API	All times are GMT - 8 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum