W
WebDude
Hello 
This query1 works flawlessly in the query builder, it returns all the rightresults regardless of the criteria i throw at it..(gender = 'male' AND age >20 AND status = 'assigned'). 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 status = 'assigned' AND *insert any other criteria here* , for example, status = 'assigned' AND gender = 'male' it returns the right records but the status field displays 'not assigned'. if i search for gender = 'male', everything comes up fine. if i search for status = 'not assigned' and gender = 'male', everything looks fine. or if i search for status = 'assigned' everything is ok. its just when i combine status = 'assigned' withanything, that the resulting records, though correct in that they satisfy the search criteria, have their status field show "not assigned" even though they are assigned.
QEURY1 is based on the volunteers table, it has all the fields from the volunteers, some calculated fields and one field called "STATUS". The "STATUS"field in QEURY1 is defined like this..
Status:IIF(Exists(SELECT Assignments.fkVolunteerID FROM Assignments WHERE (((Assignments.StartDate) Is Not Null) AND ((Assignments.EndDate) Is Null)) AND (Assignments.fkVolunteerID = Volunteers.pkVolunteerID)),"Assigned","Not Assigned")
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
(school like '*harvard*')
or
(Status = 'not Assigned') and (school is not null)
or
(Status = 'Assigned')
again, the problem arises when, the form filter includes "STATUS = 'assigned'" **combined** with any other criteria, the form returns the correct records but the STATUS field always says __"Not Assigned"__ . For example;
(status = "assigned") AND (age > 30)
will show correctly all volunteers over age 30 who are assigned, but the status field will still say "not assigned" even though these volunteers are "assigned".
another example,(status = "assigned") and (school is not null) and (gender = male)again, it will return the right records, but the status field will still say "not assigned".
if i simply search for, School is not null , the status field displays correctly. If i search for gender = male and
age = 30 and school is not null, the status field displays correctly. infact, the status field always displays correctly on the form except when i include "status = 'assigned'" in the form filter with another criteria.
Also, after displaying the results of a filter with "status = 'assigned'"and whatever, if i try to sort the datasheet by any column, database returns an error "query too complex".
Ive though about this for 2 weeks,
tried various work arounds for days,
Any advice would be appreciate.
Best Wishes,
WD
This query1 works flawlessly in the query builder, it returns all the rightresults regardless of the criteria i throw at it..(gender = 'male' AND age >20 AND status = 'assigned'). 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 status = 'assigned' AND *insert any other criteria here* , for example, status = 'assigned' AND gender = 'male' it returns the right records but the status field displays 'not assigned'. if i search for gender = 'male', everything comes up fine. if i search for status = 'not assigned' and gender = 'male', everything looks fine. or if i search for status = 'assigned' everything is ok. its just when i combine status = 'assigned' withanything, that the resulting records, though correct in that they satisfy the search criteria, have their status field show "not assigned" even though they are assigned.
QEURY1 is based on the volunteers table, it has all the fields from the volunteers, some calculated fields and one field called "STATUS". The "STATUS"field in QEURY1 is defined like this..
Status:IIF(Exists(SELECT Assignments.fkVolunteerID FROM Assignments WHERE (((Assignments.StartDate) Is Not Null) AND ((Assignments.EndDate) Is Null)) AND (Assignments.fkVolunteerID = Volunteers.pkVolunteerID)),"Assigned","Not Assigned")
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
(school like '*harvard*')
or
(Status = 'not Assigned') and (school is not null)
or
(Status = 'Assigned')
again, the problem arises when, the form filter includes "STATUS = 'assigned'" **combined** with any other criteria, the form returns the correct records but the STATUS field always says __"Not Assigned"__ . For example;
(status = "assigned") AND (age > 30)
will show correctly all volunteers over age 30 who are assigned, but the status field will still say "not assigned" even though these volunteers are "assigned".
another example,(status = "assigned") and (school is not null) and (gender = male)again, it will return the right records, but the status field will still say "not assigned".
if i simply search for, School is not null , the status field displays correctly. If i search for gender = male and
age = 30 and school is not null, the status field displays correctly. infact, the status field always displays correctly on the form except when i include "status = 'assigned'" in the form filter with another criteria.
Also, after displaying the results of a filter with "status = 'assigned'"and whatever, if i try to sort the datasheet by any column, database returns an error "query too complex".
Ive though about this for 2 weeks,
tried various work arounds for days,
Any advice would be appreciate.
Best Wishes,
WD