Queries :: IIF Condition - Displaying All The Records Including Null
Aug 26, 2013
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 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 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 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.
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
I’ve created a report containing two labels and two text boxes. The “Record Source” for the report is Tbl_MasterList2. The control source for both text boxes is named field4 and field5, which are based in Tbl_MasterList2. As a result, my report displays all records from Tbl_MasterList2, field4 & field5 including those, which are blank.
How do I write an expression in my report that will allow me to display the values in field4 if filed2 = stringXYZ?
If you have a one - many relationship between data, normally in the table it is captured, you would find multiple rows for every 'thing', each row detailing different 'functions' performed on it.
Is there a way that one can display these functions horizontally, instead of vertically, i.e, have one row per 'thing', instead of multiple rows.
I want to run a query to show all my outstanding orders between two dates. I've done all this correctly and it works fine. However I only want the query to show me the records where the checkbox on 'tblOrders' is unchecked. For obvious reasons, I don't need to see these records, just that records that have been checked, therefore are outstanding.
I have a query that carries all the properties in our database, and data for when our company 'worked on' properties. Each property falls within a certain province and municipality (this is slightly irrelevant info) , we worked on properties in a period from 2009 - 2013/06/30 this period is called '2009', and we have again worked on properties during a period 2013/07/01 onward - this period is called '2013'.
Now I require this query to display all the properties, but only the entries in certain fields, that relate to '2013'.
I have about 7 fields where this date (either 2009 or 2013) can be displayed, I tried to filter them all simultaneously by using the criteria: Is Null Or "2013", in every one of these fields, but this criteria results in me losing entire records that contained 2009 data, instead of the data simply not being displayed. Even in cases where some of the 'date' fields contained 2013.
How I can keep all records but simply display info relating to work we did in the 2013 period?
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 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;
All. Using access 2010. I have a query that returns 92 records. When I put in the criteria for one field to leave out records with “approved” which totals to 9 records, the query only returns 10 records. It is not returning the records that are blank(not null) for that field. I want those records. Why is this happening and how can I get the blanks for this query?
I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
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 m trying to make form which filters my records and generates a report..here's where I am
Code: Like "*" Or Between [Forms]![Form1]![Text6] And [Forms]![Form1]![Text8] & "*"
but this doesn't work I would like to show all records if textbox 6 is null and textbox8 is null this part of code works perfect but below but I'm struggling to get the between in with the code
Hi. I am using Expression Builder to total 3 columns for a query.
Total: [BaseTotal]+[ClickTotal]+[Adj Total]
The [Adj Total] is sometimes empty, and when this happens, Total is empty even though there are values for [BaseTotal] & [ClickTotal]. How can I make the Total show up regardless of whether any of the three items used to calculate it are Null?
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.
My table has many records for each month. I am creating a report that will display only the records in one month of a year. I have been able to create a form that gives the user the choice of the Month and Year for which to create the report. The code I am using to create the combo box is:
Code: SELECT DISTINCT Month([QTDate]) AS MoNum, Format([QTDate],"mmmm yyyy") AS MoName FROM MainTBL ORDER BY Month([QTDate]);
What I need to do now is create the query for the report that displays all records for the chosen month and year. If I simply reference the combo box, all it shows is mmmm yyyy and the days are missing so the query doesn't work. What do I need to do to create the query so it displays all days within the month and date selected?
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 quotation database running in our company network which we use to send quotes to customers. A quote may have one or more items in it and each item has different prices based on the quantity. Each item will have a record in the database. So, if a quote has five items, there will be five records with same quote number and if 30 items, 30 records with same quote number.
Many times, same enquiries are coming from different sources (customers) asking quotes specially Govt orders. Now we need to take every enquiry as separate and feed separately in the database. Consider the time for a quote which has 30 items in it and each items has 6 level of pricing. When this same enquiry is coming from 12 different customers, it takes a lot of time to feed it in.
Now my question: How can I copy all the records belongs to one "quote no" and add it to the same table with a different quote number and some changes to the other customer related fields? If I can do that, I just need to change the customer address and I am ready with the second, then third and so on. I want to copy all related records, change quote number and related fields and then append to database.
Also I need this when I revise a quote. The system needs to keep the old quote as well as the revised quote for future reference. If I can duplicate it in one command, I just need to do the changes, Revision Number, Revision Date etc. and the revision is ready to go. Now I use update querries to do changes to all related records during revision. Then I will have only the revised quote in my database and when I want to refer the old quote, I have to refer the hard copy.
I know there is a "Duplicate Record" command available in form level (through wizard), but that duplicates only one record, not a selection of records as I need it here. If this can do with macros, fine. I am not an expert in VB, but if I get the code, I will attach it to a command button and use.
What I am trying to create is a Despatch database for our warehouse.
Records in table: Date () Customer Invoice # Qty of parcels sent Courier used consignment number
In most cases we will send one invoice per consignment number (database works fine for this) But on occasion we may send multiple invoices. What I want to be able to do is have a list box to select the number of invoices, this will make available additional fields for Invoice # and Qty of parcels sent. The idea is to get away from keying in the other records for each invoice going to the same place.
My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.
This is what I have so far:
Code:
Private Sub Command19_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control Dim varItem As Variant
