Select from query that excludes records based on a field criteria

I am trying to write a query that excludes all of a particular entry in a field, but still displays entries in which that field is blank. The table is for species observations and I want to exclude records in which "cucumbers" appear in the invert field.

When I enter Not "cucumbers" for the invert field criteria, I only see records in which there was an invert recorded, and it was not a cucumber .

I want to retain all records, whether and invert was observed or not, except for those in which the invert was a cucumber.

Sponsored Links:

Related Topics

Create a query that excludes records that contain a wildcard character
In Access 2003/XP/2000/97, I have a Comments field that has data with the asterisk (*) character in it. I'm trying to write a query that excludes all comments that contain an asterisk.


excludes certain records based on the existence of other records...
I have a table that was sent to me imported from Excel (tblAllSpec). The field on which my criteria is based is called "SpecType". The table will be integrated into my database eventually, but I have to pull some numbers out before that happens.

The field that will eventually be the Primary Key in the parent table is "SBNumber". SO, there are a bunch of records with duplicate SBNumbers with many different SpecTypes.

What I need to determine is what SBNumbers have records WHERE SpecType = 'Type1' and NO records WHERE SpecType = 'Type2'. This seems like such a simple thing but I can't seem to get anything to work.

I am relatively new to this and still feeling my way around.


How come opening recordsets doesn't pay attention to criteria?
I have the following

dim rst as adodb.recordset
set rst = new adodb.recordset

rst.open "select * from officeTemp", currentproject.connection

But the problem is that rst now possesses a set that is, say, 50 entries long. If I open up "select * from officeTemp" manually in Access, it shows 42 records (and correctly excludes 8 records as denoted in the criteria of the query


Filtering On A Form That Is Based On A Query Criteria/Parameter
I have a tabular form that is based on a query with a specific criteria/parameter e.g. Officer Name. I want to be able to create the headings on each of the columns in the form as buttons so when the user clicks the button the rows of records are filtered in ascending or descending order of the field clicked.

I had tried a similar thing before but found that this would bring up all the records on the query, ignoring the query criteria/parameter.


select query based on multiple checkboxes
just wiondering if there is a way I can do the following. I have a table (table 1) with 10 fields in it. I have a form with 6 of these field names displaying on it. I have a checkbox next to each of these fields. When the checkbox is selected I want to use that field as criteria in a select query. This means that I want the query to select records based on what checkboxes are selected, whether that be 1 checkbox or any combinatiion of the 6 checkboxes.
Not completely great with the whole programming thing and would like to be able to do in in a query (design view) if possible.


Sub Form Disappears When I add new field
1. I have a main form, single form, where you select criteria. Meal name, Meal ID, Start Date and End date.

2. You click the button Find Meals which only has the code me.refresh.

3. The sub form is based on a select query that reads the criteria off the main form and refreshes the sub form (continuous form) with the correct data. See Pic 1.

4. The select query is based on one table. See Pic 2.

I add one additional field from the table to the query. I then add the field to the sub form.

When I reopen the main form the sub form has disappeared from the screen. I select the same criteria in the parent form and click the button and nothing shows up. See Pic 3.


Examples of query criteria
When you want to limit the results of a query based on the values in a field, you use query criteria. A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value.
In this topic:
* Introduction to query criteria
* Criteria for Text, Memo, and Hyperlink fields
* Criteria for Number, Currency, and AutoNumber fields
* Criteria for Date/Time fields
* Criteria for other fields


Select Query on Criteria or Blank?
I am trying to build a select query that looks up records in Table A. I am trying to allow the end users of the database to select the criteria for this query by filling in data into Table B and having Table A & B related. So far, this is working fine by telling my query to return values in Field 1 from Table A that match the values in Field 1 of Table B.

However, I want to give the end users the option of leaving a data field in Table B blank, thereby not specifying any criteria and returning all values in Table A. That is where I am running into a problem


Select Query on Criteria or Blank?
I am trying to build a select query that looks up records in Table A. I am trying to allow the end users of the database to select the criteria for this query by filling in data into Table B and having Table A & B related.

So far, this is working fine by telling my query to return values in Field 1 from Table A that match the values in Field 1 of Table B.

However, I want to give the end users the option of leaving a data field in Table B blank, thereby not specifying any criteria and returning all values in Table A. That is where I am running into a problem.


Want all if none on list selected
I am using A02 on XP. I have a criteria form for a report. One item is a combo box drop down list of user initials. My query has the field names on the criteria line (see below). If I select BWA, I get those. Cool. But, if I want all (not just BWA), I do not select any initials but I get no records. The query criteria line is:

[Forms]![fCriteriaFromTo]![WhatOriginator]

I know it can be done but cannot find it in help. I even created a record in my UserTbl with blank for initials but then I get zero records


Set Query Criteria based on Control values
I have a report based on several criteria that the user can select in listboxes on a form.

Each of these criteria have an "ALL" option which, if selected, will prevent that criteria from being added to the WHERE clause in the Query string.

Currently, this is done in VBA, but I was wondering if there was a way to do this in the SQL statement itself (or the Query designer in Access), so I can rely less on VBA.


Changing select query to append query with VBA
I have a select query that uses a criteria to select some records and puts them in a table for the user to modify them. The SQL for the query is being changed with VBA when the form is launched because the criteria is dynamic Once it is done being changed I would like to be able to put the records in a different table than the one the records came from.

The only way to do that (than I seem to think of) is to use VBA and change the SQL of the query making it an append query.

So this query would be changed back and forth in VBA. Is this the right way to do thisand common practice or do I need to look for a different solution?


Using Variable as criteria
I have a procedure that constructs an In statement and stores that value in a module level variable. I then want to use criteria in my query to call the function to pull the value stored in the variable. I know that data being stored is constructed as required but when I run the query with the criteria to GetUIC() no records are returned. I have a control on my form that calls the GetUIC() function and displays the string that was built from my multi-select listbox. If I cut and paste that string as the criteria for my query the correct records are returned. Procedure below creates the string. The field is a text field.


Changing select query to append query with VBA
I have a select query that uses a criteria to select some records and puts them in a table for the user to modify them. The SQL for the query is being changed with VBA when the form is launched because the criteria is dynamic Once it is done being changed I would like to be able to put the records in a different table than the one the records came from. The only way to do that (than I seem to think of) is to use VBA and change the SQL of the query making it an append query. So this query would be changed back and forth in VBA. Is this the right way to do this and common practice or do I need to look for a different solution


Can one query take the results from another query?
But slowly learning the ropes.

This is what I am looking for:

One query takes a search criteria and spits out all records that match the criteria, but displays only a few select fields. (This is done with the intend to give the dB user an initial brief idea about what to expect in each record). The results of query 1 could be in a form or a report (?).

Then, the user selects the records (from query 1) for which he wants to see full details, he can click on a button, for selected records and a second query provides him/her with ALL fields (of the records he selects).

The whole idea is break up a search into 2 steps - (1) find and make a list of records that match a criteria, showing just a few fields at first (2) From this list down select a fewrecords for which ALL fields are produced in a report.


flexibility of Query based on Form controls.
I have created a Form from which a user can select the data to use in a Report regarding several buildings. Within the database there is a table for the Facilities (tbl_Facilities). Each Facility may contain several buildings (tbl_Buildings).

The form allows the user to select by FacilityType, County, and/or BuildingType; and I would like to allow the user to select "all". My question is how I can build thisflexibility into the select query that is the data source for the report?

For example, I can set the select criteria for FacilityType to be Forms!frm_ReportMenu!txtFacilityType; and so long as that control contains data, the query works fine. But how can I setup the query to return all records if the field is left blank?


Access 2003 Report using Query
I built a simple four field database. I need to produce a report that excludes records which contain "Helpdesk", "Open" or "TEST" in the FirstName field.
I built a query which excluded those records. Then I built a report using the query, but the report does not exclude those records. I do not know what I am doing wrong. Can you


Limiting the values of a combo box using IIF in query criteria
I am trying to limit the options that show in a combo box based on the value of another field. In the design view of my query, I have the following criteria in the ReferralRole_ID field:

....

I am getting zero records in my combo. If I just put < 1 OR 2 OR 7 > in the criteria, it works fine. Not sure if the IIF statement is coded correctly.


Criteria for Report/Query being ignored
I have a report with a record source based on a query that has certain criteria that must be met in a particular field. However, this criteria is being ignored in 4 of the 5 reports. These five reports are similar except that they are dividing all the records up into the five separate reports based on this one field. The only one that is working, is the one whom's data is showing up on the other four reports.

Do I need to show more, or can anyone tell what is going on here? All five reports have their own separate record source.

I have a feeling I might have to explain more


Multiple criteria in a select query
I have a Select Query where I want to give criteria to one field. The possibilities for the field are listed in an other table in the database (one column-table).

How can I add this whole list as criteria (OR) without having to retype the whole list underneath each other in the criteria list?