Formula I cant figure out

[Guest]

I have a table with that include fields 01 02 03 04 05 06 07 08 09 10 11 12
for the months of the year. I have a form where the user picks 2 months.
For example the user picks 03 and 07 which means for all the records in the
table I want to sum fields 03 through 07. I cant figure how to make this
equation in the query. Or do I have to do some type of return like
ReturnMthsTotal() in the query.

Thank you for your help.

Steven

 

[ruralguy via AccessMonster.com]

If your table was normalized then each month would be in a separate record
and your task would be trivial.

 

[George Nicholson]

With that non-normalized table design, you can't easily create a saved query
to do what you want. (One reason non-normalized structures are bad.) You
could create a saved query that sums all fields, open that as a recordset
via code and then pull the 2 corresponding values from that recordset.

Or you could use an aggregate function, which does the same thing:
Me.txtResult1=Dsum("[03]","myTable")
Me.txtResult2=Dsum("[07]","myTable")

HTH,

 

[Guest]

The reason it is written this way is because it is a budget and one item is
by month which I do not want to create 12 records in this table for one item.
I am thinking I might be able to do in the query ReturnMonthsTotal()
......... and in a module have a function
Function ReturnMonthsTotal()
Case .....
Case .....
and so on but I am not sure how to approch this
End Function

 

[Guest]

Here is my problem:

If I make a function like:

Function ReturnMonthTotal()
ReturnMonthTotal = "[03] + [04] + [05] + [06]"
End Function

and in the query put: Total: ReturnMonthTotal()

I will get a sting value. How do I make it so it will work as a formula in
the query.

Thank you for your help.

Steven

 

[Guest]

Hi Steven

You could create an array to store the monthly values which will make it
easy to sum totals. It's a bit messy but will work:

Function ReturnMonthTotal(StartMonth, EndMonth)
Dim Ar(12), n as integer, RunningTot as Currency
Ar(1) = Field 01 value
Ar(2) = Field 02 value
.... etc.
For n = StartMonth to EndMonth
RunningTot = RunningTot + Ar(n)
Next n
ReturnMonthTot = RunningTot

Cheers

BW

 

[Guest]

BeWyched,

Thank you for your response. I am still having problems. Here is where I
am at:

In the Query I have ReturnMonthTotal(StartMonth, EndMonth)

In the Function:

Function ReturnMonthTotal(StartMonth, EndMonth)
Dim Ar(12), n As Integer, RunningTot As Currency
Ar(1) = [tblBudget].[01].Value
Ar(2) = [tblBudget].[02].Value
Ar(3) = [tblBudget].[03].Value
Ar(4) = [tblBudget].[04].Value
Ar(5) = [tblBudget].[05].Value
Ar(6) = [tblBudget].[06].Value
Ar(7) = [tblBudget].[07].Value
Ar(8) = [tblBudget].[08].Value
Ar(9) = [tblBudget].[09].Value
Ar(10) = [tblBudget].[10].Value
Ar(11) = [tblBudget].[11].Value
Ar(12) = [tblBudget].[12].Value
For n = StartMonth To EndMonth
RunningTot = RunningTot + Ar(n)
Next n
ReturnMonthTot = RunningTot

End Function

I get an error "External name not defined." Can you help me with this?
Thank you very much.


Steven

 

[Douglas J. Steele]

Where did you put the function? It needs to be in a module (not a class
module, nor the class associated with a form), and the module cannot be
named ReturnMonthTotal (module names cannot be the same as sub or function
names).

And just to make sure, are you running the query from within Access, or are
you running it from outside of Access (say through a web page, or a VB
program)? It won't work unless you're running within Access.

 

[Guest]

Sorry Steven

The final lne of code should read:
ReturnMonthTotal = RunningTot
and NOT
ReturnMonthTot = RunningTot

Cheers.

BW

 

[Guest]

BeWyched,

It gives me an "External name not defined on the Ar(1):

Ar(1) = [tblBudget].[01].Value

and highlights: [tblBudget]

Thank you,

Steven

 

[Guest]

Douglas,

Thank you for your response.

Everything is from within access. I have a select query that has a source
of a table with fields Co, Account, Year, 01, 02, 03 .......12. I did it
this way because for one record in the budget I wanted to post by month.
Typically I would set up a Field called Month and then records would be 01,
02 ...12 but in this case I wanted the fields to be individual months. Now
... on the form the user will pick a StartMonth and and EndMonth. So for
example ; if the user picks StartMonth 04 and EndMonth 07 then the formula
in the query for a total field would be Total: [04] + [05] + [06] + [07] .
Originally I was thinking I could return a formula to the Total: ______ in
the query and use a function to create which months to include. Note: The
module is just named Module10.

Thank you,

Steven