Selection problems in a query

[ecawilkinson]

Hi all,

To start, I am an experienced 4GL programmer, so I understand programming concepts, but cannot seem to do some very basic things in Access. Let me explain in graphic detail.
I want to make a query that selects depending on the contents of 2 fields from a single table. The problem seems to be that the second filter depends on the results of the first. anyway. The 2 fields concerned are Members.Steward and Members.Activist, the other fields are just information to put on a report and/or label print. Members.Steward has only 4 possible values : "Steward", "Deputy Convenor", "Convenor" or "Contact". I want anyone who is "Steward", "Deputy Convenor" or "Convenor". Easy. But then, I only want "Contact"s that have a non-null value in Members.Activist. In pseudo-code :
If Members.Steward is non-null and Members.Steward <> "Contact" then
select
else
if Members.Steward = "Contact" and Members.Activist is non-null then
select
endif
endif

I am unsure of the correct syntax or where I would put the code. I have tried getting all non-null values from Members.Steward then using an If or Iif in the criteria cell of Members.Activist but to no avail. Unforatunately, I only have a minimal knowldedge of SQL, so cannot attack the problem from that direction.
Whatever I try does not work. I know the solution must be staring me in the face, but after a wasted day I have decided to put my pride aside and ask for help, any of which would be much appreciated.

Thank you,
Chris

 

[sfreeman@co.mer]

I'm not sure that your pseudo-code matches your description. You want anyone in the steward field, and if that steward is a contact, then it cannot be null? If so,

Try this:

SELECT
tblMembers.Steward
, tblMembers.Activist

FROM
tblMembers

WHERE
(((tblMembers.Steward)="Steward"))
OR (((tblMembers.Steward)="Deputy Covenor"))
OR (((tblMembers.Steward)="Covenor"))
OR (((tblMembers.Steward)="Contact")
AND ((tblMembers.Activist) Is Not Null));

HTH

 

[ecawilkinson]

Thanks

Hi,

thanks to both of you who replied. The SQL supplied by both worked fine. I used the criteria cell method in the end and it worked perfectly. I feel a BIT STUPID but also the scales have lifted from my eyes. My problem was that I had all the "ORs" for Steward in the same criteria cell, so could not make any exceptions for "Contact". Well, I know now. I am feeling my way round Access and getting to know a totally different way of working. I knew though that the answer had to be fairly straight forward.

Thanks so much again.
Chris