A calculated field in reports

[wrightie]

Hi, I'm at my wits end with this. So I'm hoping someone can save my sanity!

What I have is a form where you can select a Period Number and when you click the ok button runs a query and then opens the results in a report for that period number.

Query Code -
Code:
SELECT *FROM Broker INNER JOIN Premium ON Broker.BrokerNo = Premium.BrokerNoWHERE (((Premium.PeriodEntered) In ('2')))ORDER BY Premium.PeriodEntered;


VBA CODE BEHIND THE OK BUTTON -

Code:
Private Sub cmdOK_Click()' Declare variables Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim varItem As Variant Dim strCriteria As String Dim strSQL As String' Get the database and stored query Set db = CurrentDb() Set qdf = db.QueryDefs("Qry_By_Period_Entered") ' Loop through the selected items in the list box and build a text string For Each varItem In Me!lstPeriods.ItemsSelected strCriteria = strCriteria & ",'" & Me!lstPeriods.ItemData(varItem) & "'" Next varItem' Check that user selected something If Len(strCriteria) = 0 Then MsgBox "You did not select anything from the list" _ , vbExclamation, "Nothing to find!" Exit Sub End If' Remove the leading comma from the string strCriteria = Right(strCriteria, Len(strCriteria) - 1)' Build the new SQL statement for the Query incorporating the string strSQL = "SELECT * FROM Broker INNER JOIN Premium ON Broker.BrokerNo = Premium.BrokerNo " & _ "WHERE Premium.PeriodEntered IN(" & strCriteria & ") Order by Premium.PeriodEntered;"' Apply the new SQL statement to the query qdf.SQL = strSQL' Open the query DoCmd.OpenReport "Premium_by_PeriodEntered", acViewPreview ' Empty the memory Set db = Nothing Set qdf = NothingEnd Sub

This all works fine. However in the report I want to add some calculated fields.
Now I've done this in another query where I had to store one of the calculated fields in and it works fine.

Code:
SELECT Premium.BrokerNo, Broker.BrokerName, Premium.PolicyNumber, Premium.PolicyHolder, Premium.Business1, Premium.Business2, Premium.Business3, Premium.PremiumA, Premium.PremiumB, Premium.PremiumC, Premium.IPT, Premium.Commission, Premium.AssetComm, Premium.Currency, Premium.PeriodFrom, Premium.PeriodTo, Code:
Original - CodeSum(([PremiumA]+[PremiumB]+[PremiumC])) AS PremiumBal

1. Sum(([PremiumA]+[PremiumB]+[PremiumC])) AS PremiumBal

FROM Broker INNER JOIN Premium ON Broker.BrokerNo = Premium.BrokerNoGROUP BY Premium.BrokerNo, Broker.BrokerName, Premium.PolicyNumber, Premium.PolicyHolder, Premium.Business1, Premium.Business2, Premium.Business3, Premium.PremiumA, Premium.PremiumB, Premium.PremiumC, Premium.IPT, Premium.Commission, Premium.AssetComm, Premium.Currency, Premium.PeriodFrom, Premium.PeriodTo;


The problem I have is that my new query selects throught form and selects * and won't work if I try to place the sum in to it.

I hope this is enough info for some help any appreciated.

Cheers
Wrightie

 

[jzwp22]

If the 3 fields are already part of the query just do the calculation as follows (without the SUM() function):

([PremiumA]+[PremiumB]+[PremiumC]) AS PremiumBal

SUM() is used in totals queries which require a GROUP BY clause.

 

[wrightie]

I've got it. Thanks so much

 

[jzwp22]

You're welcome; good luck with your project.