No records found

primagic · Oct 25, 2010

I have the following sql statement as a record source for a subform. I know there should be records being displayed however there are no records being displayed when I open the form.

I remove the criteria linked to the LoggedInAs and it seems to pull through records fine. Any ideas?

Code:

SELECT dbo_Leads.*, dbo_Leads.LeadID, dbo_Leads.Title, dbo_Leads.Firstname, dbo_Leads.Surname, dbo_Leads.OutcomeID, dbo_Leads.NextTaskTime, dbo_Leads.OverallRankingScore, dbo_Leads.StatusScore, dbo_Leads.TelephoneScore, dbo_Leads.LoanSizeScore, dbo_Leads.DateAddedScore, dbo_Leads.LoanTypeScore, dbo_Leads.EquityScore, dbo_Leads.SourceScore, dbo_Leads.OutcomeLastModified, dbo_Leads.Adviser, dbo_Leads.TempAdviserLock, dbo_Leads.NextTaskTimeLock, dbo_Leads.LeadSource, dbo_Leads.DateAdded
FROM dbo_Leads
WHERE (((dbo_Leads.OutcomeID)="Pending") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="No Answer") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Engaged") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Future Business") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Scheduled Call Unsuccessful") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Pending") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="No Answer") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Engaged") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Future Business") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Scheduled Call Unsuccessful") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free") AND ((dbo_Leads.LeadSource) Is Null Or (dbo_Leads.LeadSource) In (SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources INNER JOIN dbo_LeadSources ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR (((dbo_Leads.OutcomeID)="Pending") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="No Answer") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Engaged") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Future Business") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Scheduled Call Unsuccessful") AND ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Pending") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="No Answer") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Engaged") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Future Business") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR (((dbo_Leads.OutcomeID)="Scheduled Call Unsuccessful") AND ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free"))
ORDER BY dbo_Leads.DateAdded DESC , dbo_Leads.LTVPercentage;

primagic · Oct 25, 2010

Forgot to mention, if I execute the query and type in the parameter then the records display correctly

Just not through a form

dhookom · Oct 25, 2010

Try open the debug window (Press Ctrl+G) and type

Code:

?[Forms]![frmNextTask]![LoggedInAs]

Do you see what you expect to see?

Duane
Hook'D on Access
MS Access MVP

primagic · Oct 25, 2010

Yes I do

Hap007 · Oct 25, 2010

Hi,

I would issue Requery statement in the 'AfterUpdate' Property of the text box LoggedInAs

IE me.Requery

Hope This Helps,
Hap...

Access Developer [pc]

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

primagic · Oct 25, 2010

No never worked. Thanks anyway

dhookom · Oct 25, 2010

Is the subform set to data entry?
Are there any values in the Link Master/Child properties of the subform control?

Duane
Hook'D on Access
MS Access MVP

primagic · Oct 25, 2010

The subform is not set to data entry

If i have in the master field 'LoggedInAs' and in the Child field I have 'dbo_Leads.Adviser' this will work, however, it wont pull through the null 'Adviser' obviously

dhookom · Oct 25, 2010

I'm surprised it works with the extra ";" in the SQL.

Your query is complex enough to choke most SQL gurus. When I can't get something to work, I simplify down to the minimal to get it to work. Then I add back complexity one at a time until it either continues to work or chokes.

Duane
Hook'D on Access
MS Access MVP

primagic · Oct 25, 2010

i have already tried even just putting Forms!frmNextTask!LoggedInAs in the Adviser column as the only criteria and still doesnt work.

dhookom · Oct 25, 2010

What did you do to try to simplify in terms of removing tables? Are you going to provide a reason why there is an extra ";" in the SQL?

Duane
Hook'D on Access
MS Access MVP

Hap007 · Oct 26, 2010

Hi,

Sorry, I missed that the data was in a sub form.

I would issue Requery statement in the 'AfterUpdate' Property of the text box LoggedInAsIE

So, the statement in the 'AfterUpdate' for LoggedInASIE would be:
me.subFormName???.Requery

Another thing to just try would be to place a default value
in the LoggedInASIE field and see if the data shows up when the form/sub form opens.

Also, I have seen problems with sub forms, and doing a Compact/repair on the DB sometimes corrects those issues.

Hope This Helps,
Hap...

Access Developer [pc]

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

TheAceMan1 · Oct 27, 2010

All!

Here's the SQL formatted for viewing so all the subqueries can easily be seen:

Code:

[blue]SELECT dbo_Leads.*
FROM dbo_Leads
WHERE (((dbo_Leads.OutcomeID)="Pending") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="No Answer") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Engaged") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Future Business") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Scheduled Call Unsuccessful") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Pending") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="No Answer") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Engaged") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Future Business") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND 
      ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
      ((dbo_Leads.TempAdviserLock)="Free") AND 
      ((dbo_Leads.NextTaskTimeLock)="Free") AND 
      ((dbo_Leads.LeadSource) Is Null Or 
       (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Scheduled Call Unsuccessful") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free") AND 
       ((dbo_Leads.LeadSource) Is Null Or 
        (dbo_Leads.LeadSource) In 

(SELECT dbo_LeadSources.LeadSource
FROM dbo_NextTaskCriteriaSources 
INNER JOIN dbo_LeadSources 
ON int(dbo_NextTaskCriteriaSources.Source) = dbo_LeadSources.LeadSourceID
WHERE (((dbo_NextTaskCriteriaSources.EmployeeID)=[Forms]![frmNextTask]![LoggedInAs]))
GROUP BY dbo_LeadSources.LeadSource;))) OR 
      (((dbo_Leads.OutcomeID)="Pending") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="No Answer") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Engaged") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Future Business") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID="Scheduled Call Unsuccessful") AND 
       ((dbo_Leads.Adviser)=[Forms]![frmNextTask]![LoggedInAs]) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Pending") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="No Answer") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Engaged") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Future Business") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Waiting on Base Rate Moving") AND 
       ((dbo_Leads.Adviser) Is Null) AND 
       ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free")) OR 
      (((dbo_Leads.OutcomeID)="Scheduled Call Unsuccessful") AND 
       ((dbo_Leads.Adviser) Is Null) AND ((dbo_Leads.TempAdviserLock)="Free") AND 
       ((dbo_Leads.NextTaskTimeLock)="Free"))
ORDER BY dbo_Leads.DateAdded DESC , dbo_Leads.LTVPercentage;[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

TheAceMan1 · Oct 27, 2010

How are ya primagic . . .

If I had to write a query that complex ... I could never trust it!

I'm not understanding the same subquery nested within itself 11 times. Its as if your trying to be recursive. Also, I've never seen, used or heard of criteria being used in a [blue]Group By[/blue] clause ... and I thought fields in [blue]Group By[/blue] were limited to ten! Your last [blue]Group By[/blue] is really something.

Perhaps if you tell us what your trying to do with the query ... give us some background ... we can help you better.

[blue]dhookom[/blue] is right in that you need to [blue]simplify![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

primagic · Oct 27, 2010

Ok I managed to get it working believe it or not. I created a function that would store a global variable (loggedInas) and in the query that is referencing the loggedinas field just call the function and it seems to be working.

I didnt write the query and I do agree its over complex

TheAceMan1 · Oct 27, 2010

primagic . . .

I understand it works and returns records ... but how can you tell its working properly or returning the proper records? [surprise]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]

Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

No records found

primagic

IS-IT--Management

primagic

IS-IT--Management

dhookom

Programmer

primagic

IS-IT--Management

Hap007

MIS

primagic

IS-IT--Management

dhookom

Programmer

primagic

IS-IT--Management

dhookom

Programmer

primagic

IS-IT--Management

dhookom

Programmer

Hap007

MIS

TheAceMan1

Programmer

TheAceMan1

Programmer

primagic

IS-IT--Management

TheAceMan1

Programmer

Similar threads

Part and Inventory Search

Sponsor