IIF function advice

[mobile2]

I have a query in Access 97 and think I may need to use the IIF function. The query is based on a "UserDetails" table and shows users names as well as two date fields, "JoinDate" and "LeaveDate", these are to record when a user joins and leaves a service/product. If the "LeaveDate" is null then we can assume they are still registered for that particular service. There is also an "AccountRef" text field. This is duplicated in the table and the query so there are multiple "AccountRefs", some with "LeaveDates" and some with null "LeaveDates". What I want to do is find out if there are any "AccountRefs" where ALL users have a "LeaveDate" e.g. no active users within that account. Is the IIF function what I need to find this out. Any advice would be appreciated.

 

[nicsin]

Hi,

try this:


select ud2.AccountRef
from UserDetails ud2
where ud2.AccountRef not in (
select distinct ud1.AccountRef
from UserDetails as ud1
where ud1.LeaveDate is not null)

 

[mobile2]

I have entered the following within SQL view, with the correct field names, and then attempted to change to datasheet view. It will not let me do this and only shows the "egg-timer" icon within SQL view.

SELECT ud2.RDAccountReference
FROM [User Details] AS ud2
WHERE ud2.RDAccountReference not in (select distinct ud1.RDAccountReference FROM [User Details] as ud1 WHERE ud1.UserLeaveDate is not null);

When I switch to query design it shows:

Field:RDAccountReference
Table:ud2
Criteria:Not In (select distinct ud1.RDAccountReference FROM [User Details] as ud1 WHERE ud1.UserLeaveDate is not null)

Can you confirm if the above is correct?

 

[nicsin]

To be honest with you, you should make the following changes:


Field:RDAccountReference
Table:ud2
Criteria:Not In (select distinct ud1.RDAccountReference FROM [User Details] as ud1 WHERE ud1.UserLeaveDate is not null)


you want the accounts that haven't got nulls in the LeaveDate, I think.

Apart from that, it hsould be working. Have you got any test data? Do you know of an account that should be returned?

 

[mobile2]

It has worked this time. I think my PC is just a wee bit slow. Superb!