Query : Call a Function

[Guest]

In a field of a query is there a way to call a function to return a formula
to the query field? I have tried but when I create the formula in the
function it always returns the string. I need to call the function because
the field(s) needed to sum in the query field will not always be the same.

Thank you for your help.

Steven

 

[Marshall Barton]

In a field of a query is there a way to call a function to return a formula
to the query field? I have tried but when I create the formula in the
function it always returns the string. I need to call the function because
the field(s) needed to sum in the query field will not always be the same.

Not really. You could try to play around with Eval, nut I
don't see how you could get it to do that.

It sounds like you have something funny somewhere in your
table design that has created this problem. There may be a
way to calculate the sums in this situation, but you will
probably be far better off redesigning your table structure.

Either way, we will need more speciific information before
making any suggestions.

 

[Guest]

Marshall,

Thank you for your response. It is a buget system and I made it to spread
one budget item across 12 fields for each month. M01, M02, M03, M04, M05,
M06.....M12. Depending on the user request it could be any continuous group
of months. For example M01 through M06 ; M04 through M08 etc... and I would
want the sum of those months selected. I have made progress the last couple
days here in the group by creating a form and creating a SELECT string and
then making the Me.RecordSource the string and then using:
DoCmd.OutputTo acOutputForm, , acFormatXLS, ..........
but it seems to be limited to 15,000 records. I greatly appreciate any
direction in this matter.

Thank you,

Steven

 

[Tom Lake]

Marshall,

Thank you for your response. It is a buget system and I made it to spread
one budget item across 12 fields for each month. M01, M02, M03, M04, M05,
M06.....M12. Depending on the user request it could be any continuous
group
of months. For example M01 through M06 ; M04 through M08 etc... and I
would
want the sum of those months selected. I have made progress the last
couple
days here in the group by creating a form and creating a SELECT string and
then making the Me.RecordSource the string and then using:
DoCmd.OutputTo acOutputForm, , acFormatXLS, ..........
but it seems to be limited to 15,000 records. I greatly appreciate any
direction in this matter.

Thank you,

Steven

Don't store multiple months in one record! Each month should have its own
record then
you can DSum across records picking out the months you want to sum.
Something like this:

DSum("MyItem","BudgetTable","Month=" & [SelectMonth1] & " Or Month=" &
[SelectMonth2] & " Or Month=" & [SelectMonth3])

where MyItem is the field you want to sum, BudgetTable is the table that
contains the records to be summed and SelectMonth1, 2 and 3 are the months
you are looking to be summed.

Tom Lake

 

[Marshall Barton]

That's what I was worried about. The idea of having a
column in a table for each month is a gross violation of
relational databases. To be blunt, you need to stop
thinking spreadsheet and realign your brain to the rules of
database Normalization (Google will find a gazillion
explanations).

In a normalized table structure, you would have a table for
budget items and a separate table for all transactions
against budget items.

For example:
table BudgetItems
ItemtID (Primary Key)
ItemDescr Text
Assigned Long (Foreign Key to managers table)

table Transactions
TrID (Primary Key)
TrDate Date/Time
TrType Text
TrAmount Currency

Then your user interface would use a mainform for budget
items and a linked subform for the transactions against each
item.

Searching for transactions in a date range becomes very easy
with this arrangement and the subform could easily be
limited to the desired entries.