I am attempting to run a query that only captures the fields that are null so that I can
run a report that shows outstanding information still required.
Essentially, each record is a provider. I have reviewers that complete an Access Form to
certify each provider. The form consists of approx. 120 checkbox criteria. If it is checked
they meet that criteria...unchecked they do not. The form is based off of 4 tables of which
I am querying from.
I'm trying to get a query to identify only those criteria that are unmet for each provider.
In other words...only show the unmet fields/checkboxes. From that I'll make the report.
Hi, This query will allow me to view payments that are made between 2 dates. I would like to know how to flip the query around so that it gives me the payments that have not been made. I think this would be described as returning the null values?
The SQL code i have at the momnet is: SELECT download20060602.Date, download20060602.Description, download20060602.Amount, Members.FirstName, Members.[Mid Name], Members.Surname, Members.[Memb No] FROM Members LEFT JOIN download20060602 ON Members.description = download20060602.Description WHERE (((download20060602.Date) Between [Enter Start Date] And [Enter End Date]));
I have a query that is search for fields in a table that are either
1 - High 2 - Medium 3 - Low
I have a query that counts these and then puts the results into pie charts on a report.
However, when there is no "1 - High" value in the table against a paricualr criteria, obviously the quiery has nothing to look for an does not show a 0 value against the criteria but simply omits it (correctly) form the results.
This does affect the pie charts though which I want to show red for High, yellow for Medium and green for Low.
Therefore I need the query to show all criteria search results include 0 values, or to understand how I can colour code the series rather than the segments on the report.
After hours of playing around i finally have my IIF statement working the proplem is now is that when the Value is null it gives a blank value on my Report. How do i get it to show the value of 0 when it is Null. can i use an isNull withen my IIf statement maybe nested what is best practice?
I am very new to access. I have made a database that holds certain company information.
As part of a process a company must fill in and complete form and send to me and once they have I enter a 'yes' to the form and then link directly to said form.
I am wanting to be able to run a report to be able to only see the companies where a certain field has not been filled in.
For example I have a field for doc 698, if I have had this form back I enter a yes in this field, if I havent had this back I leave it blank and these blanks are what I want to pull off in the report.
I have made queries and then just filtered to show blanks but I would like to have in report form.
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 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 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
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 parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.
i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..
I am working on a school project and am stuck on the last part. I need to produce a query for use as a report. The fields in the query are:
Building Teacher 1 Teacher 2 Teacher 3 Teacher 4
Archive 1 Archive 2 Archive 3 Archive 4
"building" is the search parameter for the query
I need to produce a list of names of teachers, if there is nothing entered into the corresponding archive number e.g. no data in archive 1 and 3 , so show teacher 1 and 3.
It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:
Code: Private Sub cmdUpdateDates_Click() '################################### 'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time]. '################################### Dim intCounter As Integer intCounter = 0 Dim rs As ADODB.Recordset
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
Attached are screen shots of three queries used to get a percentage of jobs. If there is no jobs within the date range query 3 shows nothing. Can anyone tell me how if there are no jobs within the date range requested to get the query to give me 100%?
Hi, hope someone can help. I want the option of leaving perameters blank in a query in more than one field. the problem is that if I leave one blank and fill the perameters in for the other it brings all records up anyway. Is there a way around this? :rolleyes:
Hi All, I have searched and tried every post that I could find and can't seem to get this to work. What I have is 2 tables "training_tbl_200" and "topic_tbl_200". I have several querys "qry_training_215" thur "221" that make up my main query "qry_training_222" My main query will not show any records unless there is data in all of my subquerys. I need it to show a 0 if there is no training time in the category. Im not sure that I have explained this correctly so I have attached a copy of the database. I results that I need are if you run the query for August you can see that it should look like. In Feb there was training but nothing is showing up.
Thanks in advance for all your help...You guys and Gals are the best.
I have a query with Product, Product Directorate, Date Received, Date Delivered, and Date approved fields.
Product field criteria: "NAT CONV"
Product Directorate criteria: ([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)
Date Received criteria: (Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or(([forms]![frmWhatDates].[txtRecvStart] is null) or ([forms]![frmWhatDates].[txtRecvEnd] is null))
and so forth
When I closed my query, and opened it up again it looked bizarre. Because of the Is Null it added criteria multiple times.. I don't know what's going on but this makes it hard if i want to edit it in the future.
Why did Access do this to my query? Is there anyway I can make this simpler and how?
What I have is a form that filters records according to what is input and it generates a report. If that date fields are left blank.. I want it to display all the records
I need to create a query with the MTD field I have created the MTD and it works!!! but now I need it to show all of the PartNum fields even if null. do I need to convert all nulls to zeros? if so how do I go about this?
Here are the fields in the query
First Field: Field: PartNum Table: Part Total: Group By
Second Field: Field: UnitPrice Table: OrderDetail Total: Group By
Third Field: Field: NumOrdered Table: OrderDetail Total: Sum
Fifth Field: Field: CurrentYear: Year([OrderDate]) Table: (blank) Total: Group By Criteria: Year(Date())
Sixth Field: Field: CurrentMonth: Month([OrderDate]) Table: (blank) Total: Group By Criteria: Month(Date())
When I run the query I only get 2 parts and their total. but I want all parts to show up even if they have not been ordered in the month. the parts that have not been ordered should say $0.00 in the MTD field.
