iif exists -- too complex?

[webdude]

Hello :-)




This query1 works flawlessly in the query builder, it returns all the right results regardless of the criteria i throw at it..(gender = 'male' AND age >20 AND assigned= 'yes'). the problem arises on form1.

i use qeury1 as form1's recordsource and display the results in datasheet mode. i enter my search criteria in form1's filter, but when i search for assigned= 'yes' AND *insert any other criteria here* , for example, assigned = 'yes' AND gender = 'male' it returns the right records but the assigned field displays 'no'.

QEURY1 is based on the volunteers table, it has all the fields from the volunteers, some calculated fields and one field called "assigned"..

assigned:IIF(Exists(SELECT Assignments.fkVolunteerID FROM Assignments WHERE (((Assignments.StartDate) Is Not Null) AND ((Assignments.EndDate) Is Null)) AND (Assignments.fkVolunteerID = Volunteers.pkVolunteerID)),"yes","no")


Form1 has its RECORDSOURCE set to "Query1" and its FILTER set dynamically to whatever the user wants to search by.

So for example.

(gender = male)
or
(gender = male) and (age >18)
or
(schoolname like '*harvard*')
or
(assigned= 'no') and (school is not null)
or
(assigned= 'no')



again, the problem arises when, the form filter includes "assigned= 'yes'" combined with any other criteria, the form returns the correct records but sometimes the assigned field says "no" when it should say yes. For example;

(assigned= "yes") AND (age > 30)

will show correctly all volunteers over age 30 who are assigned, but the assigned field will still say "no" even though these volunteers are clearly assigned = "yes".



another example,(assigned= "yes") and (schoolname is not null) and (gender = male) again, it will return the right records, but the assigned field will still say "no".

if i simply search for, Schoolname is not null , the assigned field displays correctly. If i search for gender = male and
age = 30 and schoolname is not null, the assigned field displays correctly.

Any advice would be appreciate.

Best Wishes,
WD


ps: after displaying the results of a filter with "assigned = 'yes'" and whatever, if i try to sort the datahseet by any column, database returns an error "query too complex".

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post. Identify objects involved in this issue.

[webdude]

"want to provide db for analysis?" - i am dumping all confidential data & irrelevant forms/queries/reports. ill compact, zip then attach by tomorrow.

..and thank you.

[webdude]

Hi June7,

when i enter criteria in the form "Volunteer_Search_Form_Results" .filter property, the records returned are always correct but sometimes the "assigned" field displays incorrectly - it displays "no" when it should be "yes".

Volunteer_Search_Form_Results.filter examples ive used to test...

filter = ( (status = 'inquiry') OR ((status = 'roster') and (assigned = 'no'))) --- assigned displays properly
filter = ( (status = 'inquiry') OR ((status = 'roster') and (assigned = 'yes'))) ---assigned displays properly
filter = (assigned = 'yes') -- assigned displays properly
filter = (assigned = 'no') -- assigned displays properly


filter = ( ((status = 'roster') and (assigned = 'yes'))) -- assigned displays "NO" when it should say "yes"
filter = (status = 'inquiry') and (assigned = 'yes') -- assigned displays "NO" when it should say "yes"
filter = (assigned = 'yes') and (gender = 'male') -- assigned displays "NO" when it should say "yes"


filter = ( ((status = 'roster') and (assigned = 'no'))) -- assigned displays properly
filter = (assigned = 'no') and (gender = 'male') -- assigned displays properly
filter = (status = 'roster' and gender = 'male') -- assigned displays properly


the "Volunteer_Search_Form_Results" recordsource is query "Volunteer_Search_Query0". i also entered my filter examples in the query itself (as where statements) using the query builder and everything works perfectly - all the right records are return and the assigned field displays 'yes' when it should be 'yes' and 'no' when it should be 'no'.

why does "assigned" field give problems on the form and not the query?


cheers,
webdude.

[June7]

You have lookups in tables - I NEVER do that. Review http://access.mvps.org/access/lookupfields.htm.

I have never used EXISTS keyword so this is an exercise of discovery. I suspect that it cannot be used in this manner. EXISTS should be in the WHERE clause as part of criteria to select records. Review http://www.techonthenet.com/sql/exists.php

Consider this query instead:
SELECT IIf([onVolunteerInterest],"inquiry",IIf([OnPendingApproval],"applying",IIf([onRoster],"Roster",IIf([onMissingInAction],"mia",IIf([onVacation],"vacation",IIf([onretired],"Retired",IIf([onDoNotPlace],"Do Not Place",""))))))) AS status, IIf(IsNull([Q1].[fkVolunteerID]),"no","yes") AS assigned, DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd")) AS Age, emailstringfunction([e-mail1],[e-mail2]) AS [E-mail], Telstringfunction([Telephone1],[Telephone2],[Telephone3]) AS Telephone, Volunteers.*
FROM (SELECT DISTINCT Assignments.fkVolunteerID
FROM Assignments, Volunteers
WHERE (((Assignments.StartDate) Is Not Null) AND ((Assignments.EndDate) Is Null)) AND (Assignments.fkVolunteerID = Volunteers.pkVolunteerID)) As Q1 RIGHT JOIN Volunteers ON Q1.fkVolunteerID = Volunteers.pkVolunteerID;

[webdude]

You have lookups in tables - I NEVER do that. Review http://access.mvps.org/access/lookupfields.htm.

yeah, i should move the lookups to the form.

I have never used EXISTS keyword so this is an exercise of discovery. I suspect that it cannot be used in this manner. EXISTS should be in the WHERE clause as part of criteria to select records. Review http://www.techonthenet.com/sql/exists.php

yes, i used it incorrectly.

Consider this query instead:
SELECT IIf([onVolunteerInterest],"inquiry",IIf([OnPendingApproval],"applying",IIf([onRoster],"Roster",IIf([onMissingInAction],"mia",IIf([onVacation],"vacation",IIf([onretired],"Retired",IIf([onDoNotPlace],"Do Not Place",""))))))) AS status, IIf(IsNull([Q1].[fkVolunteerID]),"no","yes") AS assigned, DateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd")) AS Age, emailstringfunction([e-mail1],[e-mail2]) AS [E-mail], Telstringfunction([Telephone1],[Telephone2],[Telephone3]) AS Telephone, Volunteers.*
FROM (SELECT DISTINCT Assignments.fkVolunteerID
FROM Assignments, Volunteers
WHERE (((Assignments.StartDate) Is Not Null) AND ((Assignments.EndDate) Is Null)) AND (Assignments.fkVolunteerID = Volunteers.pkVolunteerID)) As Q1 RIGHT JOIN Volunteers ON Q1.fkVolunteerID = Volunteers.pkVolunteerID;

lol that was quick. i been stuck for weeks.

table aliasing? i never understood.

and how we use a join in there.. that was surprising.

thank you!
wd