I need to run a query counting how many policies a client cancelled. But I need the query to include zeros. Is this possible?
Here's my SQL Statement currently.
SELECT DISTINCTROW Pqry_CancelledPolicy01.Number, Pqry_CancelledPolicy01.Name, Pqry_CancelledPolicy01.POLICY_STATUS, Count(Pqry_CancelledPolicy01.Number) AS CountOfNumber
FROM Pqry_CancelledPolicy01
GROUP BY Pqry_CancelledPolicy01.Number, Pqry_CancelledPolicy01.Name, Pqry_CancelledPolicy01.POLICY_STATUS
ORDER BY Pqry_CancelledPolicy01.Number;
I populate the table "date" field from a Microsoft Date and Time Picker Control 6.0(SP6). I havent been able to figure out how to ONLY populate the date portion of the control so my field value containes Date & Time.
When I run the query, I have to select the day after my request to include the records I need. I think this is because of the time in the field Example, If I want all records thru 6/17/2006 I must select "thru" 6/18/2006. This mucks things up if I have records dated 6/18/2006
Both of these statements give the same bad results:
Between [forms]![freports]![from] And [forms]![freports]![thru]
>=[forms]![freports]![from] And <=[forms]![freports]![thru]
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
I've got a database that tracks name/address/phone, etc for a large group of people. I've recently added the ability to include a person's photo on the forms and reports. I've done that by saving the photos with the naming convention of [firstname]_[lastname].jpg and then using VBA, the database checks to see if there is a file with the corresponding name of the record and displays the picture if so.
It works very well, but I can see where I would have some problems in the future. For example, if someone gets married and changes their name, the corresponding photo must also be renamed to display properly. And if there are any misspellings, that becomes a problem. Also, if James decides he wants to go by Jim, the photo would not be displayed, either.
I'm wondering if anyone has any other ideas on how I might accomplish my goal. I know I could name the photo based on the record ID, but that's not a good option, since I'll be handling the photos, while someone else will be inputting the contact details. So I won't know the recordID easily.
Is there some way to have another field in each record that points to the corresponding photo? Then if James goes by Jim, his first name can be changed, but the corresponding picture name could remain the same.
Is there some great idea that I'm missing that would make this process much simpler and scalable? Or do I just have to deal with the maintenance issues?
Can anyone tell me why this sql is not returning all fldProjectID's for selected fldProvinceID regardless of whether a date exist for that fldProjectID in fldReportRec_D??
SELECT tblProject.fldProjectNo, tblProject.fldTitle, tblProvince.fldProvince, tblReport.fldReportRec_D AS [Monthly Status Report] FROM (tblProvince INNER JOIN tblProject ON tblProvince.fldProvinceID = tblProject.fldProvinceID) LEFT JOIN tblReport ON tblProject.fldProjectID = tblReport.fldProjectID WHERE (((tblReport.fldReportTypeID)=1) AND ((tblProvince.fldProvinceID)=[Forms]![Form1]![cboProvince]) AND ((Month([fldReportRec_D]))=[Forms]![Form1]![cboMonths] Or (Month([fldReportRec_D])) Is Null));
I currently have a query set up to pull information from 4 linked tables: Event, Readers, Class and School. The Event table contains a ClassID and a ReaderID. If everything has been assigned properly, there's no problem. If there's no reader assigned to ReaderID I still want the event details to be included in the output. Right now it's not. I'm pretty new to Access so if anyone can help, that'd be great. If you need to be try to explain further, let me know.
Hi I have set up a form to prompt for start and end dates so that users can input their own dates which will return a report that is based on a query. However if they enter 01/04/07 to 30/04/07 it doesn't include the last date. I have tried putting the following into the query Between [forms]![formname]![cbostartdate] and CDate[forms]![formname]![cboenddate]+#23:59:59# but I keep getting the following message when trying to access the form.
The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
Hi! I want to add two columns a and b. However, there are rows in which EITHER a OR b is NULL. This should be handled as if the cell would be zero. Is there any way besides filling in 0s in the tables?
I tried SELECT a, b, IIF( a = NULL, b, IIF(b = NULL, a, a+b)) AS c FROM
I am very much an Access novice, and use the expression builders for my queries. I have a query based on a table and another query. The table lists station names and station numbers. The "base query" includes station numbers plus other data, so I join the two in order to display the station name.
I want to run a query that lists every station by name, then for each station tells how many occurrences of a condition are met in another field for that station. Problem is, if the condition is not met, the station is not listed in the query output, and I need to see the complete list of stations, even those that do not meet the condition.
Here's what i would like to get if there are a total of 3 stations in the population:
Station Name #of Blue Items Station 1 100 Station 2 1 Station 3 0
Instead, I get:
Station Name #of Blue Items Station 1 100 Station 2 1
Part of a database I'm developing stores details of events. Each event is funded from a variety of different sources.
There are three tables involved in making this work: tblEvents - which stores the main event details tblFundingOptions - which stores all the possible funding sources tblEventFunding - which links the two together.
I have created a form to access all of this, with the information from tblEvents at the top and the Funding details on a subform.
I have created a query based on tblEventFunding which adds up the total amount of funding allocated to each event. I want to display this total on the form I've developed.
Can I do this without creating a second subform to pull the details in from the Query? I've tried creating a text box and linking it to the appropriate query, but it just displays #Name! - presumably because it doesn't know which record from the Query it should display, and I don't know how to tell it!!
Is what I'm trying to do possible, or do I need another subform?
I want to be able to copy, or duplicate a record including all the associated records of the subform. The structure is the typical, ORDERS, ORDER DETAIL, PRODUCTS. I want to be able to copy a specific ORDER and generate a new one with all associates products. It would be nice to change one field (ORDER NAME), changing the ORDER name would simulate the "Save As.." command.
The end result would be a new Order identical to the first one (including all sub items) but with a different name.
I have a table of events that includes fields for ages, a Start Age and a End Age.
I want to make a query that pulls up how many programs have been provided to a given age group. How can I make it so that the query will understand to include records that have the given age group in between the Start Age and End Age fields? I.e. if I set the query to 3rd Grade, records that start at 2nd Grade and end with 4th Grade are included in the results as well.
I would like to start keys for different suppliers with different letters. For example a company named ABC would have product keys starting with a and then a series of automatically installed numbers. That is the series of keys would be a1, a2, a3, etc. and then a company named Village woud have keys v1, v2, v3, etc. How can I do this without having to manually install thousands of keys?
I am trying to save my form that i have created as a report by right clicking on the form and choosing save as report. However, my form includes a subform but the subform does not show up in the report. Any ideas as to why this and how it can be shown????
I'm looking to simplify some aspects of our company's order database, including standard notes that appear on the sales orders that we send to customers. We have different lines of business and the notes vary. We currently have several reports, depending on the type of order, with the notes hard coded in labels. It's not an elegant, nor scalable, solution.
Our ideal solution: One order report only, with an option for the user to select one or more notes from a list when they enter the order. I'm thinking a popup form with a checkbox next to each note, where each checked note will print on the order report.
I have a query that pulls scores for this month only for each class member. Problem is, I only need a count of these scores (per person) and because the date is in the query, it doesn't group the scores together and count them as one. i.e. it sees score 1 and score 2 as separate because they have different dates so they won't count together.
The only purpose of date in this query is to filter out only this month's dates. Is there some advanced query expression something or other that will tell it to leave date out of the count and only use it to filter?
I have a columns that do not incur a count value within the time period of my query, yet i still want them to be displayed with a 0 or no value in the query view, how do i do this?
I have a database of film events, for which i have to do posters.
With the click of a button on a form, access opens word and puts all the fields in, including a field called [photo], which is the name of the image stored outside of the database.
Is there a way of getting that image into the word Document with all the other fields? Or have I just wasted an afternoon....
I am running an Access 2003 report that outputs to an Excel Spreadsheet The parameter query has two paramerters First Date and Last Date. The report runs from an Button OnClick event. I need to include the two dates in the 'name' of the spreadsheet as below
Private Sub btn_report_between_dates_Click() DoCmd.OutputTo acOutputQuery, "qry_all_calls_between_dates", acFormatXLS, "Calls By Between Dates " First Date" and " Last Date" - Date Report Run " & Format(Date, "dd-mm-yyyy") & ".xls", True End Sub
(btw I know it is preferable to use the TransferSpreadsheet method, but I've not got around to that way yet)
I want to list all the records which contains data as per my filter form field company.
I have tried this
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*","*" & [Forms]![Filter]![COMPANY] & "*")
problem is this i want to display all the records including null, but this code only show all records except null
Like IIf(IsNull([Forms]![Filter]![COMPANY]),"*" here we have to add codes for null values also but i don't know how ,"*" & [Forms]![Filter]![COMPANY] & "*")
I have an Acess database (2013). This will be used by several people within one company, they don't have their own server. I would use SharePoint Online, but it seems to me, that you only can "export" the tables. The database I have is quite a complicated system, and it is neccessary to use all the Access tools - forms, reports, queries. Not tables only.
Another idea is to split the database and "store" the backend part (the tables) on Sharepoint (Office 365 online version). And all the users would have their own frontend (forms, queries...) on their own PC, linked to this backend. Would this work? Can everyone add some entries into the database this way? How does it "synchronize" then?
User Query #1: Notes: ReportStartDate will always be entered as the first day of the month; ReportEndDate will always be entered as the last day of the month The report will always show 6 consecutive months
User selects ReportStartDate = 7/1/12; ReportEndDate = 12/31/12
Notable conditions: -The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0. -The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value. -The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.
Request: I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.
I have a Form with multiple comboboxes and listboxes whose selections are assembled into a query. The combo-box selection goes into an IF-ELSE statement for a selection check (IsNull) and if there is no selection made, it is supposed give me all values (Blanks & Non Blanks)
Here is a sample of my code:
Code: If IsNull(Me.cbReg.Value) Then RegStrng = " Like '*'" Else RegStrng = Me.cbReg.Value RegStrng = "= " & RegStrng End If
I have several If-Else statements here and a final query assembly at the bottom of the code page which is as follows
Code: MasterSql = "SELECT DISTINCT blah-blah-blah" & _ " INTO some more blah-blah" & _ " FROM even more blah-blah-blah" & _ " WHERE dbo_mytable.[Reg#]" & RegStrng & _ " AND the results from other If-Else statements similar to above"
Here is where the problem comes in:
I see the mistake in my If-Else statement
Code: If IsNull(Me.cbReg.Value) Then RegStrng = " Like '*'"
Like * means it will show me all rows where there are NON-Blanks. However, it skips all Blank Data.
What should the If IsNull() statement look like if I want to show all the values?
If there was only one combo-box and there was no selection made, then the resultant query should show me all results rather than only the results where there is some sort of data within the column filtered by the combobox.
I have an autonumber field (CarNo). This field is to be displayed as a 5 digit number on the form, ie: 1 is to display as 00001. I know I have done this before (I only use Access once a year or so, so the mind is not fresh on this) I can use a text box on the form because the user can't change the field, but how to display the autonumber as a 5 digit number, including leading zeros. I found the code of Format(FieldName, "00000"), but don't remember where to put it.
