ACCESS 2007 Null and criteria query

Topic

New Reply

I want to filter out records without an email address on a specific criteria of classification. The classification works fine but I also get both null and not null records.

Reply | Quote

Replies

G.F.

Is it correct that you have a field called Classification and you want to ONLY filter out records with a blank email address for a specific value of the Classification field?

If that is the case you need a two row query grid.
On the first row you’ll set the Classification field formula to The excluded classification value
On the second row you’ll set the Classification field formula to = The excluded classification value and the Email field to “”

Of course you include all other fields the same in both lines.
Should do the trick as this is an OR query and will select records that conform to either of the two rows.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Actually Mr. Retired, a beer right now sounds mighty good!

My query contains the 2 fields “Email1” and “Citizen/Leg” I need to work with.

I want only the fields with an Email address and those records with “SFL”, or “YAL” or “C” or “F” in the “citizen/leg” field.

Citizen/leg is the first field in the query immediately followed by Email1.

I get the “SFL” and the other 3 criteria records when I have no Email1 criteria, alone with blank and filled Email1 fields (image retired1.)

However, when I put “not null” on the first criteria row of Email1 and beginning with “SFL” on the second row of Citizen/Leg criteria, followed beneath it the remaining 3 other criteria on subsequent rows, I get the other files beneath Citizen/leg including my “H”ouse and “S”enate legislators’ records in database view and all of the emails – with or without data.(image retired.2)

Thank you very much for your patience. You wouldn’t have to twist my arm to bring us a couple of beers out of Auburn, Alabama area.

46619-retired.146620-retired.2

Either field at this time is “sorted” and both checked to “show.”

Reply | Quote

After moving the position of email1 and citizen/leg from 1 to 2nd and back again through trial and error, my message does not match up with their placement in the images.

Reply | Quote

G.F.,

Ok here’s where I think your problem is. The first line in your query will match All Citizen/Leg values! So they are all returned.

You need to move one of the values probably “F” since that will be the easiest to get rid of in the list. Next you need to repeat the IS NOT NULL on each of the rows.

Access evaluates each of the criteria rows only by the criteria that exist on that row.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You rock Mr. Retired. How do I acknowledge your solution with a “star” or “1” as you are? Will now have that beer. Might have two, one for the each of us. Thank you VERY much.

G.F.,

Ok here’s where I think your problem is. The first line in your query will match All Citizen/Leg values! So they are all returned.

You need to move one of the values probably “F” since that will be the easiest to get rid of in the list. Next you need to repeat the IS NOT NULL on each of the rows.

Access evaluates each of the criteria rows only by the criteria that exist on that row.

HTH :cheers:

Reply | Quote

You rock Mr. Retired. How do I acknowledge your solution with a “star” or “1” as you are?

Just click the thumbs up icon on the post that helped you, it’s located at the bottom left of the post.

Glad to have been of assistance.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

gotta ya. Thanks ever so much.

Reply | Quote

As I see it, the WHERE clause of your SQL should be:
WHERE [Email1] Is Not Null AND [Citizen/Leg] IN (“SFL”, “YAL”, “C”,”F”)

This says “I want to return all records that have an email value and that have 1 of 4 acceptable values for citizen/leg”. This assumes that the Email1 field can not contain a zero-length string. If it can, then use: WHERE [Email1] & “” “” AND …

On a separate issue, you should get out of the habit of using special characters in your fieldnames, with the exception of the underscore character.

Reply | Quote