Displaynig All Records Including Those With Null Value
Sep 18, 2007
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
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 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.
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 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?
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
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]
Been taking abreak from Access for a few years now and cant get my head round my problem.
I have been asked to manipulate data from a Training Recods database.
Basically, training consists of 26 modules, there are currently 180 people who need training. Each person has been assigned a unique number, once a module has been completed, the date it was completed is entered into the database. There are currently lots of modules which havent been completed, what I need is a way of pulling that data from the DB.
I need to know how many people need to complete each module. For example, Module 1 might need to be completed by 15 people, Module 2 might need to be completed by 27 people etc.
I have enclosed the DB with the table and the form that I am looking to populate with the data.
Anyone have any ideas, I just can't get my head round it!!!!
I know how to count records so that null records would be counted but I do not know how to do this:
I have customers and works. I want to create a query that will show ALL customers and number of works done for that customer (sometimes there is no work done for customer).
I have just joined the forum and spent the last day or so looking around for an answer that I wasn't able to find.
I have been asked by my employer to capture details of who has signed our confidentiality policy. I have made a very simple database which only has three tables, EMPLOYEE, POLICY and VERSION.
EMPLOYEE has three fields, NAME, NUMBER and BRANCH. POLICY has three fields, NUMBER, DATE and VERSION. VERSION has two fields, VERSION NUMBER and DATE. I won't worry about the VERSION table any further in this post as it doesn't figure in the problem query.
Primary key for EMPLOYEE is NUMBER. Compound key for POLICY is NUMBER and DATE (ie. the employee can sign the policy more than once and we want to record when this was, and whether the policy version changed).
I have an Access "type 3" join defined in table relationships from EMPLOYEE.NUMBER to POLICY.NUMBER.
I am trying to write a query that returns the employees that haven't signed the policy. I have no problem returning the employees that have (ie. employees whose numbers match the numbers in the POLICY.NUMBER field), but it is the EMPLOYEE.NUMBER entries that are not present in POLICY.NUMBER that are causing me grief.
I have tried using the expression builder in the query builder window (it gave me <>[tblPolicy]![Number]) which doesn't return any records (I have over 490 in the database and should be getting back at least 50 or so).
I have a form for booking employee holidays and in that I have a subform that displays their current holiday entitlment, which work great until I enter in a new record on the main form to book some holidays and I'm ask for a Name from a parameter box?? which I press cancel or ok at which point the subform goes blank (which is whatI want) but when I enter the employees name from the combo list it doesn't update the subform?
my question is; how do i remove the parameter box and update the subform once I have selected the employee name??
I am trying to create a Membership report that shows member activity over several months.
My problem is that the software that gives me the Member Activity does not report on accounts that were not used during that month and some members do not come in every month.
I have a Member's Details table as well as individual months activity and have queries set to show the results of each month individually (these work, obviously)
The Reports I am trying to get from Access only show the activity of Members that have some movement every month, but totally skips members that are not present for 1 or more.
The report uses these queries but fails to report any member that is not in the tables for all months.
Example:
September Rank Member FirstName LastName Activity 1 100 Andy Todd 200
October Rank Member FirstName LastName Activity
November Rank Member FirstName LastName Activity 5 100 Andy Todd 50
The report needs to show: Member FirstName LastName Sept.Rank Sept.Activity Oct.Rank Oct.Activity Nov.Rank Nov.Activity but will not because there are no values for that member in October.
I am teaching myself Access as I go, so I am sure there is an easy solution.
I recently acquired a database that is already established. When entering new date the primary key is null. I've looked at the properties and the best I can figure is it has something to do with a missing property called New Values.
Ok this null value thing is killing me. I have a parameter query that works great. I know if I use Is Null in the criteria field it will show me this. The problem is I made a search form which the users type the value in and it opens a form based on the parameter query, the parameter on the query is [Forms]![Search_frm]![txtClosed]. If they type a date in here it opens the form and shows the user all the closed records. The problem is they want to see all the records that have no value or Null. I've tried eveything to make this work. I'm ready to jump!! Just kidding. Is there a way to do this with out creating another query. Thanks a bunch!!!
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delte the records which are already null . i tried delete query but it did not work
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delete the records which are already null . i tried delete query but it did not work . This is in microsoft Access.
Depending on the answer of a question, i would like to get the records that have no data in that specific field, otherwise i want the query to give me all records. I've tried this in "criteria" but it doesn't work: IIf([Question? J/N]="N";"Is Null";"")
I have a form that using a combo box to select specific record, sometimes we need to see all records, i would like if the combo box if left empty to return all records. I typed this expression but it returns no records when combo box is empty.
If i use this expression i get the wrong results when a Loan Officer ID is enterd; LIke([Forms]![FrmReports]![LoanOfficer] & * Example of wrong results, if Loan officer Id is 1, then it returns loan officers 1,11, 111
I'm working on a db logging replies to a questionnaire. Judging by the responses I am receiving it appears that some sections are not completed at all. I need to be able to include these blank responses when it comes to analysis. My design splits the questionnaire into sections, each section has a data entry form with its own underlying table. Each table has a primary key (autonumber field) which relates to each organisation that has replied. If an organisation has failed to complete a section, I still need to create a new record in that section(table), triggering the autonumber field, hence referring back to the organisation. I know I haven't explained this very well, but if anyone can make sense of what I'm saying and can give me any suggestions on how to make this happen, I would be most grateful.
Hi. I'm unable to work out how to set up a query which will filter records according to what is returned by one control, but will also list all records if the value of the control is null.
Situation: I have a CONSULTATION table linked many to many via a CONSULTATIONLIST relation table to an ADVISOR table. (An Advisor may be present in any number of Consultations, and a Consultation may include any number of Advisors.)
I have a CONSULTATIONMANAGE form enabling the user to select an Advisor from the table ADVISOR via a combo box called SELECTADVISOR which returns Advisor ID (adID).
My CONSULTATION form, displaying the list of consultation records, currently has, as record source, the following query:
SELECT Consultation.*, Advisor.adID FROM Consultation INNER JOIN (Advisor INNER JOIN ConAdList ON Advisor.adID = ConAdList.adID) ON Consultation.conID = ConAdList.conID WHERE (((Advisor.adID)=[Forms]![ConsultationManage]![selectadvisor]));
This query correctly lists only those Consultations in which the selected Advisor was present.
Problem: What I also need to do is show ALL Consultation records if the user does not select an Advisor from the combo box.
Simple query, but I need to roll the results up with others and a null return is hurting me. I have been chasing my tail around with the nz(), input masks, IIF statements, only to prove my ignorance and possible insanity for expecting different results.
Here we go:
SELECT Count([LDB LOG].DSTHTL) AS ["tot120"] FROM [LDB LOG] WHERE ((([LDB LOG].[LDB Rcvd]) Between Now()-120 And #1/1/2008#)) GROUP BY [LDB LOG].[T-7 Date] HAVING ((([LDB LOG].[T-7 Date]) Is Null));
This is being used to count open items into age buckets, the above is the greater than 120 days (discounting anything prior to 2008). If the T-7 Date is null the item is open. What is happening is that no records are being selected, resulting in the null value back.
Any help would be greatly appreciated as I have spent an unrealistic amount of time on this.
I am looking to collate marketing data from different areas of our DB into a Marketing Hub. We have to collect various feedback at different times, if a client on a programme completes a course, they give feedback for that course.13 weeks after a client completes the programme we have to get them to complete a destination survey. On both these forms it asks would they be interested in doing any more courses. This data is then stored in the Courses table and Projects table respectively.
I want to create a query to become the record set for a new form which only shows those students that have stated they would be interested in doing other courses.Five of the options or Yes/No fields, and one is a text field (which courses, other essentially).How does one do a query that only shows records where any one of 6 fields is not null?
Code:
SELECT Students.ID, Students.[First Name], Students.[Last Name], Courses.[Sage Accounts], Courses.[Sage Payroll], Courses.[First Aid], Courses.[Food Hygiene], Courses.[CV Services], Courses.[Interview Skills], Courses.[Which Courses] FROM Students INNER JOIN Courses ON Students.ID = Courses.ID;
I have a table with a multi-field unique index: PatientID TestId TestDate Examiner
The problem is, date and/or examiner can be blank until that information is updated. I want those null values to be understood as actual values so that multiple instances of that "waiting to be updated" record do not occur. Is it possible for Access to understand my indexes in the following way?
