Conditional count in access report Results

Hello, (access 2010)

I have a text box in a report (see screen shot)
Control Source as follows as per screenshot:
Total Jobs:=DCount("jobid","qryFSales")
Total Invoice Amount:=Sum([invoice_amount])
Avg Invoice Amount:=Sum([invoice_amount])/[txtTotalJobs]
RTA Jobs:=Count(IIf(Nz([Assembled],0)=0,1,Null))
RTA Jobs $:=Sum(IIf([Assembled]=0,[invoice_amount],0))
RTA Jobs $ Avg:=Sum([txtRtaCost])/[txtRtaJobs]

this produces an error box looking for txtRtaCost and txtAssCost to not produce the average for RTA Jobs $ Avg: and Assembled Jobs $ Avg:

If a job has NO $ value ($0.00) associated to it, how do I not include those jobs in the average sum?

Appreciate the help for this new user


I'm sure this has been answered (matter of fact I'm still scouring the forums trying to find an example or the answer to what I'm looking for).

Access 2003 as we all know has a limit of 3 conditional formatting options (4 if you count the default). I'm looking to add to this via some VBA code. The data I'm referencing is in a table stored in columns labeled 'Day1, Day2' all the way to 31 (for days of the month). Each row has a persons name (for example John Doe). This is sorta like a time clock entry. John Doe did 3 hours of work on the 3rd day, so in Day3 it'd have 3.0.

Anyway, the report that was given to me pulls this data and generates the report. Right now, if it's over 6.5 the cell is colored Green. between 3.0 and 6.5 it's another color, less than 3.0 is another color and if no data entered it's red (default). I want to break this down even further.

I'm having a hard time finding the proper steps to do this. Would I need to do it as Report_Open or Report_Load function? Module? ARGH.

I can do this no problem in Excel, I just can't figure out the VBA to make it work in Access 2003.


i have a report that serves the pupose of reminder to students that they have not paid their fee of the months listed in detail section, listing of month varies student to student, some student have only current month fee due, some have 3 or 4 month fee due, i want a label "Last reminder" to print on those students whose outstanding fee is more than current month i.e. current plus previous months. if a student has only current month fee due, this label should not be printed on his reminder. a sample of my report designe is attached. Attached Thumbnails   Reply With Quote 12-14-2012, 11:08 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,119 Best to use a textbox with an IIf expression in ControlSource to display text if condition is met. Just don't know enough about your db to advise how to test for the condition. Count the months listed in the Detail section?

I'm relatively new to the Access game but amd trying to extract some data from a healthcare database that I have created.

There is the main table with standard/simple data which has a linked table that allows multiple values to be entered. I have created queries that will count how often each individual response has been entered however I want to create a query that tells me how many records have more than one (multi)value selected.

This would mean that in our reporting we could say x% of people have this condition, x% of people have this condition, x% of people have this condition while x% of all respondents had more than one condition.

NB. The linked multivalue table has approx 20possible responses while the main table has about 1000 records.

Thanks in advance

hi dear,

i have a question that can i count in report with conditional itself?
example in excel we can count what i mean with "countif" but how about access?its possible?

I have designed a report based on a cross tab query that counts the number of units sold by each dealer per month. So the left hand column lists the dealer and the rows along the top list the months. There is then a total at the bottom.

This worked find when I was reporting on an entire year. However, the query now has a date range criteria and if this is only part of a year the report wont run because it thinks it is missing data (i.e. if the date range is 01/01/2010 to 01/09/2010 then the report fields for October onwards cant find a matching field and so the report wont run).

I was hoping the months without data would just appear with a blank in the field or an error code that I could use conditional formatting to white out.

Any ideas?

BTW it is Access 2003

I have an Access 2007 database where I generate and export a report to Excel 2007. I then have a button on my report to open and format the exported file. The file opens just fine. The initial formating is completed, but then it hacks on defining conditional formats. My conditional formating is pretty simple - comparing all the date fields in column G with 4 other date fields (A1, B1, C1, and D1). The error is one I found to be common when searching the net 'Run-time error '5': Invalid procedure call or argument' I have not been able to find another thread with a solution that fitls my issue. Below is my code - it stops on the first call to Add a Formula for conditional format. I followed other advise of recording a macro in Excel and then copy/paste to Access, but I've made some minor modifications not pertaining to the conditional formulas. Any help is much appriciated.

Private Sub btnExport_SB_Click()
Dim objXLApp As Object
Dim objXLBook As Object
Dim strFormula1 As String
Dim strFormula2 As String
Dim strFormula3 As String
Dim strFormula4 As String

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("D:dataWorkDataProjects ProjectPlanningqryProjectMSTR3_SB.xls")
objXLApp.Application.Visible = True
'strFormula1 = "=IF(G1

I am trying to use one spreadsheet that is based off of another and have it all set up like I need it, but I cannot conditionally format anything because the data is based on another spreadsheet.

So my thought is to move everything to the same workbook, but is there a way to lock or secure the second worksheet so only certain people (with a password) have access to open or even view the worksheet?

Need to keep the second worksheet private from the users that are entering data in the "data entry" worksheet.

Does "Protect Sheet" block anyone from viewing it or just changing data (in testing this, it appears you can see the data, you just cannot change it, so this will not work)

Thought: Can I unprotect all the cells in the data entry worksheet and then hide the analysis sheet and then protect the workbook? Would the users have to enter a password to enter data or since the cells are unprotected or would they be able to just enter the data (need to keep it simple for them)?
So it looks like this will not even work as you cannot c-format between sheets.

Any thought on how to get a report to show this in access:


Data1..........2 (this is a count)
Data2.......... $629.78 (this is a sum)
Data3.........$3.94 (average)
Data4..........$729.45 (sum
Data5..........$4.56 (average)
Data6..........4 (count)
Data7 (you get the idea)

Sorry, I have tried this in excel (and it works great until I need to c-format) and in access I cannot figure out to to get the report to show months going across with other data going vertical.

Tried a crosstab query, but it seemed to only want to allow one set of values (count and sum area)

Any ideas would be greatly appreciated.

....How about copying values only to a new workbook and c-format there (but how would it stay current as new data is entered?) Sorry, getting tired, not thinking very well

I currently have 3 spreadsheets with simliar information. How can I take a database and export only select fields to make either a report or table?
Here is the example with types of info i will be using:

100 communities and all their roads. Each of these communities are in a borough, which is then in a region.

Then we have a mileage for each road.
Where I have difficulty is that we have another set of data that would be awesome if we could just include into this which is all of those roads but additionally they have:
Surface type/condition
Traffic count

Some roads have two classifications.. each of those having a mileage.

Does anyone have any pointers for where I could go with this? I would really like to see all of this data in a database that spits out the needed tables and totals.

Lastly I should ask if anyone knows anything about ArcGIS data being compatible with Access.. Most of this information will be in a geodatabase.

Thank you for ANY and all help.


Hi all-
I work for a staffing firm and I'm trying to figure out a way to turn exports from our vendor-based sql gui (i.e. I can only get .xls files out of it, can't just run SQL queries) into spiffy reports using Crystal Reports. My grand plan is to use Access to process these various XLS files (one for each client) by linking them to an Access DB and coding a table to conditionally tally individual records based on different criteria.

The criteria are company name, position name, date submitted, active, and status. I was able to code an Excel template that did the trick perfectly, but required each file to be edited to reflect the new report format, and updated as more positions are added. My question is this: is there some way for me to create a DB that links to the XLS files for each client and puts each company name, position name, date submitted (pulled straight from XLS files) and then the following tallies:

submitted (just # of pos name by date)
accepted (status != "s" AND active = "y" by date)

I don't really expect someone to do all my coding for me, maybe just point me in the right direction, as the only coding outside of excel formula coding (which I feel doesn't really count despite my proficiency) that I've done has been over a decade ago. Any suggestions?


I am new here and so desperate to ask anybody here to help me fix my database.

I need to create a report to count my Subjects if they only have the following date conditions:

StartDate: with date
EndDate: blank

and any Subjects with

StartDate: with date
EndDate: today's date or future date

I created a formula in my Query but i'm having errors:

Expr1: IIf([StartDate]"" And [StartEnd]="",1,0)

The above formula is just for #1 and it's already not working. I don't know how to create a formula for #2 or how to create formula for both

Please help!!!


P.S. I am using Access 2007

Hi guys, please help me.

I'm using access 2003, and I have a report that is contrained to 1 master record per page. Each page has a sub-report in the detail section. quite standard practice, I believe.

I'm trying to use the setValue macro to resize the TopMargin property of my controls in the sub report on the condition of a count.

For example, if the count of the number of records in the sub report is

I have a master table TEST_REPORTS_MT having fields:
Sample data is:
TEST_REPORTS_MT Report_No Product_Name Test_Date TR-1 Pr1 1-12-2011 TR-10 Pr1 5-1-2012 TR-2 Pr2 10-12-2011 TR-3 Pr3 15-12-2011 TR-4 Pr5 15-2-2012 TR-5 Pr2 18-12-2011 TR-6 Pr4 20-12-2011 TR-7 Pr1 22-12-2011 TR-8 Pr5 1-1-2012 TR-9 Pr4 2-1-2012
Detail table is TEST_REPORTS_DET having fields:
TEST_REPORTS_DET ReportDet Report_ID Serial_Nos Qty Result 1 TR-1 100,101,102 3 Qualified 2 TR-1 103 1 Fail 11 TR-10 20,21,22,23,24 5 In Process 3 TR-2 10~19 10 Qualified 12 TR-2 20~29 10 Qualified 4 TR-3 F1,F2,F3 3 Fail 15 TR-4 S1,S2,S3,S4,S5 5 Qualified 16 TR-4 S6,S7,S8,S9,S10 5 Qualified 17 TR-4 S11,S12,S13,S14,S15 5 Qualified 5 TR-5 P1,P2,P3,P4,P5 5 Qualified 6 TR-6 R1,R2,R3,R4,R5 5 R&D 13 TR-6 R6,R7,R8,R9,R10 5 R&D 14 TR-6 R11,R12,R13 3 R&D 7 TR-7 R6~R15 10 R&D 8 TR-8 F4,F5 2 Fail 9 TR-8 P10,P11,P12 3 Qualified 10 TR-9 F6,F7 2 Fail Requirement:
We need to count the Report_No from table TEST_REPORTS_MT group by TEST_REPORTS_DET.Result
There are 10 reports in total in TEST_REPORTS_MT table and count of them is as follows with respect to Result field in TEST_REPORTS_DET table.

Qualified Fail R&D In Process Qualified & Fail Mix 3 2 2 1 2 The query should be simple and do not contain UNION as the query will be imported in data environment of VB6 as a view and will be used as data source of report. Attached Files (26.7 KB, 2 views) Reply With Quote 03-10-2012, 01:54 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,117 One issue appears to be the pk/fk relationship of Result_ID between Results_MT and Test_Reports_Det. The ID is an autonumber pk but the fk is set as text. You have a query that attempts to join on these fields. However, Test_Reports_Det is saving the actual Result text, not the ID. This results in datatype missmatch error. The Result_MT table is not needed in the query. Remove it.

Also, don't understand your count results. I see the following in the data:
Qualified Fail R&D In Process Qual & Fail Mix 5 4 2 1 2
The Mix value is the most difficult because that involves evaluating data from multiple records. Review this

This nested query returns the above numbers except for the Mix value:

SELECT Count(Query1.Qual) AS CountOfQual, Count(Query1.Fail) AS CountOfFail, Count(Query1.RD) AS CountOfRD, Count(Query1.InProc) AS CountOfInProc
(SELECT DISTINCT TEST_REPORTS_DET.Report_ID, IIf([Result]="Qualified",[Result],Null) AS Qual, IIf([Result]="Failed",[Result],Null) AS Fail, IIf([Result]="R&D",[Result],Null) AS RD, IIf([Result]="In Process",[Result],Null) AS InProc

I have an access report that counts the number of "double" and triple rooms for a group of travelers. I optain the number of rooms by looking at each traveler and their room assignment. If it is a triple room, that means there are 3 people in that room so I count the number of travelers with a triple room assginment and divide by 3 to obtain the number of triple room I need. For a double room, there are 2 people in those rooms so I count the number of travelers with a double room assignment and divide by 2 to obtain the number of double rooms I need.

The Control Source code I am using for the double room is this


The Control Source code I am using for the triple room is this


This works great however I need to add an additon condition to the double room. I need to also add to count each room type of "DoublX" as an additional room. Not divide it by 2. I haven't been able to figure out how to add the double room/2 plus each DoublX room.

Any help you can provide is greatly appreciated, thank you.

Hello to all fo you,

I have one challenge task which is daunting for me . I have very large database ( 100000 records and counting)Access 2003 on my job and one table is append every day once we process the data from big American Bank Citi bank in this case .I have created a form that filter the data in the table( query in this case call qryCitiTotal2012). The qryCitiTotal2010 consists following fileds: CitiTrns,Code,DateProccessed, Debtor Name, Gross Amount,Net Amount, Comm Amount, GrossInv, FeesInv and NsfInv.
The user can run query and report accordingly by debtor name, date processed, CitiTRNS( it is 10 digits number) and code. The upper managements wants me to sum gross Amount, net amount and Comm amount based on GrossInv, feesinv and nsfinv. These fileds populated by me and they reflect name of oracle invoice that we booked in our accounting system.
I created text box on the form called txtGross and put there iif condition like this
" =iif([GrossInv]=0,0,sum[Gross Amount]))" and it returned 0.
The invoices fileds are not populated in every filed and it append accordingly the code:
code is 002 it belongs to Grossinv, if it end 003 it belong to Nsf).
For examples for one day it can be total 200 records and only 100 grossinv ,50 feeinf and 50 nsf invoice. The groosInv are the same for one day and the same with two fee and nsf fileds. They are all the same for one day.
So, I would like to sum gross amount for gross inv filed and the same with two others.
Should I create a second cross tab query to do that or there is another way to sum gross amount, net and comm amount?
Thnak you in advance,
The best regards,

Not finding an answer? Try a Google search.