Returning All Records If Dependent Field Is Null
May 14, 2007
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.
Any help would be much appreciated.
Mat.
View Replies
ADVERTISEMENT
Apr 18, 2006
Hello all,
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?
Thanks.
View 4 Replies
View Related
Jan 14, 2008
Hello everyone.
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).
Sorry if this question is too basic.
Can anyone offer me any advice here?
Thanks in anticipation.
John
View 2 Replies
View Related
Nov 21, 2005
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";"")
Who can help me?
Thanks
Fred
View 5 Replies
View Related
Feb 20, 2013
I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.
When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?
Could there be some sort of join preventing all records from being returned?
View 2 Replies
View Related
Nov 16, 2007
I think the title pretty much sums it up....
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.
The query is refusing to return any results...
Any ideas?
View 10 Replies
View Related
Jun 24, 2005
I have a left-join query where I know that some of the values in the left-hand table have no corresponding values in the right-hand table...that's why I used a left-join: I want all the values in the left-hand table to show up. I would expect that records without corresponding values in the right-hand table would show up as nulls (I swear I have dozens of other queries that work this way) but in this one, they show up as "#error" instead.
This is a problem because I want to do some calculations on this field. Normally I would use the nz function to change the nulls to zeros.
So,
1. Any idea why this is happening?
2. Is there a way to change the #error values to zeroes, similar to using the nz function?
HEre's the SQL:
SELECT FeederInventorySummary.machine, FeederInventorySummary.Size, FeederInventorySummary.Leaf, FeederInventorySummary.Type, FeederInventorySummary.CountOfFeederID,FeederNeeds Summary_7.FeederQty FROM FeederInventorySummary LEFT JOIN FeederNeedsSummary_7 ON (FeederInventorySummary.Type = FeederNeedsSummary_7.Type) AND (FeederInventorySummary.Leaf = FeederNeedsSummary_7.Leaf) AND
(FeederInventorySummary.Size = FeederNeedsSummary_7.FeederSize) AND (FeederInventorySummary.machine = FeederNeedsSummary_7.ActualMachine);
For every record without a corresponding record in FeederInventorySummary_7, the FeederQty field shows up
as #error.
View 2 Replies
View Related
Mar 3, 2007
Hi, first of all I have to say I'm quite a noob at Access, so I apologise if my question seems stupid. :p I Googled for quite a bit looking for a solution but couldn't really get anything to work (maybe because I wasn't doing some of the possible solutions correctly?).
Anyway, I have a database with two tables, and I'm trying to make a QBF to search for data from these two tables. Quite a few of these entries have null values.
SELECT [M-Table].Firstc, [M-Table].Secondc, [S-Table].Thirdc
FROM [M-Table] LEFT JOIN [S-Table] ON [M-Table].Firstc = [S-Table].Firstc
WHERE
((([M-Table].Secondc) Like "*" & [Forms]![interface]![TxtSecondc] & "*")
AND (([Forms]![interface]![TxtFirstc]) Is Null)
AND (([Forms]![interface]![TxtThirdc]) Is Null))
OR ((([M-Table].Firstc) Like "*" & [Forms]![interface]![TxtFirstc] & "*")
AND (([Forms]![interface]![TxtThirdc]) Is Null)
AND (([Forms]![interface]![TxtSecondc]) Is Null))
OR ((([S-Table].Thirdc) Like "*" & [Forms]![interface]![TxtThirdc] & "*")
AND (([Forms]![interface]![TxtFirstc]) Is Null)
AND (([Forms]![interface]![TxtSecondc]) Is Null))
OR ((([M-Table].Firstc) Like "*" & [Forms]![interface]![TxtFirstc] & "*")
AND (([M-Table].Secondc) Like "*" & [Forms]![interface]![TxtSecondc] & "*")
AND (([Forms]![interface]![TxtThirdc]) Is Null))
OR ((([M-Table].Secondc) Like "*" & [Forms]![interface]![TxtSecondc] & "*")
AND (([S-Table].Thirdc) Like "*" & [Forms]![interface]![TxtThirdc] & "*")
AND (([Forms]![interface]![TxtFirstc]) Is Null))
OR ((([M-Table].Firstc) Like "*" & [Forms]![interface]![TxtFirstc] & "*")
AND (([M-Table].Secondc) Like "*" & [Forms]![interface]![TxtSecondc] & "*")
AND (([S-Table].Thirdc) Like "*" & [Forms]![interface]![TxtThirdc] & "*"))
OR ((([M-Table].Firstc) Like "*" & [Forms]![interface]![TxtFirstc] & "*")
AND (([S-Table].Thirdc) Like "*" & [Forms]![interface]![TxtThirdc] & "*")
AND (([Forms]![interface]![TxtSecondc]) Is Null));
This really long and tedious code was the only way I could get it to work such that in my query form, if I leave any one of the fields blank the search will still pull all the related results out, regardless of whether there are nulls in the values themselves, e.g. if I search for anything with "ab" in Secondc, it will give me all the data with "ab" in Secondc, even if there are null values for those entries in Firstc and Thirdc. Similarly for searches with the other fields.
I tried using IIF and Nz, first using IIF under Criteria, then I read somewhere that they should be used under Field rather than Criteria, but I couldn't really get it to work. Either the QBF wouldn't return any results, or it would return only those entries without null values, so if I searched for "ab" in Secondc, I wouldn't get the entries that had "ab" in Secondc but null values in either Firstc or Thirdc.
The problem with my solution is that I wouldn't be able to get it to work in another database with like 10 fields. The number of combinations would be a killer...
Is there any workaround? Many thanks! :)
View 2 Replies
View Related
Jun 18, 2015
In a query, how do I return a blank or null.
IIf([Field1]=[Field2],NULL,[Field3]
They are all numerical values that I need to format into percentages however Field3 could contain zero so I can't replace Null with zero. I simply want to return nothing if Field1 is equal to Field2.
View 3 Replies
View Related
Oct 10, 2011
2007 version - my child query is not returning null values. I have 2 fields, both pulled from a parent query. One is "DaysSinceRecd", the other is "AmountPaying". The parent query returns multiple results, which is fine. For example, the parent query might have 10 different records for "0" "DaysSinceRecd", with different "AmountPaying" for each of those records.
In the child query, I am trying to condense that down, so that I only have one "AmountPaying" for each "DaysSinceRecd". It's working fabulously, except that there are records in the parent query that have a value for "AmountPaying" but a null value for "DaysSinceRecd". The null value for "DaysSinceRecd" cannot be changed to another value, it needs to stay null. My child query is not returning the null records. I tried adding to the criteria: IsNull Or IsNotNull, >=0 or <=0. That's not working.
View 5 Replies
View Related
Aug 8, 2005
Is anyone else experiencing similar problem ?
Basically here's what happen:
1. User need to look up item name, while in a subform. He pressed F5
2. A form showed up, with a ComboBox (which got its values from a query)
3. User chose an item name in the ComboBox, and then pressed the Close button
4. The item name got copied into the subform.
Problem is, for some items/records, step 4 doesn't happen :eek:
When I investigated, turned out that for some records, the ComboBox returned null (instead of the selected record)
This is on a 5800+ records table, on Access 2000.
Here's the code :
[Forms]![005_cashier].[subform_0051_enter_sold_items]!selling_price = Me!item_name.Column(2)
[Forms]![005_cashier].[subform_0051_enter_sold_items]!quantity = 1
The second line always works (always got copied to the subform properly), while the first one only works with certain items/records.
I've examined the offending records, but couldn't imagine how they'd be able to make Access to behave like that.
Any ideas ?
Thanks,
Harry
View 2 Replies
View Related
May 15, 2013
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?
View 2 Replies
View Related
Aug 19, 2014
I am new to Access. I am after a vba script to run an update query.
I have a table named MYOB_Invent.
I have a Query Named MYOB_Update.
The query is showing records where my field named Test1 is null.
I want to update records with null in the field to today's date (the date of the day that it is run) formatted as 18/08/2014 and the field next to it is named Updated (which is yes/no) from un-ticked to ticked for the updated values.
I would like a vba script to automate the process as I want to automate it via a commandbutton for users.
View 3 Replies
View Related
Jul 6, 2014
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
View 5 Replies
View Related
Oct 24, 2012
I have a table called welding, I want to create a user friendly form for input. The database ultimately wants to go on share point so I am building it in the web database option.
One of the fields is called location, if the location is 'Field' I want 10 more fields to appear for data entry such as weld reason, rail temperature etc . If the location is 'Depot' I want these fields to be hidden as they aren't relevant.how would I do this?
View 14 Replies
View Related
Nov 23, 2005
Is there a way to make a field on a form dependent on what the user selects from 2 other cbo? By that I mean I would want the user if they selected in cbo1 Easter and they select cbo2 Regional President in the form field I would want it to auto return the name John Smith because in my table he is the Eastern Regional President. I am not sure if I can do this by query or is there an easy way to do this?
View 3 Replies
View Related
May 28, 2005
I am trying to put in a validation rule so that data in one field cannot be less than that in another.
e.g. i want to make a validation rule so that a phone call end time cannot be before the phone call start time.
Can anyone help???
View 5 Replies
View Related
Nov 2, 2013
If I have a Form that is used to populate a Table can I make one field entry dependent upon another?
For example, if I have a field for Discipline (with values such as Mechanical, Electrical, Piping) and another field for Equipment Type (with values such as Motor, Gearbox, etc) can I set up the Form so that when I select Electrical for the Discipline, only electrical items (such as Motor) are available to select for the Equipment Type field?
Using the table below as an example, if I select Electrical for the Discipline field in my Form, when I go to select a value for the Equipment Type field the drop down shows only Motor and Fan?
Discipline
Equipment Type
Electrical
Motor
Electrical
Fan
Mechanical
Gearbox
Mechanical
Pump
Piping
DN 150 Pipe
Or, better still, can I set it up so that if I select Motor for the Equipment Type field, the Discipline field will automatically be populated with Electrical?
View 4 Replies
View Related
Nov 21, 2014
I would like to display the value of a field [Name] based on the value of another field in the same Table [Position] in a report. My goal is to have a visual representation of data that changes based on the changes to the value of the [Position] field.
View 3 Replies
View Related
Nov 2, 2004
so i'm trying to create a report that only displays certain fields per record based on another field in that record. To clarify: [Type] is a numeric field holding either 1, 2, or 3. I have a function that is instructed to display (ie, change from not visible to visible) a certain combination of fields depending on the number in [Type] when the form is opened. I assume I would have to go through all the records individually (do loop until), but i'm not sure if this works in a report.
is there any way to have different fields displayed for different records within the same report??
Thanks
-Jason
View 4 Replies
View Related
Mar 4, 2014
Is it possible to use date dependent field names in a query?
I have this table and there are columns based on year and month (formate of names is: 2014,01) I need to select the columns of last month and then 4 months further down in history.
Is there a way to do that as the information in those columns I need to make calculations and graphs with.
View 14 Replies
View Related
Mar 16, 2005
Hi,
I've got a data entry form where orders can be created. Within this form there is a ComboBox which is used to search for items and stores the selected item ID in the Order table. However the field for storing the Unit Price relating to the selected item is currently manually enterable. Where as I would like this to be dependent on the item selected within the ComboBox and updated automatically.
At the moment I've added an extra colum to the ComboBox to retrieve the relevant Unit Price and I can show this in a normal seperate text box using '=Combo27.Column(1)' as the ControlSource.
How can I get rid of the text box and incorporate the code into the current UnitPrice field so as it inserts the relevant record (price) into the UnitPrice coloumn of the Order table?
Any help on this would be much appreciated!
View 1 Replies
View Related
Aug 18, 2015
I am attempting to open a website hyperlink, some of the fields contain https:// and some of them dont.
Code:
Private Sub Facebookbut_Click()
Dim Hyper As String
If InStr([TEAMFacebook], "https") Then
Hyper = Me.TEAMFacebook
Else
Hyper = ("https://www.facebook.com/" & Me.TEAMFacebook)
End If
Application.FollowHyperlink Hyper
End Sub
So far this does 2 things, it doesn't open any hyperlinks at all and continues to attempt to until the program is closed from task manager. Before I had this error it would open the hyperlink twice if the field does not contain "https" and the IF statement was passed to the 2nd option.
View 14 Replies
View Related
May 10, 2013
I there a way to determine whether or not my checkbox within a form is ticked or not dependent on another value within a field?
I have a field called 'DaysRemaining' and another check box field called 'Expired'. I want the expired checkbox to be ticked if the value within 'DaysRemaining' is '<0' and unticked if '>=0'. Is there a way i can do this within the control source of my check box?
View 1 Replies
View Related
Dec 12, 2006
I have a database of purchase orders
Many of the purchase orders have a revision No against them, eg:-
Purchase Order No / Line No / Revision No / Value
1001 / 001 / 00 / £50
1001 / 001 / 01 / £100
1001 / 001 / 02 / £200
I am trying to find a way to write a query that will show me the actual value of the final revised value of each Purchase order line ie: the answer to above is
1001 / 001 / 02 / £200
Could anyone please help?
View 2 Replies
View Related
Sep 28, 2005
I have 3 tables - Students, which contains all students; Attendance, which contains dates a particular student was absent; and AttendanceCtrl which contains Quarter beginning/ending dates. I need to be able to calculate the number of days a student was present, absent, tardy in a quarter. The query needs to extract all students regardless of whether or not they missed any days. I tried using a Left join Students to Attendance and then joining AttendanceCtrl but Access said it couldn't do it because of "ambiguous outer joins". It said to create the first join then use that query in the second join. I tried that. I created a query with a Left join joining Students to Attendance. That gives me what I wanted, all students from Students and also those students with matching records in Attendance.
The 2nd query is giving me problems. I tried joining the first query (qryStudents) to AttendanceCtrl and calculating my totals. Problem is, the query only gives me students what have records in the Attendance file; it doesn't give me all the students. If a student did not miss any days, I need to show that he was present 90 days out of 90. Because there's no matching record in the Attendance file, I get nothing for those students.
As I said the first query (qryStudents) seems to be working but I need to take the records from that query, group them together by StudentID, and then calculate my totals. How can I do this? Am I approaching this wrong altogether?
Thanks for any help.
View 1 Replies
View Related
