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)
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 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 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
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";"")
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...
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.
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.
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.
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?
In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine.
The SQL used for the combobox is as follows..
Code: SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact FROM tbl_Contacts WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company])) ORDER BY tbl_Contacts.ContactForename;
User pick a record from a list and are able to doubleclick that record which will open another form with that record's ID. If the field is Null, then another form opens allowing the user to create a new record. The part that doesn't work, is if that record that was in the combo, gets deleted from somewhere else. Then the ID is still being shown in the combo (that's OK but it's no longer null), so double clicking the field does not bring up the correct Create New record form.
What I would like to have is something similar to the NotInList message that states something like, "That record has been deleted". This is what I have so far,If not in list
Code:
Private Sub APID_NotInList(NewData As String, Response As Integer) MsgBox "Please select an ... or double-click this field to add a new entry to the list." Response = acDataErrContinue APID = Null End Sub
On double clicking the combo
Code: Private Sub APID_DblClick(Cancel As Integer) Dim stDocName As String Dim stLinkCriteria As String
I have a very simple database with two tables. One stores Schools that I work for and the other holds people that work at that school.
I have a form that displays information from the schools table and I have a button that I want have open a form that displays the people that are related to that school.
THe problem is I can't figure out how to limit the recordset to just the people from the school record I was just viewing.
For example. I am looking at a record with School ID 1. I want to run a query on the People table that will return just the people that work for school id 1. I just can't figure out the criteria part.
I have a continouus form that queries a table and displays various fields on the form. For each record in the master table displayed on the continous form I also need to show the result of a calculation in an unbound field.
The calculation needs to be based on the Count of the rows of a particular type in another table. This other table will have many different types of record - and I have a query that returns the count. This query is based on a SQL WHERE clause that is fed in a value from a field in the master table. To be concret a field in the master table has the name ID_component. This field has to be then used in the query to determine how many rows in this other table has a type of ID_component. From what I can tell right right now is that the query which is triggered from a combo box in the form is not getting the ID_component value on a record by record basis.
My question therefore is whether using continous forms one can get an unbound field to do a query on another tabel when the query has to be fed in a parmeter from a field in the original tabel?
If this is not possible - any ideas on how I can achieve something similar to I can obtain a count of rows and feed it into a continous form
The fields are somehow related where values of B (unique) is taken from A(unique). I trying to do an SQL such that if I input a single value A=9 or 6 or 5 or 3 or 1, the following should be the output:
A B 9 6 6 5 5 3 3 1 1 Null
Or in another case where I input a single value A=8 or 4 or 2 the following should be the output:
I have a data base,one of the field contain Data like "ZZZ-DEFS#UUH1234567".
There should always be 19 characters in this field including #.I want to design a query which can sort out entries less than and more than 19 characters, so that wrong entries can be corrected .
I need to run a parameter query in which you enter a product number and the query (a) returns the list of components and (b) then automatically reruns the query for these components. In other words, I enter an input (a product number) the query gives me outputs (component numbers) and then reruns the query with each of these outputs as the input (i.e. I want to know what are the sub-components of these components). This process stops when all the components are raw materials -- this will be easily identifiable.
Does anyone know how to set-up this type of query?
I have a linked table in my access accdb file to a view on a SQL Server DB.
One of the columns is a date column, where only the time value is important, so it's stored in the default format, like 12-30-1899 12:00 AM.
In SQL Server, I can query records that fall between a certain time frame. It works in Access when written as an ADO query (that's another story), and it also works when I convert the view to a local access table.
But when I query it as a linked server, it will return everything is greater than 12-29-1899 11:59 pm, but when I try to search any date/time ranges on 12-30-1899 nothing is returned. This seems to be a bug in Access.
The reason I no longer use ADO, is that the results would not print. For some reason, when I go to print preview, Access would evaluate the query I'm passing through to SQL Server and throw a syntax error. I miss ADP.
I have a listbox on a form displaying records which can be filtered via a number of comboboxes. These comboboxes allow the user to select "<ALL>" or a specific value from the list. It all works perfectly.
However, I would like to be able to select a filter value of "Null" on one of the comboboxes which would display those records without a value in the relevant field. How can I do this?
The following (simplified) code is what I'm using:
SQL for the results listbox: SELECT qryClientSearch.ID, qryClientSearch.Name, qryClientSearch.AccountManager, qryClientSearch.AccountMgr FROM qryClientSearch WHERE ( ((qryClientSearch.ID) Like fCboSearch([Forms]![frmClientSearch]![cboSearchName])) AND ((qryClientSearch.AccountManager) Like fCboSearch([Forms]![frmClientSearch]![cboSearchAccountMgr]) Or (qryClientSearch.AccountManager) Is Null) ) ORDER BY qryClientSearch.Name;
SQL code for the combobox filter: SELECT qryStaff_All.ID, qryStaff_All.Nickname FROM qryStaff_All UNION (SELECT "*" AS ID,"<ALL>" AS Nickname FROM qryStaff_All) ORDER BY qryStaff_All.Nickname;
VB Code in Combobox Search Function: Public Function fCboSearch(vCboSearch As Variant) If IsNull(vCboSearch) Or vCboSearch = "" Then fCboSearch = "*" Else fCboSearch = vCboSearch End If End Function
For those that have noticed I have a "Or is null" criteria on the AccountManager field in the result set. I want to take this out and allow the value "Null" to be selected on specifically.
what I am trying to do with pretty much create a search query through code. So what's happening exactly is that the user enters a part number and expects to get 2 values: total orders and total items associated with the part number he/she entered. I have 3 tables and 2 of them are related. So I began my creating a query relating table 2 and table 3. I cannot include table 1 in the query.
1) Part Number is input by user 2) Search that Part Number into Table1 3) Take returned Parent Values associated with Part Number and store in Array 4) Modify Array values 5) Search Array values using a For Each loop into the query 6) Take the returned values found in query associated with each parent that was retrieved from the part number (user input) and return that through a table or query. 7) Also return the total rows in a specific column (Order Numbers)
Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.
I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.
I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?
I am trying to perform a search function query in access but am having trouble. I have two tables. One table has a column in which I need to search the contents of this field by referencing data in another field/table. I am able to write the query searching the data manually (not referencing the other table) but can't seem to get the query right when referencing the other table!
this query works = SELECT * FROM Sample_Data WHERE (((Sample_Data.[Affected_Frequencies]) Like "*451.425*" Or (Sample_Data.[Affected_Frequencies]) Like "*451.400*"));
but, i put together a frequency table (Table name = Frequencies) and the column within that I am searching for is called Frequency. im trying to reference Frequencies.Frequency using a LIKE statement.
I'm building a make-table query for which if the result is null (no record correspond to the set of criterias), a default message like "there was no activity during the period" would appear in the table (not a message box...I need the message in the output table). The best I could think of is an IIF function but it doesn't seem to work... Is there any way to do this without using VBA?
