sum two or more felds in a query

[EORTIZ]

I need help in two things:

1. How can I sum the results of two or more fields in a query. I tried creating a variable like this: total: [field1] + [field2] + [field3] but i just get the concatenation of the three fields values.

2.In a report, include the page number by group, so it resets when a new group starts.

[EORTIZ]

What happened is that there were null values in the fields. I created a variable that replace the Null for a zero. However, doing that, made the variable as text.

To correct it, I first created a table from the query, then run an UPDATE query to put zero where the value was NULL, and then create a query based on the table to create the variable that sums the fields as total:[field1]+[field2]+[field3]

[Stewart Ross]

Hi. Your calculated total field is correct - it is the field values which are the problem. These may look like numbers, but they are undoubtedly strings. When you add strings you get the concatenated result you have found. Another clue to look for is that strings are left-aligned by default, whereas numbers are always right-aligned.
I do not know why what these fields are actually strings - you may be using the format function (which returns a string value) or Nz (which also returns a string value). Anyway, to add them together you need to convert them back to numbers using the Val function:

total: val([field1]) + val([field2]) + val([field3])

There is a thread elsewhere in this forum about resetting page numbers in a report group. I don't have the thread to hand at present. However, here is how I do one of mine for a report on our academic areas (called Schools). This uses a global variable called PrevSchl (defined in the report's code module) to store the last school name as a comparator, and an unbound text box in the footer of the report for the page number (SchoolPages in the example below). The code is in the footers Format event.

Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
    If PrevSchl <> Me.School_Name Then
        Me.SchoolPages = 1
        PrevSchl = Me.School_Name
    Else
        Me.SchoolPages = Me.SchoolPages + 1
    End If
End Sub
 

Note that because the code is in the page footer I don't have to consider the use of Cancel if FormatCount > 1, as the footer is the last item to be placed on the page by Access. In general, Format events for Access reports may be called repeatedly as individual sections are moved around to fit, but the footer seems not to be moved in this way.

-Stewart

I need help in two things:

1. How can I sum the results of two or more fields in a query. I tried creating a variable like this: total: [field1] + [field2] + [field3] but i just get the concatenation of the three fields values.

2.In a report, include the page number by group, so it resets when a new group starts.

[Stewart Ross]

Ahh, Your revised post was not there when I put mine together...

I think using an update is a fair bit of work, and would suggest that you simply use Val conversions on any calculated fields accidentally converted to strings in addition to Nz or whatever you are using at present.

-Stewart

[EORTIZ]

Thanks a lot, kind response.

I am not experiences with MS ACCESS codes, so... simple question How I define the global variable? :-P

[Stewart Ross]

Hi again. The global variable is defined at the top of the code module for the report - just below where it usually says Option Compare Database - as

Dim PrevSchl as String

(for the example given)
or equally valid:

Public PrevSchl as String

You will have to open the code module anyway to add the footer event code, so the easiest way if you have no existing public modules defined is to open the report in design view, go to the footer area of the report, right-click on white space in the footer and select properties, select the Event tab of the properties set, click On Format and choose build, code to open the code module and create a skeleton of the On Format event code. You can then scroll to the top of the code module and add the DIM or Public declaration.

I would caution that you may experience difficulties in using even a simple programmed approach such as the resettable page number described if you are totally unfamiliar with VBA code. You may find that until you gain experience and familiarity it is best to stick with the use of the built-in page numbering. This is your choice, of course, but I feel it is important that you understand that any programmed approach is dependent on a degree of familiarity with the VBA environment and conventions. There are no pre-built wizards or packages which will do this for you.

-Stewart

[EORTIZ]

Thanks a LOTTTTTTTT !!!! I could make it work!! Excellent Thanks thanks