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

 

[MajP]

isnull([Amount])...
or better to check for empty strings, spaces, and nulls
iff(trim([Amount] & " ") = "",...

 

[dhookom]

I'm having great difficulty understanding your report. Where is the "combo box [TYPE]" and why don't I see it referenced in your expressions?

Are you viewing your report in report view or print preview?

You mention "both values (primary and secondary)" but I don't see any reference to any values other than [Amount].

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

 

[OCM]

Thank you both,
Duane,
Sorry about the confusion. I didn’t mention that referencing [TYPE] and its values (primary and secondary) was the main question I was struggling with here.

Are you viewing your report in report view or print preview?

report view

TIA
Regards,

OCM

 

[dhookom]

If I understand,
[ul]
[li]You are asking a report question in a "Forms" forum (just to be sure I'm not missing something)[/li]
[li]You have a combo box named [Type] in your report's header section[/li]
[li]The combo box has two values "Primary" and "Secondary"[/li]
[li]Based on the selection of the combo box you expect different values to be displayed in a text box some place on the report[/li]
[li]We don't know anything about the report's record source except is contains a numeric field named [Amount][/li]
[li]If the Sum([Amount]) is null meaning no records have a value in the Amount field, you want to display "N/A"[/li]
[/ul]
Is all of this correct?


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

 

[OCM]

Duane,

You are asking a report question in a "Forms" forum (just to be sure I'm not missing something)

I’ve a filter form whereby users select from drop down and click a button to display filtered records to the report

You have a combo box named [Type] in your report's header section

Correct, my form includes a field (combo box) called [Type]

The combo box has two values "Primary" and "Secondary"

--> Correct

Based on the selection of the combo box you expect different values to be displayed in a text box some place on the report

--> Correct

We don't know anything about the report's record source except is contains a numeric field named [Amount]

The report’s record source is a query. Correct, the report contains a currency field named [Amount]

If the Sum([Amount]) is null meaning no records have a value in the Amount field, you want to display "N/A"

--> Correct

Please let me know if you need additional clarification.

TIA

Regards,

OCM

 

[MajP]

Public Sub TestNull()
  Dim x As Variant
  x = Null
  'Debug.Print x Is Null
  Debug.Print IsNull(x)
  Debug.Print x = Null
End Sub

x is null throws an error because only works on objects
IsNull(x) returns true
x = Null returns Null and will never be true

In SQL you can use [Amount] is null but not in vba.

 

[OCM]

MajP,
Now, I’m completely lost.
Can the formula in my OP, or the formula you suggested in your reply be modified this time to include referencing combo box values?

e.g.
If Primary is selected from [Type], show [Amount] sum for Primary
If Secondary is selected from [Type], show [Amount] Sum for Secondary
If no selections made from [Type] drop down, this means the report displays both Primary & Secondary [Type]

TIA

Regards,

OCM

 

[dhookom]

OCM,
Are you using any VBA or is this strictly expressions in the Control Source of a text box?

Is there any difference between what you want to display if the user select Primary or Secondary?

If all you want is to display N/A where the Sum() is null, try:

=Nz(Sum([Amount]),"N/A")

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

 

[dhookom]

show [Amount] sum for Primary" has no meaning unless you tell us how records are defined as primary or secondary. Is there a field in the report's record source that you haven't told us about?

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

 

[Andrzejek]

It may also be beneficial to know the structure of your data.
Is it:

[pre]
SomeTable
Primary Secondary Amount
Y N 20.00
N Y 40.00[/pre]

Or maybe:

[pre]
SomeTable
Prim_Sec Amount
P 20.00
S 40.00[/pre]

Or something else...?


---- Andy

There is a great need for a sarcasm font.

 

[OCM]

Hi,
Thank you both. Please let me know if the attached sample screenshot helps.
Andy,

It may also be beneficial to know the structure of your data.

To answer your question, it’s similar to your second sample.
Please see second page on sample attachment.


TIA
Regards,


OCM

 

[dhookom]

So there is not field "[Amount] field in my report" but there is a field named [Type] in the report's record source that contains either P or S. To be totally honest I'm not sure why this information wasn't provided in the first posting. Some sample data and expected/desired results with a form and combo box name would have been minimal information. I don't know how we were expected to provide any assistance without some basic information.

To sum the PAmount where Type = "P" you could use the expression:

 =Sum(Abs([Type]="P") * [PAmount])

Sum CAmount where Type = "S"

=Sum(Abs([Type]="S") * [CAmount])

You should be able to wrap either of these expression in Nz(....,"N/A") to return a value if Null.

If you want to only display totals for P or S you could use something like:

=IIf(Forms!frmYourFormName!YourComboBoxName = "P", Sum(Abs([Type]="P") * [PAmount]), Sum(Abs([Type]="S") * [PAmount]))

I find it hard to believe =Sum([AnyFieldHere]) will return any value added twice. You are doing something non-standard that we don't yet know about.

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

 

[Andrzejek]

This doesn't look like any table I know:

[pre]
tbl_Investigators
[Type] [PAmount] [CAmount]
P $20.00
S $40.00 $30.00
Totals $60.00 $30.00
[/pre]
Tables do not have Totals (do they )
If P and S in [Type] field stand for Primary and Secondary, and [PAmount] field holds amount for... both? Just the Primary?
And what is this [CAmount] field for?


---- Andy

There is a great need for a sarcasm font.

 

[OCM]

Andy,
Total is not part of my table. The example in the screenshot (previous attachment) was to show how I would like to show Total/Sum in the report.

I have a table called tbl_Investigators to include the following objects:
[Type]: P: primary and S: secondary
[PAmount]: previous amount
[CAmount]:current amount

If P and S in [Type] field stand for Primary and Secondary, and [PAmount] field holds amount for... both? Just the Primary? And what is this [CAmount] field for?

[PAmount] and [CAmount] are currency fields and they hold amount for both P and S.

Sorry about the confusion

Regards,

OCM

 

[dhookom]

OCM,
Did you try the expressions I suggested?
Did they work?
What are the rules you want to follow for what you want to display in your report group sections?

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

 

[OCM]

Duane,
Yes, I did and got #Type! error. I probably have a name conflict which I'm currently troubleshooting to correct.

What are the rules you want to follow for what you want to display in your report group sections?

I'm not sure if I understand your question. Can you please give me example?

TIA

Regards,

OCM

 

[OCM]

Duane,
I managed to fix the #Type! error and tested the expression. Here is the result
1. I selected ‘P’ from [Type] and the report displays the correct total (sum)
2. I selected ‘S’ for [Type] the report again displays the correct amount
3. I made no selection for [Type] this time the report displays nothing $0.00 for both [PAmount] and [CAmount]

TIA

Regards,


OCM

 

[dhookom]

When I run a similar report with no values in a numeric field (not even zeros) there is nothing displayed in the Sum() text box. Are you sure all of your fields are Null and not zero? You can use an IIf() expression to display something else if the sum is 0. You can also use the Format property to display whatever you want if an expression is 0.

Rules:
If you select P from the Type combo box what do you want to see in the report? Only the sum of the amount columns from records where the Type is P?

If you select S from the Type combo box what do you want to see in the report? Only the sum of the amount columns from records where the Type is S?

If you don't want to see any sum in the report, I would probably add "None" to the P and S in the combo box and if the combo box value is None, use IIf() to return whatever you want.

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

 

[OCM]

Duane,
I think we are getting somewhere. I've attached a screenshot so you have a better idea of my requirements.
But, to answer your questions about rules below:

If you select P from the Type combo box what do you want to see in the report? Only the sum of the amount columns from records where the Type is P?

Correct.

If you select S from the Type combo box what do you want to see in the report? Only the sum of the amount columns from records where the Type is S?

Correct.

And, in addition to the above, 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)

TIA

Regards,

OCM