Hello, i have a table that each record maintain 6 donate fields that is money, and 6 date fields. each date relates to one donate. I knew this is not good design. But i have to work on this. I don't want to change the design as it was design by somebody else. Now i need to create a report to have only the most recently date and donate for each record. I use module to have function to get the max date and its donate. Then i have a query that use this function maxDonate.
But when i create a report based on this query, in the report footer, i want to get the total of donate =Sum([MaxDonate]). I got error message:
This expression is typed incorrectly, or it is too complex to be evaluted. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
Any body can help me how to get the total that base on module? Many thanks.
Can you post the expression that it can't calculate? Usually, I get that error message because of the query, not the report. So, check the function and make sure your query that's used for the report can run on its own first.
Your report will need to have all six fields to do the computation on the report, and the expression needs to be =Nz([Field1])+Nz([Field2])+Nz([Field3])+Nz([Field4])+Nz([Field5])+Nz([Field6]). Or, you can create this equation in your query.
why do it as part of a query, you should be able to do this as aprt of the report
consider adding a column to the detail and set its rowsource= ][mycolumnname#1]+[mycolumnname#2]....+[mycolumnname#n
place some group footers on each relevant boundary
copy the detail columns containing the numeric data you are interested in
change the controlsource to =sum([mycolumnname])
copy the modified grouping to the report footer
WHERE donor_id = 'your foriegn key here'
In general, if you find yourself creating a table with a whole bunch of columns like "mycolumn1, mycolumn2, mycolumn3", ask yourself if those columns might be better off in their own table. then you won't run into these kinds of shenanigans.