Summing unbound fields in a report

[Rainer2000]

Hallo,

It sounds like a weird question, but I try it anyway.
I have horizontal calculations in a report using a conditional VB-Code in the detail section of the report. I thought it would be a terific idea to use an unbound field and all worked well until I came to vertically summing and grouping the unbound fields.
I have no idea how. Normally it is done by =Summe([Price]). But Grouping & Summing unbound fiels??

The VBA routine looks like this, and Text11 is an unbound field as I need the data only in the report.

Private Sub Detailbereich_Format(Cancel As Integer, FormatCount As Integer)
If Me.Text1 >= 25 Then Text11 = Text1 * 0.9
If Me.Text1 <= 4.5 Then Text11 = Text1 * 0.6
If Me.Text1 >= 5 And Me.Text1 <= 24.99 Then Text11 = Text1 * 0.8
End Sub

I may however be on a completely wrong track.!

Rainer

 

[dhookom]

IMHO this code should never be in a report. This seems to be some business rules or logic that should be stored in tables or at least placed in a single function in a module of business rules. Hard-coding values leads to hard to manage and maintain applications.

Consider creating a function like:

Public Function GetValue(dblSomeValue as Double) as Double
    Select Case dblSomeValue
       Case ....
          GetValue = ...
       Case ....
          GetValue = ...
       Case Else
          GetValue = ...
    End Select
End Function

You can then bind text11 to
=GetValue([FieldX])
and then use
=Sum(GetValue([FieldX]))

You should also make it a practice to provide good names for controls that you might reference in either code or other expressions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rainer2000]

Duane,
thanks a lot for your repy. I think I will, use a more conventional ways to do this.
Anyway, it was worth giving it a try.

Rainer