Query Criteria Not multiple fields with data (1 Viewer)

Ambrosia · May 31, 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.

Does this make it clearer?

JHB · May 31, 2015

Ambrosia said:
...
Does this make it clearer?

NO!
We need to see some sample data and your query.
And also the result you want.

Uncle Gizmo · May 31, 2015

I think you probably need to add "Like" and a * for both parameters. As JHB says, A closer look would be helpful.

vbaInet · May 31, 2015

Start with your first field, get it working then move on.

Ambrosia said:
First field ECO LifeCycle Status Criteria is Not "ERP UPDATE" or "CANCELLED"

NOT "ERP UPDATE" OR "Cancelled" = It's not ERP_UPDATE but it can be "Cancelled".

So what you really want is one of the following:

Code:

NOT "ERP UPDATE" OR [COLOR="Blue"][B]NOT[/B][/COLOR] "Cancelled"

NOT IN("ERP UPDATE", "Cancelled")

Ambrosia · May 31, 2015

Okay so that takes care of the first field of Criteria.

I also need to use another field to omit "COMPLETED" Not Like "COMPLETED"
Thank you for the And Not Like that works much better.

vbaInet · May 31, 2015

Well that's up to you, I don't know if you want an AND or an OR condition. So put the second criteria on the same line as the first and test it - that's an AND. If you don't get your desired results, move that second criteria to the OR row (i.e. the row below your first criteria) and test it - that's an OR.

Ambrosia · May 31, 2015

The first two are in one field for criteria the second is in a different field.

Field one is Status Update ECO Not "ERP UPDATE" And Not "CANCELLED"
Field two is Status Update ECR Not "COMPLETED"

Two separate fields with criteria that isn't required in the query.

vbaInet · May 31, 2015

This is what I'm talking about. See attached.

Ambrosia · May 31, 2015

Unfortunately, that doesn't work! I've put data in all fields with each type of data and nothing comes out for records. It looks sound but doesn't provide the output!

vbaInet · Jun 1, 2015

Ambrosia said:
Unfortunately, that doesn't work!

What doesn't work?

Query Criteria Not multiple fields with data (1 Viewer)

Ambrosia

New member

JHB

Have been here a while

Uncle Gizmo

Nifty Access Guy

vbaInet

AWF VIP

Ambrosia

New member

vbaInet

AWF VIP

Ambrosia

New member

vbaInet

AWF VIP

Attachments

Ambrosia

New member

vbaInet

AWF VIP

Similar threads

Users who are viewing this thread