Query works for 2 out of 4 status' help

R

Ruzz2k

Registered User.
Local time
Today, 23:10
Joined
Apr 28, 2012
Messages
102
hi I have a combobox with status and a qry in which the status is a calculated field. I am trying to filter using -1 and 0 dependant on if it is selected or not. But it does not work for CLOSED or AUTHORISED, but works for OPEN and OVERDUE. I cant see the wood through the trees. if someone could help me thanks.

SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method, tblInvestigations.Ref1, tblInvestigations.Ref2, tblInvestigations.Issue, tblInvestigations.[Root Cause], tblInvestigations.[Due Date], tblInvestigations.Analyst, tblInvestigations.Technical, tblInvestigations.Closed, IIf([ClosedTest]=-1,"CLOSED",IIf([TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=-1 And [ClosedTest]=0,"COMPLETE",IIf([AnalystTest]=-1 And [TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=0 And [ClosedTest]=0 And [OverdueTest]=-1,"OVERDUE",IIf([OpenTest]=-1,"OPEN")))))) AS Status, tblInvestigations.Notes, tblInvestigations.[Evidence Only], IIf([Due Date]<Date(),True,False) AS OverdueTest, IIf(IsNull([Analyst]),False,True) AS AnalystTest, IIf(IsNull([Technical]),False,True) AS TechnicalTest, IIf(IsNull([Closed]),False,True) AS ClosedTest, IIf(IsNull([Analyst]) And ([Due Date]>=Date()),True,False) AS OpenTest
FROM tblInvestigations
WHERE (((IIf([Due Date]<Date(),True,False))=IIf([Forms]![frmMain]![Status]="OVERDUE",-1,0)) AND ((IIf(IsNull([Analyst]),False,True))=IIf([Forms]![frmMain]![Status]="COMPLETE",-1,0)) AND ((IIf(IsNull([Technical]),False,True))=IIf([Forms]![frmMain]![Status]="AUTHORISED",-1,0)) AND ((IIf(IsNull([Closed]),False,True))=IIf([Forms]![frmMain]![Status]="CLOSED",-1,0)) AND ((IIf(IsNull([Analyst]) And ([Due Date]>=Date()),True,False))=IIf([Forms]![frmMain]![Status]="OPEN",-1,0)) AND ((IIf(IsNull([Forms]![frmMain]![Source]),True,[tblInvestigations].[Source]=[Forms]![frmMain]![Source]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![No]),True,[tblInvestigations].[No]=[Forms]![frmMain]![No]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![Ref1]),True,[tblInvestigations].[Ref1]=[Forms]![frmMain]![Ref1]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![Method]),True,[tblInvestigations].[Method]=[Forms]![frmMain]![Method]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![Section]),True,[tblInvestigations].[Section]=[Forms]![frmMain]![Section]))<>False));
 
What is the Row Source of Status, and what is the Bound Column? Also, why are you using IIf? Instead of:

(IIf([Due Date]<Date(),True,False)=IIf([Forms]![frmMain]![Status]="OVERDUE",-1,0)

You could simply do:

([Due Date] < Date()) And ([Forms]![frmMain]![Status]="OVERDUE")
 
row source is a value list
ALL;OPEN;OVERDUE;COMPLETE;AUTHORISED;CLOSED
bpund column is 1

what I pasted was sql and this is what access 2010 changes it to, I will give what you said a try but the principle is each calc shows a -1 if it is true which leads to the overall status calc however I dont think I can manipulate the calc status criteria to Overdue or Closed etc as it doesnt recognise it, im still thinking on this but i it is baffling me currently, I may try setting criteria just for the status again.#

thanks
 
Try this:
Code: 
SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method, tblInvestigations.Ref1, tblInvestigations.Ref2, tblInvestigations.Issue, tblInvestigations.[Root Cause], tblInvestigations.[Due Date], tblInvestigations.Analyst, tblInvestigations.Technical, tblInvestigations.Closed, IIf([ClosedTest]=-1,"CLOSED",IIf([TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=-1 And [ClosedTest]=0,"COMPLETE",IIf([AnalystTest]=-1 And [TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=0 And [ClosedTest]=0 And [OverdueTest]=-1,"OVERDUE",IIf([OpenTest]=-1,"OPEN")))))) AS Status, tblInvestigations.Notes, tblInvestigations.[Evidence Only], IIf([Due Date]<Date(),True,False) AS OverdueTest, IIf(IsNull([Analyst]),False,True) AS AnalystTest, IIf(IsNull([Technical]),False,True) AS TechnicalTest, IIf(IsNull([Closed]),False,True) AS ClosedTest, IIf(IsNull([Analyst]) And ([Due Date]>=Date()),True,False) AS OpenTest
FROM tblInvestigations
WHERE (([Due Date]<Date()) And ([Forms]![frmMain]![Status]="OVERDUE")) AND ((Not IsNull([Analyst])) And ([Forms]![frmMain]![Status]="COMPLETE")) AND ((Not IsNull([Technical])) And ([Forms]![frmMain]![Status]="AUTHORISED")) 
AND ((Not IsNull([Closed])) And ([Forms]![frmMain]![Status]="CLOSED")) 
AND (IsNull([Analyst]) And ([Due Date]>=Date()) And ([Forms]![frmMain]![Status]="OPEN")) 
AND (( IsNull([Forms]![frmMain]![Source]) Or ([tblInvestigations].[Source]=[Forms]![frmMain]![Source]) ))) 
AND (( IsNull([Forms]![frmMain]![No]) Or ([tblInvestigations].[No]=[Forms]![frmMain]![No]))) 
AND ((IsNull([Forms]![frmMain]![Ref1]) Or ([tblInvestigations].[Ref1]=[Forms]![frmMain]![Ref1])))
AND ((IsNull([Forms]![frmMain]![Method]) Or ([tblInvestigations].[Method]=[Forms]![frmMain]![Method]))) 
AND ((IsNull([Forms]![frmMain]![Section]) Or ([tblInvestigations].[Section]=[Forms]![frmMain]![Section])))
Copy and paste that into the SQL View of a new query. I don't guarantee that I got all the paren balancing right, but it's worth a shot.
 
thanks but that did not work but i figured it out:
however now I want a status of All and cannot figure it out as simply adding a extra iif with criteria as = 1 or 0 will not work. any ideas?

SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method, tblInvestigations.Ref1, tblInvestigations.Ref2, tblInvestigations.Issue, tblInvestigations.[Root Cause], tblInvestigations.[Due Date], tblInvestigations.Analyst, tblInvestigations.Technical, tblInvestigations.Closed, IIf([ClosedTest]=-1,"CLOSED",IIf([TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=-1 And [ClosedTest]=0,"COMPLETE",IIf([AnalystTest]=-1 And [TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=0 And [ClosedTest]=0 And [OverdueTest]=-1,"OVERDUE",IIf([OpenTest]=-1,"OPEN")))))) AS Status, tblInvestigations.Notes, tblInvestigations.[Evidence Only], IIf(IsNull([Analyst]) And [Due Date]<Date(),True,False) AS OverdueTest, IIf(IsNull([Technical]) And IsNull([Closed]),IIf(IsNull([Analyst]),False,True),False) AS AnalystTest, IIf(IsNull([Closed]),IIf(IsNull([Technical]),False,True),False) AS TechnicalTest, IIf(IsNull([Closed]),False,True) AS ClosedTest, IIf(IsNull([Analyst]) And ([Due Date]>=Date()),True,False) AS OpenTest
FROM tblInvestigations
WHERE (((IIf(IsNull([Analyst]) And [Due Date]<Date(),True,False))=IIf([Forms]![frmMain]![Status]="OVERDUE",-1,0)) AND ((IIf(IsNull([Technical]) And IsNull([Closed]),IIf(IsNull([Analyst]),False,True),False))=IIf([Forms]![frmMain]![Status]="COMPLETE",-1,0)) AND ((IIf(IsNull([Closed]),IIf(IsNull([Technical]),False,True),False))=IIf([Forms]![frmMain]![Status]="AUTHORISED",-1,0)) AND ((IIf(IsNull([Closed]),False,True))=IIf([Forms]![frmMain]![Status]="CLOSED",-1,0)) AND ((IIf(IsNull([Analyst]) And ([Due Date]>=Date()),True,False))=IIf([Forms]![frmMain]![Status]="OPEN",-1,0)) AND ((IIf(IsNull([Forms]![frmMain]![Source]),True,[tblInvestigations].[Source]=[Forms]![frmMain]![Source]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![No]),True,[tblInvestigations].[No]=[Forms]![frmMain]![No]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![Ref1]),True,[tblInvestigations].[Ref1]=[Forms]![frmMain]![Ref1]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![Method]),True,[tblInvestigations].[Method]=[Forms]![frmMain]![Method]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![Section]),True,[tblInvestigations].[Section]=[Forms]![frmMain]![Section]))<>False) AND ((IIf(IsNull([Forms]![frmMain]![Investigator]),True,[tblInvestigations].[Investigator]=[Forms]![frmMain]![Investigator]))<>False));
 
What is your criteria for "All?"

And why in the world are you using IIf that will slow down your query immeasurably? For example, this:

IIf(IsNull([Analyst]) And [Due Date]<Date(),True,False))=IIf([Forms]![frmMain]![Status]="OVERDUE",-1,0)

Can be more simply stated as:

(IsNull([Analyst]) And ([Due Date] < Date())) AND ([Forms]![frmMain]![Status] = "OVERDUE")
 
my criteria for All is That all the different status can be =-1 or 0
Ive always used iif and not sure how to get it to work otherwise.

thanks, I am here to learn so I am happy to take any advice
 
Then stick this at the front of your WHERE clause:

([Forms]![frmMain]![Status]="ALL") OR ...
 
Hi that wont work, this is what I want, currently My filter works for all 5 tests
OpenTest
OverdueTest
AnalystTest
TechnicalTest
ClosedTest

However the All filter would only work if the criteria for every one of these is set to equal to -1 or 0. i.e the values could be
-1,0,0,0,0
0,-1,0,0,0
0,0,-1,0,0
0,0,0,-1,0
0,0,0,0,-1
I dont know how to do this whilst maintaining the individual criteria aswell, i.e -1
note I would also have to do the same for outstanding filter, where Open and Overdue would be equal to -1 and rest 0
hope that makes sense,
thanks, I really need to figure this out as I would like the status field to remain calculated.
 
Clearly, you're calculating "Status" here:

IIf([ClosedTest]=-1,"CLOSED",IIf([TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=-1 And [ClosedTest]=0,"COMPLETE",IIf([AnalystTest]=-1 And [TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=0 And [ClosedTest]=0 And [OverdueTest]=-1,"OVERDUE",IIf([OpenTest]=-1,"OPEN")))))) AS Status

So, I don't understand what you mean by "all." A given record cannot be all these statuses at the same time. If by "all" you mean you want a record regardless of status, then my suggestion above works because if the criteria in the form control is "All" then status will be ignored.
 
Hi Texan, yes the status is calculated above as you mentioned. this is calculated from the 5 separate calculations as metioned as tests.
What I have is a form with a combobox with OPEN, CLOSED, OVERDUE, AUTHORISED, COMPLETE to match the above. But I canot just set the criteria to these as the status field is calculated from 5 calcs so its asks me what they equal. so I set the individual test calcs as depicted by the SQL code for each of the 5 status' that worked. so now I am stuck as I want to create an extra drop down field called ALL, which will bring up all records regardless of status. hope that makes sense. I am going to give some things a try I may modify the data and upload it for help unless you think I can filter the calculated field status directly from the combobox. thanks for al your help.
 
Simply make sure that "ALL" is in the combo box Row Source - I assume it's a Value List, right? If you want all records regardless of status, then try what I suggested.

The criteria needs to end up looking like:

(combo box = "ALL") OR ((combo box = "OPEN") AND (Test status flags for open)) OR ((combo box = "CLOSED") AND (Test Status flags for closed)) OR ((combo box = "OVERDUE") AND (Test status flags for overdue)) OR ((combo box = "COMPLETE") AND (Test status flags for complete)) OR ((combo box = "AUTHORIZED") AND (Test status flags for authorized))

Note that if the combo box contains "ALL", the predicate performs no further tests, thus selecting all records. And the combo box can contain only one value at a time, so the predicate performs only the tests dictated by the combo box value.
 
Hi Texan, I am unsure how to get this to work so I have modified the data so I can upload the file.

frmMain - is the form with the status filter on it, currently ALL and OUTSTANDING do not work.
qryInvestigations - is the query that runs it all.

ALL = where each of the five tests can be equal to a 0 or -1.
I have records with all the status' so it will be evident if the code works or not.

Outstanding = where OverdueTest and OpenTest = -1 and the others 0

I am happy to change the way the status is calculated if required so that my filters work. This is bugging the hell out of me. Thanks for your help in advance.
 
Last edited:
Well, I'm close. Your query *should* look like this:

Code: 
Parameters [Forms]![frmMain]![Status] Text;
SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method, tblInvestigations.Ref1, tblInvestigations.Ref2, tblInvestigations.Issue, tblInvestigations.[Root Cause], tblInvestigations.[Due Date], tblInvestigations.Analyst, tblInvestigations.Technical, tblInvestigations.Closed, IIf([ClosedTest]=-1,"CLOSED",IIf([TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=-1 And [ClosedTest]=0,"COMPLETE",IIf([AnalystTest]=-1 And [TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=0 And [ClosedTest]=0 And [OverdueTest]=-1,"OVERDUE",IIf([OpenTest]=-1,"OPEN")))))) AS Status, tblInvestigations.Notes, tblInvestigations.[Evidence Only], IIf(IsNull([Analyst]) And [Due Date]<Date(),True,False) AS OverdueTest, IIf(IsNull([Technical]) And IsNull([Closed]),IIf(IsNull([Analyst]),False,True),False) AS AnalystTest, IIf(IsNull([Closed]),IIf(IsNull([Technical]),False,True),False) AS TechnicalTest, IIf(IsNull([Closed]),False,True) AS ClosedTest, IIf(IsNull([Analyst]) And ([Due Date]>=Date()),True,False) AS OpenTest
FROM tblInvestigations
WHERE (([Forms]![frmMain]![Status] = "ALL") OR
((IsNull([Analyst]) And [Due Date]<Date()) AND ([Forms]![frmMain]![Status]="OVERDUE")) 
OR ((Not IsNull([Technical]) And Not IsNull([Closed]) AND Not IsNull([Analyst])) AND ([Forms]![frmMain]![Status]="COMPLETE")) 
OR ((Not IsNull([Closed]) AND Not IsNull([Technical])) AND ([Forms]![frmMain]![Status]="AUTHORISED")) 
OR ((Not IsNull([Closed])) AND ([Forms]![frmMain]![Status]="CLOSED")) 
OR ((IsNull([Analyst]) And ([Due Date]>=Date())) AND ([Forms]![frmMain]![Status]="OPEN"))) 
AND ((IsNull([Forms]![frmMain]![Source])) OR ([tblInvestigations].[Source]=[Forms]![frmMain]![Source])) 
AND ((IsNull([Forms]![frmMain]![No])) OR ([tblInvestigations].[No]=[Forms]![frmMain]![No]))
AND ((IsNull([Forms]![frmMain]![Ref1])) OR ([tblInvestigations].[Ref1]=[Forms]![frmMain]![Ref1]))
AND ((IsNull([Forms]![frmMain]![Method])) OR ([tblInvestigations].[Method]=[Forms]![frmMain]![Method]))

But when you save the query and try to run it, you get "query too complex" because Access has tried to "normalize" the ANDed ORs. Result is too big for one post. See the next...
 
What Access does to the query is this:

Code: 
PARAMETERS [Forms]![frmMain]![Status] Text ( 255 );
SELECT tblInvestigations.[No], tblInvestigations.[Date Logged], tblInvestigations.Investigator, tblInvestigations.Section, tblInvestigations.Source, tblInvestigations.Method, tblInvestigations.Ref1, tblInvestigations.Ref2, tblInvestigations.Issue, tblInvestigations.[Root Cause], tblInvestigations.[Due Date], tblInvestigations.Analyst, tblInvestigations.Technical, tblInvestigations.Closed, IIf([ClosedTest]=-1,"CLOSED",IIf([TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=-1 And [ClosedTest]=0,"COMPLETE",IIf([AnalystTest]=-1 And [TechnicalTest]=-1 And [ClosedTest]=0,"AUTHORISED",IIf([TechnicalTest]=0 And [AnalystTest]=0 And [ClosedTest]=0 And [OverdueTest]=-1,"OVERDUE",IIf([OpenTest]=-1,"OPEN")))))) AS Status, tblInvestigations.Notes, tblInvestigations.[Evidence Only], IIf(IsNull([Analyst]) And [Due Date]<Date(),True,False) AS OverdueTest, IIf(IsNull([Technical]) And IsNull([Closed]),IIf(IsNull([Analyst]),False,True),False) AS AnalystTest, IIf(IsNull([Closed]),IIf(IsNull([Technical]),False,True),False) AS TechnicalTest, IIf(IsNull([Closed]),False,True) AS ClosedTest, IIf(IsNull([Analyst]) And ([Due Date]>=Date()),True,False) AS OpenTest
FROM tblInvestigations
WHERE ((([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR ((([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR ((([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR ((([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ...

Second part next...
 
.. and here's the rest:

Code: 
((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Method]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Method]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Ref1]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![No]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![No]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL") AND ((IsNull([Forms]![frmMain]![Source]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Forms]![frmMain]![Source]))<>False) AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="ALL")) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])<Date()) AND (([Forms]![frmMain]![Status])="OVERDUE") AND ((IsNull([Analyst]))<>False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="COMPLETE") AND ((IsNull([Analyst]))=False) AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="AUTHORISED") AND ((IsNull([Technical]))=False) AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND (([Forms]![frmMain]![Status])="CLOSED") AND ((IsNull([Closed]))=False)) OR (((tblInvestigations.[No])=[Forms]![frmMain]![No]) AND ((tblInvestigations.Source)=[Forms]![frmMain]![Source]) AND ((tblInvestigations.Method)=[Forms]![frmMain]![Method]) AND ((tblInvestigations.Ref1)=[Forms]![frmMain]![Ref1]) AND ((tblInvestigations.[Due Date])>=Date()) AND (([Forms]![frmMain]![Status])="OPEN") AND ((IsNull([Analyst]))<>False));

It's getting late here, so I need to look at this again in the morning after a few cups of coffee. Don't despair.
 
thanks texan, I appreciate the help and look forward to a resolution. Enjoy your evening.
 
Well, what you want to do cannot be done with query parameters. It's not uncommon to want to use parameters like this to check for a value in a control and apply a filter, but ignore the filter if the control is empty (Null). Typically, your criteria will look something like:

(([FieldInTable] = [Forms]![MyForm]![FieldFilter]) OR ([Forms]![MyForm]![FieldFilter] Is Null))

That's cool for up to 3 filters applied this way, but it falls down with more because the SQL Parser tries to "optimize" the query for you. For example, the parser changes (for two filters like this):

(([FieldInTable1] = [Forms]![MyForm]![FieldFilter1]) OR ([Forms]![MyForm]![FieldFilter1] Is Null)) AND
(([FieldInTable2] = [Forms]![MyForm]![FieldFilter2]) OR ([Forms]![MyForm]![FieldFilter2] Is Null))

To:
(([FieldInTable1] = [Forms]![MyForm]![FieldFilter1]) AND ([FieldInTable2] = [Forms]![MyForm]![FieldFilter2])) OR
(([FieldInTable1] = [Forms]![MyForm]![FieldFilter1]) AND ([Forms]![MyForm]![FieldFilter2] Is Null)) OR
(([FieldInTable2] = [Forms]![MyForm]![FieldFilter2]) AND ([Forms]![MyForm]![FieldFilter1] Is Null)) OR
(([Forms]![MyForm]![FieldFilter1] Is Null) AND ([Forms]![MyForm]![FieldFilter2] Is Null))

You can imagine how this "explodes" when you add the third, fourth, etc. filters.

You have been getting away with it because you're masking the straight boolean tests by using the IIf function. The parser leaves what you entered alone, but the result is a terribly inefficient filter that can take minutes to run if you have a lot of rows. You're attempting to apply six filters like this!

The real solution is to dynamically build and apply the correct filter in code and cut out all the Is Null nonsense. I started to work on this for you this morning, but I ran into a snag. For Status, you have these values:

ALL;OUTSTANDING;OPEN;OVERDUE;COMPLETE;AUTHORISED;CLOSED

From your original query, I can figure them all out except for Outstanding. Here's what I have found:

ALL: No filters
OUTSTANDING: don't know
OPEN: Analyst not assigned, and the Due Date is in the future (OpenTest)
OVERDUE: Analyst not assigned, and the Due Date is in the past (OverdueTest)
COMPLETE: Values have NOT been assigned to any of: Technical, Analyst, and Closed (AnalystTest)
AUTHORIZED: It is not closed, but an Analyst has been assigned (TechnicalTest)
CLOSED: Closed is set (ClosedTest)

Attached is your database fixed with everything but Outstanding implemented. Basically, I've blown away all your parameters in the query and have implemented everything in code using dynamic filters.

By the way, "Section" is a reserved word - it's a property of every Form and Report. To reference your Section control, I had to use Me!Section instead of Me.Section (a reference to the Form property).

By the way #2: The filtering would run much faster if you were to put indexes on the Investigator, Source, Method, Section, and Ref1 fields.
 

Attachments

thanks your a genius, I had to modify the criteria to make sure I was getting the records I required, but using the filter by code never even occured to me.

Yes I will take your advice and use indexing, just wondering if theres anyway to have more than 3 colours for conditional formating the status field on the form in access 2000. i.e. for the other status' Open and Closed.

Really appreciate all the time you spared me.
I will sure be back for more help. Currently I am churning out charts but having trouble adding them to the tabbed form on the page two of the tabbed form, yet I can copy them in after creating them in a normal form, but having difficulty retaining any resizing or changes
thanks again
 
It was a fun thing to put together. Glad to hear you could tweak my code to get the filters to work the way you wanted. I assume you've successfully added "Outstanding."

Sorry, but Conditional Formatting has always been limited to no more than three criteria.

Are you using Pivot Charts? They can be a bear to work with. Be sure you set the Link Child / Link Master properties of the subform in which you embed the chart to get the correct results.
 
You must log in or register to reply here.

Similar threads

medihub_guy
Solved Searching between two dates just doesnt seem to work properly
Replies
15
Views
810
Pat Hartman
P
A
Solved Referring to a control on a subform from the parent form.
Replies
3
Views
496
AlliCarr
A
A
Solved SQL query advise for inventory item and set
Replies
11
Views
1,341
atzdgreat
A
mloucel
Solved SQL Help..
Replies
10
Views
592
mloucel
mloucel
L
Forecolor on or off
Replies
7
Views
877
tecrms
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom