Select from query that excludes records based on a field criteria
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.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
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.
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.
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
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.
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.
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.
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.