Formula I cant figure out

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven - 07 Mar 2007 22:43 GMT

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

Reply to this Message

ruralguy - 07 Mar 2007 23:53 GMT

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

>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.
[quoted text clipped - 6 lines]
>
>Steven

Signature

HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Reply to this Message

George Nicholson - 07 Mar 2007 23:59 GMT

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,

>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.
[quoted text clipped - 7 lines]
>
> Steven

Reply to this Message

Steven - 08 Mar 2007 00:19 GMT

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

Reply to this Message

Steven - 08 Mar 2007 01:01 GMT

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

Reply to this Message

BeWyched - 08 Mar 2007 10:12 GMT

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

> Here is my problem:
>
[quoted text clipped - 12 lines]
>
> Steven

Reply to this Message

Steven - 08 Mar 2007 15:24 GMT

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

Reply to this Message

Douglas J. Steele - 08 Mar 2007 16:50 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> BeWyched,
>
[quoted text clipped - 30 lines]
>
> Steven

Reply to this Message

Steven - 08 Mar 2007 20:00 GMT

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

Reply to this Message

BeWyched - 08 Mar 2007 18:37 GMT

Sorry Steven

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

Cheers.

BW

> BeWyched,
>
[quoted text clipped - 30 lines]
>
> Steven

Reply to this Message

Steven - 08 Mar 2007 19:25 GMT

BeWyched,

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

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

and highlights: [tblBudget]

Thank you,

Steven

Reply to this Message