×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Report level formulas

Report level formulas

Report level formulas

(OP)
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:

CODE -->

=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.

CODE -->

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

I use Access 2016.

TIA

Regards,


OCM

RE: Report level formulas

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

RE: Report level formulas

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

RE: Report level formulas

(OP)
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.

Quote:

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

TIA
Regards,

OCM

RE: Report level formulas

If I understand,
  • You are asking a report question in a "Forms" forum (just to be sure I'm not missing something)
  • You have a combo box named [Type] in your report's header section
  • The combo box has two values "Primary" and "Secondary"
  • Based on the selection of the combo box you expect different values to be displayed in a text box some place on the report
  • We don't know anything about the report's record source except is contains a numeric 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"
Is all of this correct?

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

RE: Report level formulas

(OP)
Duane,

Quote:

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

Quote:

You have a combo box named [Type] in your report's header section
Correct, my form includes a field (combo box) called [Type]

Quote:

The combo box has two values "Primary" and "Secondary"
--> Correct

Quote:

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

Quote:

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]

Quote:

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

RE: Report level formulas

CODE -->

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.

RE: Report level formulas

(OP)
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

RE: Report level formulas

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:

CODE --> ControlSource

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

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

RE: Report level formulas

"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

RE: Report level formulas

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

SomeTable
Primary  Secondary Amount
   Y         N      20.00
   N         Y      40.00 

Or maybe:

SomeTable
Prim_Sec    Amount
   P        20.00
   S        40.00 

Or something else...?


---- Andy

There is a great need for a sarcasm font.

RE: Report level formulas

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

Quote:

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

RE: Report level formulas

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:

CODE --> ControlSource

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

CODE --> ControlSource

=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:

CODE --> ControlSource

=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

RE: Report level formulas

This doesn't look like any table I know:

tbl_Investigators
[Type] [PAmount] [CAmount]
P       $20.00	
S       $40.00    $30.00
Totals  $60.00    $30.00
 
Tables do not have Totals (do they ponder)
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.

RE: Report level formulas

(OP)
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

Quote:

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

RE: Report level formulas

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

RE: Report level formulas

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

Quote:

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

RE: Report level formulas

(OP)
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

RE: Report level formulas

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

RE: Report level formulas

(OP)
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:

Quote:

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.

Quote:

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

RE: Report level formulas

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

RE: Report level formulas

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

Quote (OCM)

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

Quote (Duane)

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 ponder


---- Andy

There is a great need for a sarcasm font.

RE: Report level formulas

(OP)
Hi,

Thank you both.

Duane,

Quote:

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)

Quote:

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

RE: Report level formulas

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

CODE --> vba

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

RE: Report level formulas

(OP)
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

RE: Report level formulas

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

RE: Report level formulas

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

OCM

RE: Report level formulas

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

RE: Report level formulas

(OP)
Got it, many thanks Duane!

Regards,

OCM

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close