Add/Subtract 2 count records fields two seperate reports into one. (1 Viewer)

JMichaelM

Registered User.
Local time
Today, 02:34
Joined
Aug 4, 2016
Messages
101
Anyone recall the control syntax to add two count records totals from two seperated reports into another report?
 

Mark_

Longboard on the internet
Local time
Today, 02:34
Joined
Sep 12, 2017
Messages
2,111
By "Two separated reports", are these both subreports on ONE report?
 

Ranman256

Well-known member
Local time
Today, 05:34
Joined
Apr 9, 2015
Messages
4,339
use the 2 queries in Rpt1 and Rpt2
combine them in a union query then sum the values
 

isladogs

MVP / VIP
Local time
Today, 09:34
Joined
Jan 14, 2017
Messages
18,186
If I understand you correctly you should use DCount for each of the items you need
Code:
=DCount("*","table or query name","optional criteria")
 

JMichaelM

Registered User.
Local time
Today, 02:34
Joined
Aug 4, 2016
Messages
101
Report 1. Has a count total field.(=Count (*)
Report 2. Has a coint total field.(=Count (*)
Report 3. Need to add a control to subtract both.
 

isladogs

MVP / VIP
Local time
Today, 09:34
Joined
Jan 14, 2017
Messages
18,186
The Count(*) only works for counting the records in a report whilst it is open.
So you need to count the underlying data using the record source used for each report.
In other words look at the suggestions already provided.
 

JMichaelM

Registered User.
Local time
Today, 02:34
Joined
Aug 4, 2016
Messages
101
Completed the line of work for report. Cant thank you enough. I know its basic but i have to go through the process and build it out so i can then maybe translate it into a better solution using VBA. You are all great and cant thank you enough.
 

JMichaelM

Registered User.
Local time
Today, 02:34
Joined
Aug 4, 2016
Messages
101
Solution..create blank report

1. Text box control. =DCount("*,"qry1") Name: Box 1
2.Text box control. =DCount("*,"qry2") Name: Box 2
3. Text box control. =[Box1]-[Box2] Name: Box3
4. Text box control. =[Box3]/[Box1] Name: Box 5
5. Convert Box 5 to percent.
 

isladogs

MVP / VIP
Local time
Today, 09:34
Joined
Jan 14, 2017
Messages
18,186
Solution..create blank report

1. Text box control. =DCount("*,"qry1") Name: Box 1
2.Text box control. =DCount("*,"qry1") Name: Box 2
3. Text box control. =[Box1]-[Box2] Name: Box3
4. Text box control. =[Box3]/[Box1] Name: Box 5
5. Convert Box 5 to percent.

You can do that in one step in a single textbox
Code:
=(DCount("*,"qry1") - DCount("*,"qry1"))/DCount("*,"qry1")
Format the textbox control as Percent

OR convert to % like this - format as General Number this time
Code:
=100*(DCount("*,"qry1") - DCount("*,"qry1"))/DCount("*,"qry1") & "%"
 

JMichaelM

Registered User.
Local time
Today, 02:34
Joined
Aug 4, 2016
Messages
101
I needed to display the stats but that is very helpful.
 

Users who are viewing this thread

Top Bottom