Topics Search

Select from query that excludes records based on a field criteria

Select from query that excludes records based on a field criteria
Views: 10
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:

More topics

 

Create a query that excludes records that contain a wildcard character

Preview
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...

Preview
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?

Preview
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

Preview
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

Preview
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

Preview
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

Preview
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?

Preview
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?

Preview
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

Preview
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