Data Type ?

[Guest]

Hello

I've had to create a custom running sum function to be able to calculate the cumulative sum of a column in a query. I then export the query results to excel to be able to graph the information and perform other operations

My problem is that, although the running sum function does calculate the value properly, when I export the data it is not in a "numerical" format. when I try to graph it values I get a flat line. The function I used was taken form this foru

Function Running_Sum(MyVar As Integer
Rsum = Rsum + MyVa
Running_Sum = Rsu
End Functio

And in my qdf I simply call this function "Running_Sum([Value]) AS [Cumulative Value]

Can someone tell me what is wrong and more importantly how this problem can be overcome

Thank you very muc

Daniel

 

[Guest]

I think your problem is that you never define what the data type is in the first place.

Try this

Function Running_Sum(MyVar As Integer) As Intege
Rsum = Rsum + MyVa
Running_Sum = Rsu
End Functio

Also, you could try putting INT([expression]) in your export query. This defines this field as an integer

Hope this helps

Jim

 

[Guest]

Jim

I have previously tried to define what the data type is in the first place, but when I do so, the sum function no longer returns the proper values (I can't figure it out

without defining the data type in the first plac
Value Su
**********
1
0
0
2
0
5

when I define the data type in the first plac
Value Su
**********
1
0
0
2
0
5 1

As far as your second comment, where would I need to use this? I didn't understand

Thank you

Daniel

 

[Guest]

Daniel

As for the second part, I was just seeing if putting INT() around the field helped when exporting.

If your query is
SELECT Value, Running_Sum(Value) FROM [wherever

Try this
SELECT Value, INT(Running_Sum(Value)) FROM [wherever

This will cast the second field as type integer. Look at Type Conversion Functions in Microsoft Access Help for a complete list of casting

Hope this helps

Jim

 

[TC]

Try assigning a type to the function:

Function Running_Sum(MyVar As Integer) As Single (or whatever)

^^^^^^^

Run the query & look at the position of the function values in the relevant
column. If those values are >left aligned<, Access thinks that the function
is returning non-numeric values - even if they "look like" numerics. If it
is >right aligned<, Access correctly thinks that the function is returning
numeric values.

HTH,
TC

Hello,

I've had to create a custom running sum function to be able to calculate

the cumulative sum of a column in a query. I then export the query results
to excel to be able to graph the information and perform other operations.

My problem is that, although the running sum function does calculate the

value properly, when I export the data it is not in a "numerical" format.
when I try to graph it values I get a flat line. The function I used was
taken form this forum

Function Running_Sum(MyVar As Integer)
Rsum = Rsum + MyVar
Running_Sum = Rsum
End Function

And in my qdf I simply call this function "Running_Sum([Value]) AS [Cumulative Value]"

Can someone tell me what is wrong and more importantly how this problem can be overcome.

Thank you very much

Daniel