Queries :: Excluding Records From Search Results That Have Fields With No Data?
Aug 6, 2015
I have a search form with 12 fields. In my query I use
Code:
Like "*" & [Forms]![CustomerRetestDatabaseSearch]![RetestLocation] & "*" Or Is Null
for each field on the search form.
I get the results I expect, it finds all records that match the criteria. Even if some of the fields in a record are null.
But if the query finds a record that matches one field I enter criteria into, and nulls for the other fields I enter criteria into it displays the record. I want to show exact matches. (If what I entered is null... don't show the record).
The reason I have "Or Is Null" is to include the records for the fields I left blank on the form.
Search Form with Criteria.PNG
Search Query.jpg
Search Results With Missing Entered Criteria(Dont Want These Records Included).jpg
View Replies
ADVERTISEMENT
Apr 29, 2014
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
View 4 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
May 3, 2013
All using access 2010. I have a query1, query2 and query3. Query1 is my master. Query2 and Query3 was created based on different criteria but derived from the Query1. I now want to exclude the records from Query1 that are in Query2 and Query3. When I try to put isnull in criteria of both queries Im trying to exclude; instead of returning the remainder records in the master I get none.
View 3 Replies
View Related
May 2, 2013
I have a table of around 6000 records comprising 4 fields (A,B,C,D).
- Each field can contain numbers or Nulls.
- Each record can comprise all numbers, a mixture of numbers or Nulls, or all Nulls.
I'd like to build a query that excludes all records that contain any number from a small list of numbers.
This sounds very simple but I am having problems when trying to include records that have Null's in my query output.
For test purposes I tried to exclude all records that contain the numbers 1 or 9 (these numbers can be present in any field).
This works perfectly, in isolation, on Field A (i.e. 1 or 9 but not Null are excluded from field A):
Code:
WHERE Table.A Not In (1,9) OR Table1.A Is Null;
When I try to copy the above, referencing fields B-D, I run into problems - no matter how I try to alter the Boolean operators.
View 2 Replies
View Related
Jul 23, 2015
I want to run a query on a table that holds all speed information for our trucks imported from a third party. Some of the speed alerts in that table are not correct so we set up a second table managed by the users to enter a speed exception. So if we know that Main St in Dallas TX generates false alerts for speeding we know not to call the driver, the third party db speed limit is not up to date.
So I want my query to pull all the speed data from tblSpeedData, except leave out the records where the street and zip are listed in the tblSpeedExceptions.
View 3 Replies
View Related
Sep 12, 2014
I am trying to write a query that will check all records in a table but exclude the 10 newest records in the table.
The table is from a stock program i have wrote for the company i work for (i am a novice access user). what ive been asked to do is write a duplicate order system that will flag up if the order has already been packed.
the table logs the [OrderID] with each item [barcode] scanned out with a barcode scanner what i want is a query that checks the OrderID for a duplicate entry in the entire table but because the OrderID is entered with every item scanned i want to ommit the last 10 records as prety much no order has more than 10 items i understand this may not be 100% fool proof but it is close enough.
The other option is to have it ommit all records within the last 15 minutes there is also a [Time] and [PackDate] Field which im guessing could be used for this the time field records Now() and the PackDate records Date(). After searching the web i cant seem to find anyway to ommit the last 'n' records and the few things i have found with the Date("m",-15,Date()) doesnt seem to work
View 7 Replies
View Related
Mar 10, 2014
I want to enter text boxes on a form that are linked to certain fields on a table and when search criteria is typed in show the results in a table on a subform.
View 5 Replies
View Related
Jul 31, 2015
So I created a search form which filters my results. Apparently I don't have enough posts to link to the source of the code though. The code is from this website, /forums/showthread.php?t=188663
I then wanted to allow users to double click a result in the listbox to take them to the form which contains details about that record, however the listbox doesn't point to the correct record.
-When nothing is specified in the search field, all records are shown in the listbox, but double clicking on the first record takes me to a form that has no information. Clicking the second record takes me to the Detail Form of the first record.
-When information is put into the search field, the results are filtered and only a few display, however clicking the first result again takes me to a blank form while the second result takes me to the first record of the unfiltered list. There is no way to reach the Detail Form for the last result of the listbox.
-I tried right clicking the results in the listbox and copy pasting the information. The first result gave me a "0", the second a "1", and so on and so forth. This was the same whether or not the information was filtered or not.
-I tried switching to a combo box and got the same results.
View 2 Replies
View Related
May 30, 2015
I'm creating a query from one table using two fields that require certain records to NOT bring up records that have the following text:
One table, two fields
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"
Second field ECR LifeCycle Status Criteria is Not "COMPLETE"
When I put the Not Criteria in the first field only I get a result.
When I put the Not Criteria in both fields I get no results.
View 9 Replies
View Related
Jun 18, 2015
I am running Access 2013. I have created a search form with about 10 different categories from a single table. I will only show 5 fields for this example The form is called FrmSearch with
Description
CarNum
SerialNum
Category
Condition
In the query, I have the criteria, Like "*" & [Forms]![FrmSearch]![description] & "*" and have this same criteria for all fields with the proper text field entry. (CarNum, serialnum, category, etc)
The problem I have is that I have over 200 items listed in the Description column but only 2 entries in the CarNum column. When I run the search with nothing in the FrmSearch, it should show all data but it only shows the 2 CarNum. If I put something in the Description, only the two items that show CarNum. I tried to use Is Null and Is Not Null but still doesn't work. I take out the "Like" criteria from the CarNum and it works. So, it seems that it does not pull up all the records because the CarNum has empty fields.
View 5 Replies
View Related
May 18, 2015
I have 3 text fields on a form that I search by. I have the search query action set on a button, which produces the results in a datasheet view. Is there any way that I can make the records in the datasheet view clickable so I can select the record and go to the form to update the data?
View 2 Replies
View Related
Dec 8, 2013
I'm trying to make a search form that makes use of queries that search a table based on what fields I have. How would I go about having the query display all the fields for the record(s) that match the search criteria? This is for Access 2010.
As far as the query is set up, I have several fields with a search criteria, and the others are for displaying the relevant information about the results (since they're not search criteria, I used "Like '*'"). When I ran the query, it doesn't come up with anything - even if the record actually exists in the table with specified criteria.
View 9 Replies
View Related
Oct 25, 2013
I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.
This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.
View 3 Replies
View Related
Sep 11, 2014
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
View 14 Replies
View Related
Sep 9, 2013
I have a query which is supposed to search for all engines with a power rating between a user-specified range ('Rated Power'). The results should state all of these engines along with a few more related details ('System_ID_No', 'Project No', 'Rated Speed', 'Other Ratings' and 'Cylinder Capacity') that are useful to know. However, the problem arises when these other fields are empty. If empty, the related engine results do not appear in the final results spreadsheet. How do I make sure they are included aswell?
SQL:
SELECT tblProjectOverview.System_ID_No, tblProjectOverview.[Project No], tblProjectOverview.Customer, tblEnginePerformance.[Rated Power], tblEnginePerformance.[Rated Speed], tblEnginePerformance.[Other Ratings], tblEngineDefinition.[Cylinder Capacity]
FROM (tblProjectOverview INNER JOIN tblEnginePerformance ON tblProjectOverview.[System_ID_No] = tblEnginePerformance.[Sytem_ID_No]) INNER JOIN tblEngineDefinition ON tblProjectOverview.System_ID_No = tblEngineDefinition.System_ID_No
WHERE (((tblEnginePerformance.[Rated Power]) Between [Enter minimum power rating (kW):] And [Enter maximum power rating (kW):]));
View 10 Replies
View Related
Jul 29, 2014
I have a table with:
A dDataID (sequential auto-number)
B dDateTimeEntered (date/time field)
C dFactoryOrder (text)
D dSpecificationID (number)
E dStartWeek (text)
F dIncreasingDecreasing (text, values are limited to "Increasing", "Decreasing", or "NA")
I need a query to look up the last 9 values where C, D, and E match variables from a form, then tell me how many "Increasing" values are contained within that set. I want to store the final value in a combo box on the form. I tried writing the code and this is what I got - design view attached as a pic and SQL below. It looks like the "Top 9" is sorting on the Expr1 field, which isn't what I want, but I'm not sure how to force it to use dDataID or dDateTimeEntered to figure out which records to use.
SQL:
SELECT TOP 9 Sum(IIf([tblData]![dIncreasingDecreasing]="Increasing",1,0)) AS Expr1
FROM tblData
WHERE (((tblData.dFactoryOrder)=[Forms]![frmDataEntryDisplay]![dFactoryOrder]) AND ((tblData.dSpecificationID)=[Forms]![frmDataEntryDisplay]![dSpecificationID]) AND ((tblData.dStartWeek)=[Forms]![frmDataEntryDisplay]![dStartWeek]));
View 8 Replies
View Related
Sep 30, 2014
I am working on a report that has some special characteristics.
Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.
Each of these tables has a date that the Complaint, Complement, and Termination notice was received.
Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.
The equation used for that is : (complaints/vendors_in_group)*100
It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.
What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.
Questions:
1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.
2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.
3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.
View 4 Replies
View Related
Feb 28, 2006
Hi All,
I'm trying to extract records that start with 10 but I need to exclude all that start with 1011,1015 & 1025
so far I can only extract records that I need but cant exlude the others when I use the SQL below
INSERT INTO tblConceptOrders ( TA_TASK_ID )
SELECT dbo_F_TASKS.TA_TASK_ID
FROM dbo_F_TASKS
GROUP BY dbo_F_TASKS.TA_TASK_ID
HAVING (((dbo_F_TASKS.TA_TASK_ID) Like "10*"))
ORDER BY dbo_F_TASKS.TA_TASK_ID;
I'm looking for a better way to exclude the records I don't want & collect the ones that I do
Any help will be appreciated
thanks :)
View 6 Replies
View Related
Feb 13, 2008
Good morning,
I'm trying to create a query and I'm asking for specific records, but also need to exclude certain records within that criteria. Here is my Select statement:
SELECT [MD IMPORT PURGE PEND].PROC,
[MD IMPORT PURGE PEND].SEG,
[MD IMPORT PURGE PEND].NAME,
[MD IMPORT PURGE PEND].GROUP,
[MD IMPORT PURGE PEND].BEN,
[MD IMPORT PURGE PEND].DATE,
[MD IMPORT PURGE PEND].RISK,
[MD IMPORT PURGE PEND].REASON,
[MD IMPORT PURGE PEND].PLAN,
[MD IMPORT PURGE PEND].PAR,
[MD IMPORT PURGE PEND].IND,
[MD IMPORT PURGE PEND].REC,
[MD IMPORT PURGE PEND].SUBNO,
[MD IMPORT PURGE PEND].CLIENT,
[MD IMPORT PURGE PEND].CLAIM,
[MD IMPORT PURGE PEND].FSDT,
[MD IMPORT PURGE PEND].PDDT,
[MD IMPORT PURGE PEND].CHECK,
[MD IMPORT PURGE PEND].PROV,
[MD IMPORT PURGE PEND].SOURCE,
[MD IMPORT PURGE PEND].PAYEE,
[MD IMPORT PURGE PEND].EFF2,
[MD IMPORT PURGE PEND].CSHRCNO,
([CARR] & [CARR1] & [CARR2]) AS CPL,
[MD IMPORT PURGE PEND].CLASS,
[MD IMPORT PURGE PEND].MKTSEG,
[MD IMPORT PURGE PEND].PAID
FROM [MD IMPORT PURGE PEND]
WHERE ((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND
(([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="CONTROL") AND
(([MD IMPORT PURGE PEND].REASON)="35") AND
(([MD IMPORT PURGE PEND].IND)="HOMES" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND
(([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="NASCO-NASCO") AND
(([MD IMPORT PURGE PEND].REASON)="35") AND
(([MD IMPORT PURGE PEND].IND)="HOSTP" Or ([MD IMPORT PURGE PEND].IND)="OTHER") AND
(([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND
(([MD IMPORT PURGE PEND].SOURCE)<>"IN" And ([MD IMPORT PURGE PEND].SOURCE)<>"IP") AND
(([MD IMPORT PURGE PEND].PAYEE)="J" Or ([MD IMPORT PURGE PEND].PAYEE)="N")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND
(([MD IMPORT PURGE PEND].REASON)="44" Or ([MD IMPORT PURGE PEND].REASON)="51") AND
(([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M")) OR
((([MD IMPORT PURGE PEND].SEG)="PAR") AND
(([MD IMPORT PURGE PEND].REASON)="35") AND
(([MD IMPORT PURGE PEND].IND)<>"HOSTS") AND
(([MD IMPORT PURGE PEND].EFF2)="3")) OR
((([MD IMPORT PURGE PEND].REASON)<>"35" And ([MD IMPORT PURGE PEND].REASON)<>"44" And ([MD IMPORT PURGE PEND].REASON)<>"51") AND
(([MD IMPORT PURGE PEND].IND)="HOMES") AND
(([MD IMPORT PURGE PEND].CHECK)="000000000") AND
(([MD IMPORT PURGE PEND].PAYEE)="S" Or ([MD IMPORT PURGE PEND].PAYEE)="M") AND
(([MD IMPORT PURGE PEND].EFF2)="3") AND
(([MD IMPORT PURGE PEND].CSHRCNO) Is Null));
The part in bold is the exclusion. I want it to exclude records where the reason is 35 or 44 or 51, IND is HOMES, check number is 000000000, payee is s or m, eff2 is 3 AND cshrcno is null.
Can anyone tell me how to achieve this? I really appreciate your help!
View 4 Replies
View Related
Jun 24, 2015
I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.
The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:
Code:
Private Sub cmdAssetSearch_Click()
Dim rs As Object
If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then
MsgBox "Please type in an asset number to search for.", vbOKOnly
Me.TextAsset.SetFocus
[code]....
I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.
View 6 Replies
View Related
Dec 3, 2013
Why I am getting duplicate results for some of my records in a query. I have unique values set to Yes. I have also validated that the tables I am using don't have duplicate data. SQL is below.
SELECT DISTINCT [tbl_Rewards Activity Report - By Member Number].[Member Number],
[tbl_Rewards Activity Report - By Member Number].[Last Four],
[tbl_Rewards Activity Report - By Member Number].[BAL ID],
[tbl_Rewards Activity Report - By Member Number].[Primary Name],
[Code] .....
View 1 Replies
View Related
Jul 23, 2013
I have a hourly report I need to run and one of the records will always be filled with a name of the employee. I need to hardkey some data in every day and instead of changing who is there that day I would like to be able to enter in, for example, sales data in a seperate record and when I go to create the report it will exclude any employee names that have 0 sales data or a blank record. so the table would be something like:
Employee.....Sales....
John.............0
Steve............__
Mathew..........1
So when I run the report it will only pull Mathew's name and sales information and leave John and Steve off the report.
View 2 Replies
View Related
Nov 14, 2013
I'm creating a search form to filter out data based on certain inputs. My VBA code looks like:
Code:
Private Sub Command18_Click()
On Error GoTo errorcatch
Me.Filter = "([Experiments.Log] Like ""*" & Me.Text21 & "*"") AND ([Expdate] Like ""*" & Me.Text22 & "*"") AND ([BaseSolution] Like ""*" & Me.Text24 & "*"") AND([AddCom] Like ""*" & Me.Text25 & "*"") AND ([Test] Like ""*" & Me.Text26 & "*"") AND ([Plan] Like ""*" & Me.Text23 & "*"")"
Me.FilterOn = True
Exit Sub
errorcatch:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
However, the output does not include records where other fields are blank. I have read that I may need to use Is Null but am not sure how to.
View 3 Replies
View Related
Sep 27, 2005
Hello, I'm new to the forum and relatively new to Access. There's something I'm trying to do but I'm not sure if it's even possible so I'm hoping someone can help me out and point me in the right direction
the situation:
I have one very big table containing data for around 250 petrol stations. The data is collected monthly, so each site has a record relating to each month. E.g site A has data for month 1, 2, 3 etc.
The problem is that in any month, some of this data needs to be excluded from analysis. And this will vary over the months. What I want to do is run a query where specific records are excluded, for example, exclude Site A data for month 1 and 3, Site B data for month 2 etc.
I have another table containing a list of which site + month data is bad and to be excluded. Ideally I want to simply add to the list each month before I run the select query.
the question:
Is there any way to exclude records from a select query based on their details appearing on another table?
help would be much appreciated!
thanks
Catherine
View 2 Replies
View Related
Aug 18, 2015
I have a query that outputs results like:
Company ID | Data A | Data B | Data C
101 | results |results |results
102 |results |results |results
103 |results |results |results
104 |results |results |results
105 |results |results |results
In another Table containing additional company information, I have the primary key as the company ID, and I want to make the query that outputs the above table, auto-fill the blank fields in the existing Company information with the same headings as Data ABC etc.
However, I don't want the query to add full new records (which I think is the Append Query?), instead I want the existing company records have additional fields (Data ABC) added, with information from the Query added.
View 1 Replies
View Related
