Mystifying QBE Problem

[David Lovegrove]

I am not a developer – perhaps I qualify as a “power user”. I am developing a simple contact management database using Access within Office 365. All the fields are short text fields. They comprise name, contact information, a comment field and some fields that I use to hold codes. One of the latter, “Comms”, is currently either blank or contains “PA” or “EMF” – the latter indicates that although I have an email address, it has failed. I do not have an email address for 6 people.



The first thing that I want to do with my database is to send out an email about a new event that I am planning.

So I develop a query, using the QBE mechanism, that selects records that have an email address. I can use “Is not Null” or “Like *@*” for the email field. Both work correctly.

Then I want to weed out records that have “EMF” in the “Comms” field. So I add a column to the query:-

“Comms” <> EMF or Is Null (for some reason if I do not use “Is Null” I do not get records where “Comms” is blank – however this not the issue that I want to raise)

When I run the query, records with the EMF code are omitted but I get records where there is no email address !

Can anyone explain and put me right please.

[orange]

Please post the SQL of the query involved.

[isladogs]

If you need both filters they should be on the same row in the filter criteria so its filtering on one AND the other.
I suspect you have them on different rows which filters for one OR the other.
If you change to SQL view, you can see what is going on

[CJ_London]

simply, your query is not written correctly. Hard to say without seeing the actual sql generated by the query builder - but suspect you are mixing up your and's and or's

[David Lovegrove]

If you need both filters they should be on the same row in the filter criteria so its filtering on one AND the other.
I suspect you have them on different rows which filters for one OR the other.
If you change to SQL view, you can see what is going on

Thank you - you are spot on. Problem solved. This was the first time I had used QBE with slightly more complex filtering.

[isladogs]

You're welcome.