calculation opinion

[slimjen]

All, using 2010. I have a database which I inherited. I’m in the process of revamping it. I noticed there were a lot queries which are group named to perform calculations leading to the final calculation for a result. i.e. pre-qryPerformcalculation 1, pre-qryPeformcalcuation2…..finalCalcuations. Of course I’ve been working on these to consolidate where I can. I am now creating a form for the user interface. My question for you; is it out in left field to have many of the calculations in the queries to be performed in the form with vba instead? I can recall years ago I saw some functions called from a form to do just this. But may be some restrictions because of this need to be done with a query not a table. Just thinking out loud. For example;

Private Sub Calculate()
Dim i As Integer
Dim varFieldValue As Variant
Dim strFieldName As String
 
 'Loop through records and calculate 
  Do While Not rs.EOF
  varFieldValue = Null
 For i = 1 To 10 Step 1
 strFieldName = "r" & Format(i, "00") 
If Not IsNull(rs(strFieldName)) Then
  varFieldValue = rs(strFieldName)
   'Done
         Exit For
   End If
  Next

This is just a snippet of something I can remember from performing calculations in VBA. Again, I am just asking for an expert opinion.

[rpeare]

are you using this function to update data in a table? i.e. calculate a value then return that value to the table? If you are storing calculated values in your table, I would suggest not doing that. Calculated values can be very touchy and if an update doesn't perform correctly it can destroy your results.

[June7]

Do calcs in queries whenever possible. When needed, call functions to return values to query.

That snippet looks incomplete. If it is used to save calculated result to field, that is not the same as doing calcs in queries.

[slimjen]

Thank you for replying. I absolutely not will be updating a table. I learned the hard way a few years agoJ My current queries does calculations and the results are updated to a report. Sometimes it may be a couple hundred records or fewer than a hundred. Instead of using the query to perform the calculations; I thought maybe performing in the form with a function. Again thanks for replying

[June7]

How does code update a report if it is not saving results to table?

[rpeare]

Probably just writing it to a report field which seems horribly inefficient to me. I don't know why a function would be more efficient than doing this in a query though if it's a mathematical function. If you're doing something like trying to label a column on your report based on a query field name then I would understand it but typically formulas are more efficient in queries rather than in functions.

[June7]

rpeare: Writing to a report field? That suggests a table is involved and editing a record with calculated result. Did you mean setting an unbound textbox? I did not think that possible with report.

[rpeare]

I have edited control sources of unbound text boxes, changed captions on labels etc in the ON OPEN event of reports. I've got a financial database split into 'special' and 'general' revenue for each calendar year, the 'special' revenue is run on a crosstab query but I don't define the formulas in unbound text boxes or the column headers until the report is open. The formulas are generated and dumped into each control source. I don't really know based on his snippet of code what he's trying to do but I've got at least 2 reports where I modify column labels (they are blank if you look at the design view) and unbound text box control sources to handle this unpredictable number of columns from year to year.

[June7]

Really! Seem to remember trying to directly set properties of report controls with VBA some time ago and gave up. Decided to use textboxes as dynamic labels. If I ever have to build report based on CROSSTAB, will keep that in mind.

[slimjen]

Lol. Please guys; ease up. I was just brainstorming. I appreciate your feedback. I hear loud and clear calculations may be best in a query. Thank you

[hapm]

It depends for me on how the calculations are used. If the calculation needs to be fine for each record and you have a continuous form for example, you should do it in a query in most places. If so, avoid using custom VBA functions as they are non deterministic by default. Calculating sums or other aggregations in the record source can otherwise lead to requeries you wouldn't need when it would be a calculated firm field. In general there are some things I normally look at before making a decision: how the value is represented, can it change while viewing the data and how long the calculation takes.

[rpeare]

I think we were brainstorming too and got carried away.

Here's an example of a dynamic report based on a crosstab (for June7 if he's still interested).

https://www.accessforums.net/reports...tml#post234002