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:

Similar posts...

 

Create a query that excludes records that contain a wildcard character

Create a query that excludes records that contain a wildcard character Icon
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...

excludes certain records based on the existence of other records... Icon
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.
 

Filtering On A Form That Is Based On A Query Criteria/Parameter

Filtering On A Form That Is Based On A Query Criteria/Parameter Icon
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.
 

Examples of query criteria

Examples of query criteria Icon
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
 

Sub Form Disappears When I add new field

Sub Form Disappears When I add new field Icon
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.
 

Set Query Criteria based on Control values

Set Query Criteria based on Control values Icon
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.
 

Using Variable as criteria

Using Variable as criteria Icon
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.
 

Can one query take the results from another query?

Can one query take the results from another query? Icon
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.
 

Query Criteria based on drop down menu

Query Criteria based on drop down menu Icon
On my query I added criteria that says [Forms]![frmMain]![Combo11] so that the query will run based on what is selected from the drop down. However, if the drop down is left blank (no select made). How would I update my criteria to say than display all?
 

Limiting the values of a combo box using IIF in query criteria

Limiting the values of a combo box using IIF in query criteria Icon
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.