Reports :: Multiple Counts In One Expression?
Oct 31, 2014
I have a report that I have called "0 Master". It details the number of complaints that my company has received, broken down by a specific service.
I have a CountIIf that returns the number of complaints received in a certain category. This works - but this only shows the total number of complaints received for that specific category.
That formula is =Count(IIf([Contact Category]="Stage 1 Corporate Complaint",1))
I have also got a formula that tells me the total number of those complaints that are outside of the deadline (regardless of category).
This formula works and is;
=Count(IIf([Date Response Expected]>=Date(),1))
But - I want to combine these 2 formulas to tell me the number of complaints outside the deadline for a particular category - I've had a go myself and anything I do tends to return either nothing or -1
So far, I've tried various incarnations of;
=Count(IIf([Contact Category]="Stage 1 Corporate Complaint",1)) And (IIf([Date Response Expected]>=Date(),1))
View Replies
ADVERTISEMENT
Mar 4, 2015
I have a list of dates and I want 1 query which counts the dates between numerous criteria. for example, colum 1; dates between 1/1/14 and 16/02/2014. Colum2 between 17/02/2014 and 15/04/2014.
I have attached a screenshot.
View 4 Replies
View Related
Sep 4, 2013
I need to produce a Data Integrity report that lists the users of a separate database and the count of errors that they make, separated by error type.
Currently my query displays all the users who made errors, and the total errors they made. But I don't want the total - I want to break this number down by the types of errors that are made.
I modified the SQL to make it easier to read. How can I take the below statement and make a few expressions that count up the specific values? The field I need to dissect is "Type Error" and a couple of examples of error types are "A1" and "B1"
Code:
SELECT DISTINCTROW [Errors Table].User, Count([Errors Table].[Type Error]) AS [Errors]
FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC
WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))
GROUP BY [Errors Table].User
ORDER BY [Errors Table].User;
I've seen it done with multiple queries joined together, but I'd like to avoid that if possible...
View 5 Replies
View Related
Jul 21, 2013
in my daily roll call report i have 2 groups..."on program" and "graduates" these 2 groups are creating in the query...as u see in the 2nd pic... the expression as followed
Expr1: IIf([Date Graduated]<Date(),Date(),[Date Graduated])
next you can see in my 3rd pic the report and the expression that gives the 2 groups there names...it is as followed
=IIf(IsNull([Date Out]),IIf(IsNull([Date Graduated]),"On Program","Graduates"),"Recent Departures")
i will clarify that i took out the names in the roll call but both groups are sorted by the date they came in going down the list..now i need to add another group "Staff Members" to my roll call.every way i have tried altering the query expression or the report expression result in a blank roll call.
View 14 Replies
View Related
Aug 8, 2011
I work for a landscape company and I am trying to create a customer specific report by linking only the services each client receives, leaving out the services they dont receive, we have over 50 different types of services we offer. I can get the expression to pull the correct information separately but I don't know how to build the expression together. Each expression is listed below.
=IIf([Call List- Cut Backs.2010 Season Status]="Automatic","Automatic Cut Backs",IIf([Call List- Cut Backs.2010 Season Status]="Call Every Year","Call Every Year Cut Backs"," "))
=IIf([Call List- Annuals.2010 Season Status]="Automatic","Automatic Annuals",IIf([Call List- Annuals.2010 Season Status]="Call Every Year","Call Every Year Annuals"," "))
View 1 Replies
View Related
May 11, 2015
I've been struggling with the following expression:
=DSum("[FieldName]","TableName","[FieldName]>=" & [Forms]![FormName]![textbox01]& " and " &[FieldName]<= " & [Forms]![FormName]![textbox02] & "")
I keep getting invalid syntax (Access 2007). I tried adding the # symbols but no success.
I also need to put an additional criteria for a Integer field in the same expression where the value is True.
View 5 Replies
View Related
May 21, 2014
I am trying to sum fields if a another field (Accurate/Complete (30 pts)) is less than 30
Right now it works to provide my total if the yes/no button for a certain field has been clicked.
=Sum(IIf([Procedure Error],1,0))
now I want it to only total the numbers if
[Accurate/Complete (30 pts)]<30
How would I add the if less than to the top expression?
View 3 Replies
View Related
Dec 18, 2014
I am using expression builder to specify a field in a report but it is acting more like a filter.So I have a report based on a query. However I want to add a field that is not in the query but is in a related table - called tblAgent.
So using expression builder I select the tblAgent in Expression Elements and then select the field from this table. This creates the expression =[Agent]![AgentAddress] however when I try to run the report it asks for a Agent parameter? Do I need to go back to reports 101?
View 1 Replies
View Related
Jun 8, 2015
I have an Access application that print a report with 7 sub-reports in it. When I preview the report, it looks fine. When I print it directly to the printer, it looks fine. The problem is when I print from the preview, Access error with a "...too complex..." error and crashes the application.
View 8 Replies
View Related
Sep 3, 2013
I have been stuck trying to write an expression that will calculate the Yes values in a range of 16 Yes/No fields.
I have attached a screen print of the report I am working on.
View 5 Replies
View Related
Jul 4, 2013
I have a report that comes from a query with multiple expressions. The expressions look something like this:
IIF([TimeArrive]>[TimeTriage],DateDiff("n",[TimeArrive],[TimeTriage])+1440,DateDiff([TimeArrive],[TimeTriage]))
This works great and my report gives me my columns for each expression in minutes they way it should. Now here is the question...is there a way to insert a text box for each column/expression so I can calculate the Average for each column/expression?
i.e. I want the report to show the average minutes of Expr1 and Expr2, etc at the bottom of each column.
View 2 Replies
View Related
Jan 12, 2014
I have the following expression as part of the recordsource for a report:
Is it possible to put a mask on the output of [phone1]? Ideally something like: (716) 555-5555 x1234
Code : phone1: [ContactNo1] & " " & [ContactNo1_ext]
View 10 Replies
View Related
Jul 20, 2006
Hi everyone,
My query is coming along nicely, but as always once one problem is solved you find another :rolleyes: !
My problem is that I have thus far specified criteria for the field OrdDeliveryCountry, but this field is not filled in unless the delivery address is different from the default address for the customer, therefore it is frequently blank and so the query wasn't finding all records, only those where the Delivery Address was specific to the order.
I want to use the IIf function to make an expression to say (in linguistic terms):
If OrdDeliveryCountry is blank, then use the country in the Customers table.
Sounds simple enough, but the criteria currently is:
WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")
AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
AND ((ORDERS.[DEMO/SALEID]) = 2))
So how do I combine the IIf(expr,truepart,falsepart) with "Is Not x Or x Or x"?I.E. I need to get it to exclude records where OrdDeliveryCountry does not equal one in the list, and if that is blank then the Country field in the Customers table does not equal one in the list?
My attempt is this, but I think I'm way off the mark
SELECT ORDERS.SHIPDATE,
PRODUCTS.[STANDARD TARRIFF NUMBER],
[ORDER DETAILS].[QUANTITY] * [ORDER DETAILS].[UNITPRICE] * (1 - [DISCOUNT]) * (1 - [SPECIAL DISCOUNT]) AS LINETOTAL,
[ORDER DETAILS].QUANTITY,
ORDERS.ORDDELIVERYCOUNTRY,
ORDERS.ORDERID,
[ORDER DETAILS].PRODUCTID
FROM CUSTOMERS
RIGHT JOIN (PRODUCTS
RIGHT JOIN (ORDERS
LEFT JOIN [ORDER DETAILS]
ON ORDERS.ORDERID = [ORDER DETAILS].ORDERID)
ON PRODUCTS.PRODUCTID = [ORDER DETAILS].PRODUCTID)
ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
WHERE (((ORDERS.ORDDELIVERYCOUNTRY) = IIF(ISNULL([ORDERS]![ORDDELIVERYCOUNTRY]),([CUSTOMERS]![COUNTRY] NOT LIKE "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden"),
(([ORDERS]![ORDDELIVERYCOUNTRY]) NOT LIKE "Austria"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Belgium"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Cyprus"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Czech Republic"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Denmark"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Estonia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Finland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "France"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Germany"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Greece"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Hungary"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Ireland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Italy"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Latvia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Lithuania"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Luxembourg"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Malta"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Holland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Poland"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Portugal"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovakia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Slovenia"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Spain"
OR (ORDERS.ORDDELIVERYCOUNTRY) = "Sweden")))
AND ((PRODUCTS.PRODUCTNAME) NOT LIKE "*Upgrade"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Repair"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Rpr"
AND (PRODUCTS.PRODUCTNAME) NOT LIKE "*Commission")
AND ((ORDERS.[DEMO/SALEID]) = 2))
ORDER BY ORDERS.SHIPDATE DESC;
My thoughts:
Maybe I need to re-structure the WHERE clause?
Would it work if the IIf expresssion was in the SELECT part not the WHERE part?
I would really appreciate some help with this: I'm not sufficiently familiar with structuring statements as complex as this and I don't know all the syntax rules etc.
Thanks in advance!!
View 2 Replies
View Related
Aug 26, 2013
I am trying to set up a calculated field in one of my form querries, using expression builder.
In one of my source tables I have 4 date fields called Inspection date 1 to 4
I need the lowest date among those inspectinos which are in the future (next inspection) If all 4 dates are in the past or Nulls, the function should return the current date. The logic of the expression could be:
MIN(MAX(D1,Date()),MAX(D2,Date()),MAX(D3,Date()),M AX(D4,Date()))
How can I do this in expression builder?
The built-in functions DMin and Dmax work with single field arrays, witch would be perfect if the database were properly designed, but now I dont have the power to change this.
View 1 Replies
View Related
Oct 2, 2013
I have been an MS Excel man all along my career and I am a novice in MS Access.I have created a table, [Initial Customer Approval] which records data from a Form, [Initial Customer Approval]. Once the data is entered in the Form, I need to do some calculations based on the data entered in some of the fields in the form.I created 6 different queries for the six possible values in those fields. now for each of those queries I created respective reports.I placed a Print command button in the Form.
1. When I press the Print button it should open the report for the current record in the Form. (Currently It Opens all the reports simulatneously, with only one relevant report containing the current record; other opened reports being blank.)
2. If user presses the Print button before pressing Save button then system should prompt user.
Here is the code (Please note [reference number] is the unique ID generated for each record entered in the tabe through form):
Private bSaveClicked As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not bSaveClicked Then
MsgBox "You are trying to navigate away from the active record. Please either save your changes, or press ESC to cancel your changes.", vbOKOnly + vbInformation
Cancel = True
[code]...
View 5 Replies
View Related
Nov 23, 2013
I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.
I am having issues figuring out how to get it to pass them to a query or filter the reports.
I have tried many different examples and nothing seems to work.
View 4 Replies
View Related
Nov 4, 2013
I am trying to pass a date range parameter & an additional parameter (Type of Audit) to 6 subreports based off individual crosstab queries and housed on one Unbound Report ("rptFinal").I have an Unbound Form "frmDate" passing a date range and Type of Audit using [Start Date] and [End Date] to rptFinal with a button that simply opens the rptFinal as follows:
stDocName = "rptFinal"
DoCmd.OpenReport stDocName, acViewReport
In each Crosstab query, I have set the parameter criteria (in both the Parameter section & the query itself) to:
[Forms]![FrmDate]![Start Date] And [Forms]![FrmDate]![End Date]
and also,
[Forms]![FrmDate]![Enter Type of Audit]
On each subreports On Load Event, I have added:
Private Sub Report_Load()
Me.Filter = "[DteAuditDate] BETWEEN #" & Forms!frmDate![Start Date] & "# AND #" & Forms!frmDate![End Date] & "#"
Me.Filter = "[Type of Audit] = #" & Forms!frmDate![Enter Type of Audit] & "#"
End Sub
and I've set the Filter On Load property to: Yes..I can open frmDate, fill in the date and Type of Audit, launch the report and it runs with no error, I have 6 blank subreports in report Preview. The headers are showing up but none of the data.
View 2 Replies
View Related
Feb 27, 2015
I have a small database for producing various financial reports, by date period (from/to). It works perfectly except when there are either no payment records or no receipt records for the chosen period. Naturally enough, MS Access comes up with the message "Database engine does not recognize 'payment' as a valid field name or expression" --- !!!
Is there some way I can tell MS Access that I don't mind if eg the payment column result is zero?
The structure of the table on which the report is based (via a crosstab query) is :
transaction date
auto number ID
transaction type (either payment or receipt, chosen by form's drop down box) - TEXT
amount - CURRENCY
receipt type - TEXT
payment type - TEXT
fundno - TEXT
The crosstab query design is as per the attached jpeg file
View 2 Replies
View Related
Jun 12, 2014
I am trying to create an expression in a query to sum only the # of hours a student attended between two date fields. I do not want to use a parameter because each student has different start and midpoint dates and I need to see all of them in one list.These are the fields I'm using in the query:
Student Name
Start Date
Midpoint Date
Hours
It keeps giving me "0" or if I move the () around it says the correct syntax is [NOT]
Expr1: Sum([Hours]) between [Start Date] & [Midpoint]))
View 14 Replies
View Related
Apr 26, 2015
I have created a report which contains within it multiple sub-reports, which I use to generate a document for management meetings on a bi-weekly basis.
For each of these I have the subreports filtered to a unique number for consideration that period e.g. LIKE "88/00039" which relates to my data.
In order to change this I have to manually update each of the filter commands within the subreports but I assume there must be an easier 'catch-all' method of achieving this?
Ideally I'd be looking for a command prompt so I could enter just the number sequence e.g. "88/00040" and then enter this (via a corresponding macro or similar) to update the filter commands.
View 2 Replies
View Related
Mar 30, 2014
I have four forms named [Information Form], [Evidence Form], [Subject Form], and [Vehicle Form]. The Final Form is a Tabbed Form named [New Incident]. The tabbed form has four tabs, with each tab displaying one of the forms above. I also have a Print Command Button named [Print All] underneath the tabbed portion of the [New Incident] form.
Also, All of the four forms are set to data entry - yes, so that on open they will be clean and read for new record entry. I need the [Print All] button to do, just that, print everything that has or has not just been entered into the four forms, even if some are printed blank because they were not necessary for this particular report. One thin of note is that each form on the tab has a field for the "Incident Number" which will be the same for all four forms.
View 12 Replies
View Related
Nov 18, 2013
I am trying to add to a db I inherited. One of the end reports that is produced is a cost breakdown for each end user.As things stand, the data collates into individual reports which are then grouped into one file and saved via PDF. What I am trying to work out is whether or not I can selectively pick some of those reports to have more than one copy.
I see a form (within an existing form) that will list all of the end users for a particular scheme and, next to that, be a dropdown that will allow the db user to select how many copies of each report needs to be published. These will then collate merrily into one document to be saved to PDF.
View 8 Replies
View Related
May 2, 2014
I have a queries that do all the calculations and dumps the output to Query X for all different types of customers. At the moment I am required to generate a report for each of the customers and send it to them, manually.
End Goal:Initiate a Macro (at a given time at a given frequency) that would run a process to generate different reports for all different types of customers using a standard report template. I am also trying to avoid having to create a report for each customer (as the customer base grows, the report count will grow) so looking at something that would look into Query X and generate # of reports depending on number of customers.
View 6 Replies
View Related
Feb 28, 2008
Hi everyone
I'm a very very new access user so many apologies in advance for when I have no idea what I"m talking about.
I'm working on creating a report that will display multiple expiration dates. Currently I have an employee database, not created by me, that has all of our employees professional licenses listed.
Prof license, auto license, liability, etc.
I want to create a report that will tell me what has already expired or will expire in the next 30 days.
I did use this
Between DateAdd("d",-30,Date()) and Date() and it is bringing back info up to 2009. which isn't what I need. I'm sure I'm doing something wrong here.
I want anything that has expired regardless of the date from today, before today, and 30 days from today but I don't care about anything more than 30 days from today's date.
I noticed that when I looked at the existing database that the fields are set as text fields. I tried to change them to dates and it gave me a "deleting 106" records error message. Yikes!
Any help would be appreciated. I don't really understand expressions and I don't understand if I'm supposed to put the actual date in parentheses or what. Please pardon my ignorance.
Many many thanks.
View 14 Replies
View Related
Jan 5, 2015
Every month, I create 15 individual reports with each report filtered by two fields, let's say Dept and Exec. You change Dept and Exec via a combo box drop down based on a table, and this information is passed to the query behind the report. Now, I repeat this process for every report. I use an unbound form that gathers the "Title", which is the Dept Name +"Special Report"; report Date using "as of" [DATE], and an updated through [Second Date].
Each monthly set of reports is sent to a new directory and folder (i.e. M:MOR Reports2005January. This changes each month. Somehow, I'd like to automate this where each report prints with its name to a newly created folder in pdf format.
I have been reading prior posts and am coming up with some ideas like adding a "selected" field to the Dept/Exec table and then step through those selected records to determine which report you are running. It takes a long time to print these to pdf in a new directory every month.
View 11 Replies
View Related
Oct 25, 2013
I wanted to print multiple reports using for loop but I am not sure how to start with.
This is basically my idea:
1. Create a hidden indicator, I name it txtHidden.
2. I have one table, there are one column for "report name".
3. I have one query, filtered the "report name" column according to txtHidden, criteria written in the design view.
4. Using For loop, browse through all the existing report.
5. Every loop, txtHidden will equals to every reportItem.Name. Query will run.
6. If Query is no result, dont open report, if query returns some result, open the report.
I wanted to code something as below, but I dont know what to put in the [Dont know what to type] field...
For Each [Dont know what to type] In Access.Reports
[Forms]![frmStartup]![txtHidden] =[ Dont know what to type] .Name
If DCount("*", "ReportQuery", "") > 0 Then
DoCmd.OpenReport [Forms]![frmStartup]![txtHidden], acViewPreview
End If
Next
Can ignore the query part. It is working fine.
View 4 Replies
View Related