Report level formulas

[OCM]

Hi,

I’ve a combo box [TYPE] with two values to choose from (Primary & Secondary). Based on selection made from the dropdown, I would like to display the sum for the [Amount] field in my report.
If user makes no selections, [Amount] = the sum of both values (primary and secondary). And, if Amount is blank, I would like to display “n/a”

I tried the following:

=Sum(IIf([Amount] Is Null,0, [Amount]))

a. if Primary is selected from dropdown, the sum is correct
b. if Secondary is selected from dropdown, the sum is correct

1. But, if nothing is selected from [TYPE] dropdown (i.e. type = Primary and secondary) I get an incorrect sum. For some reasons the amount for type = Secondary is being added twice.

2. How can I display a text message e.g. “N/A” or “No totals for this case” etc. when amount is blank?
I tried the following formula, but it didn’t work.

=Sum(IIf([Amount] Is Null,”N/A”, [Amount]))

I use Access 2016.

TIA

Regards,




OCM

 

[dhookom]

So if P is selected, you only want records with a Type "P" in the report. The "S" records will not display.
So if S is selected, you only want records with a Type "S" in the report. The "P" records will not display.
So if nothing is selected, you want records with a Type "S" in the report. The "P" records will not display.

This would be a basic filter on the records returned to the report.

You still have a Sum([Amount]) but I don't see a field named [Amount].

You can't really Sum distinct values without using a running sum. However I need to first understand if you are filtering the report or just filtering the totals displayed.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

I think there is still some miss-communication here...

if nothing is selected from Type drop down,
I would like to show the sum of both P & S
(amount columns from records where the Type = P and S

So if nothing is selected,
you want records with a Type "S" in the report.
The "P" records will not display.

And that has to me just me, but I still don't know the structure of the table/query OCM is using


---- Andy

There is a great need for a sarcasm font.

 

[OCM]

Hi,

Thank you both.

Duane,

So if P is selected, you only want records with a Type "P" in the report. The "S" records will not display. Yes, as per my previous screenshot
So if S is selected, you only want records with a Type "S" in the report. The "P" records will not display.
So if nothing is selected, you want records with a Type "S" in the report. The "P" records will not display.

The answer to all your question above is yes (as per my previous example screenshot)

You still have a Sum([Amount]) but I don't see a field named [Amount]

Field names for the Amount are [PAmount] and [CAmount] these are titled Prev. Amount and Curr. Amount on my report.
I tried Sum([PAmount]) and Sum([CAmount]). It worked for Type = P, but gave incorrect sum when Type = S.

Please refer to my analysis on page 2 of my previous screenshot for possible reasons.

TIA

Regards,


OCM

 

[dhookom]

Code on the form with the TYPE combo box (cboType):

Dim strWhere as String
Dim strReportName as String
strWhere = " 1=1 "
strReportName = "Your Report Name Here"
Select Case Me.cboType 
    Case "P"
       strWhere = strWhere & " AND [TYPE]= 'P' "
    Case "S"
       strWhere = strWhere & " AND [TYPE]= 'S' "
End Select
DoCmd.OpenReport strReportName, acViewPreview, , strWhere

If you are getting some values duplicated, you must tell us how to look at a single record and determine if it is a duplicate.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Duane,
Thanks, I’ll try it on Monday when and let you know the result.
The code should go on the form (Type combo box to the on click anent of cboType correct?

TIA

Regards,


OCM

 

[dhookom]

Typically you would allow the user to make a selection from the combo box and have a command button to open the report.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Hi Duane,
Thank you, it is working now.
Can you please explain the usage of strWhere = “ 1 =1 “ ?
TIA
Regards


OCM

 

[dhookom]

I use "1=1" so that I can simply add " AND someother filter ". Without the "1=1" I would need to check to see if the filter was the first or possibly remove a leading or trailing " AND "

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[OCM]

Got it, many thanks Duane!

Regards,

OCM