OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [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??

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
T3rr3nc3
General User
General User


Joined: 17 Sep 2009
Posts: 39
Location: Malaysia

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

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
Back to top
View user's profile Send private message MSN Messenger
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

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

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 / Apache OpenOffice 4.0.1 / LibreOffice 4.1.0
MS-Windows 7 Home SP1
This forum is spammed, use instead Apache OpenOffice forums
Back to top
View user's profile Send private message Visit poster's website
T3rr3nc3
General User
General User


Joined: 17 Sep 2009
Posts: 39
Location: Malaysia

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

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 ~
Back to top
View user's profile Send private message MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group