Query running slow [General]

Prev: Access 2003 and Windows 7
Next: When is the latest version not the latest version

From: Daniel on 14 Feb 2010 20:45

Hi All,

I have got a query with 7-8 tables. Some of them are linked from SQL
server and some of them are local Access table. I just updated this
query and added another creteria and it became very slow (about 10
minutes) to return the result. If I remove one of the local table, it
just needs around 5-8 seconds. But I do need this table in the query.

There are three fields in the table, one of Number type field is
primary key. The creteria is nothing to do with the fields in the
local table.

What's the possible reason for that?

Any help will be appreciated.

Thanks
Daniel

From: Jerry Whittle on 14 Feb 2010 22:13

Please post the SQL statement for this query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Daniel" wrote:

> Hi All,
>
> I have got a query with 7-8 tables. Some of them are linked from SQL
> server and some of them are local Access table. I just updated this
> query and added another creteria and it became very slow (about 10
> minutes) to return the result. If I remove one of the local table, it
> just needs around 5-8 seconds. But I do need this table in the query.
>
> There are three fields in the table, one of Number type field is
> primary key. The creteria is nothing to do with the fields in the
> local table.
>
> What's the possible reason for that?
>
> Any help will be appreciated.
>
> Thanks
> Daniel
> .
>

From: Daniel on 14 Feb 2010 22:32

On Feb 15, 2:13 pm, Jerry Whittle
<JerryWhit...(a)discussions.microsoft.com> wrote:
> Please post the SQL statement for this query.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
>
> "Daniel" wrote:
> > Hi All,
>
> > I have got a query with 7-8 tables. Some of them are linked from SQL
> > server and some of them are local Access table. I just updated this
> > query and added another creteria and it became very slow (about 10
> > minutes) to return the result. If I remove one of the local table, it
> > just needs around 5-8 seconds. But I do need this table in the query.
>
> > There are three fields in the table, one of Number type field is
> > primary key. The creteria is nothing to do with the fields in the
> > local table.
>
> > What's the possible reason for that?
>
> > Any help will be appreciated.
>
> > Thanks
> > Daniel
> > .- Hide quoted text -
>
> - Show quoted text -

It is a big query, TblTempPackingSchedule_Step3 is the local table I
mentioned. I have compact and repaired the database but no luck.

SELECT TblDbMaintenanceADS.DateDelivery,
TblDbMaintenanceADS.PackingSelection,
TblDbMaintenanceADS.DateEstDelivery, TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.MaintADSRef, TblDbMaintenanceADS.MaintADSRefNo,
TblDbMaintenanceADS.SiteWorkOnly, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS sort_DateDelivery, IIf([TblDbMaintenanceADS]!
[Pickup]=-1,"Pick Up",IIf([TblDbMaintenanceADS]!
[VehicalType]="Direct","Direct",IIf([TblDbMaintenanceADS]!
[VehicalType]="Crate","Fulton",""))) AS Pickup1,
IIf(IsNull([DateEstDelivery]) Or
IsNull([PackingLeadTime]),Null,Workday([DateEstDelivery],
[PackingLeadTime])) AS InTransit1, IIf(IsNull([TblDbMaintenanceADS]!
[DateDelivery]),2,1) AS Prio1, TblDbMaintenanceADS.PackingDate,
TblDbMaintenanceADS.DateOnSiteComp, TblDbMaintenanceADS.Packed,
TblDbMaintenanceADS.MaintADSType,
TblDbMaintenanceADS.DateOnSiteRequest,
TblSuburbListingsBranch.InransitLeadTime,
TblSuburbListingsBranch.BranchLeadTime,
IIf(IsNull([DateOnSiteRequest]),"N/A",Workday([DateOnSiteRequest],-
[BranchLeadTime])) AS BranchReq, IIf([BranchReq]="N/A","N/
A",Workday([BranchReq],-[InransitLeadTime])) AS ReqTransit,
TblDbMaintenanceADS.MaintADSId, TblDbMaintenanceADS.DateADSMaintComp,
TblSuburbListingsBranch.LocationID,
TblTempPackingSchedule_Step3.Complete,
IIf(IsNull([Complete]),"Complete","") AS Complete1,
TblDBADSFloor.Process AS ManufRespon,
Q_TblCrateIDAvailability.CrateID, TblDbMaintenanceADS.TransitDone,
TblDbMaintenanceADS.InTransit,
TblDbMaintenanceADS.TransittedTimestamp, TblDbMaintenanceADS.Pickup,
TblDbMaintenanceADS.DeliverConf, TblDbMaintenanceADS.BranchRecDone,
Q_TblCrateIDAvailability.DocketPrinted,
TblDbMaintenanceADS.NationalReceiveDone,
TblDbMaintenanceADS.NationalReceiveDate,
TblDbMaintenanceADS.BranchRequestDone,
TblDbMaintenanceADS.BranchRequestDate,
TblDbMaintenanceADS.NationalTransitDone,
TblDbMaintenanceADS.NationalTransitDate,
TblDbMaintenanceADS.TransitDocketNo,
TblDbMaintenanceADS.SelectForTransit, TblDbMaintenanceADS.BanchRec,
InStr([CustCompanyDimension3],"Dist") AS test, TblDBADSFloor.Qty AS
Packages, TblDBADSFloor.PackageConfirmation, TblDBADSFloor.BayNo,
TblDBADSFloor.MaintNumber AS MaintNo, TblDBADSFloor.Process AS
Department, TblDBADSFloor.ADSDone,
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location]))))
AS Location1
FROM (((((((tblClaytonsJobData LEFT JOIN TblClaytonsJobsDetails ON
tblClaytonsJobData.JobNumber = TblClaytonsJobsDetails.JobNumber) LEFT
JOIN TblSuburbListingsBranch ON
TblClaytonsJobsDetails.DedicatedLocation =
TblSuburbListingsBranch.LocationID) LEFT JOIN TblDbCustomerCompany ON
tblClaytonsJobData.Customer = TblDbCustomerCompany.CustCompanyName)
RIGHT JOIN TblDBADSFloor ON tblClaytonsJobData.JobNumber =
TblDBADSFloor.JobNumber) LEFT JOIN TblDBADSDPAndFloor ON
TblDBADSFloor.MaintNumber = TblDBADSDPAndFloor.MaintNumber) LEFT JOIN
TblDbMaintenanceADS ON TblDBADSDPAndFloor.MaintADSID =
TblDbMaintenanceADS.MaintADSId) LEFT JOIN TblTempPackingSchedule_Step3
ON TblDbMaintenanceADS.MaintADSId =
TblTempPackingSchedule_Step3.MaintADSId) LEFT JOIN
Q_TblCrateIDAvailability ON TblDbMaintenanceADS.MaintADSId =
Q_TblCrateIDAvailability.MaintNo
WHERE (((TblDbMaintenanceADS.MaintADSType)="E" Or
(TblDbMaintenanceADS.MaintADSType)="F") AND
((TblDbMaintenanceADS.SiteWorkOnly) Is Null Or
(TblDbMaintenanceADS.SiteWorkOnly)=0) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True)) OR
(((TblDbMaintenanceADS.MaintADSType)="H") AND
((TblDbMaintenanceADS.SiteWorkOnly)=True) AND
((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
((TblSuburbListingsBranch.LocationID)<>1) AND
((TblDbMaintenanceADS.TransitDone)=0 Or
(TblDbMaintenanceADS.TransitDone) Is Null) AND
((InStr([CustCompanyDimension3],"Dist"))=0 Or
(InStr([CustCompanyDimension3],"Dist")) Is Null) AND
((TblDBADSFloor.ADSDone)=True))
ORDER BY IIf(IsNull([TblDbMaintenanceADS]![DateDelivery]),2,1),
IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location])))),
TblDbMaintenanceADS.DateDelivery, TblDbMaintenanceADS.PackingDate;

From: Jerry Whittle on 15 Feb 2010 09:43

That is one ugly monster!

Is the TblTempPackingSchedule_Step3.MaintADSId field the primary key or
indexed?

The same question goes for TblDbMaintenanceADS.MaintADSId?

What happens when you remove either or both of these two lines:
TblTempPackingSchedule_Step3.Complete,
IIf(IsNull([Complete]),"Complete","") AS Complete1,

I'm especially interested in the second line as it looks like you are
dealing with nulls plus displaying the same data twice.

One thing that might really speed things up is to create a view in SQL
Server that joins all the tables needed there into one query. That way a lot
of the work would be done by the SQL Server engine and you wouldn't need to
bring so much data into Access for it crunch.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Daniel" wrote:

> On Feb 15, 2:13 pm, Jerry Whittle
> <JerryWhit...(a)discussions.microsoft.com> wrote:
> > Please post the SQL statement for this query.
> > --
> > Jerry Whittle, Microsoft Access MVP
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> >
> >
> >
> > "Daniel" wrote:
> > > Hi All,
> >
> > > I have got a query with 7-8 tables. Some of them are linked from SQL
> > > server and some of them are local Access table. I just updated this
> > > query and added another creteria and it became very slow (about 10
> > > minutes) to return the result. If I remove one of the local table, it
> > > just needs around 5-8 seconds. But I do need this table in the query.
> >
> > > There are three fields in the table, one of Number type field is
> > > primary key. The creteria is nothing to do with the fields in the
> > > local table.
> >
> > > What's the possible reason for that?
> >
> > > Any help will be appreciated.
> >
> > > Thanks
> > > Daniel
> > > .- Hide quoted text -
> >
> > - Show quoted text -
>
> It is a big query, TblTempPackingSchedule_Step3 is the local table I
> mentioned. I have compact and repaired the database but no luck.
>
> SELECT TblDbMaintenanceADS.DateDelivery,
> TblDbMaintenanceADS.PackingSelection,
> TblDbMaintenanceADS.DateEstDelivery, TblDbMaintenanceADS.MaintADSType,
> TblDbMaintenanceADS.MaintADSRef, TblDbMaintenanceADS.MaintADSRefNo,
> TblDbMaintenanceADS.SiteWorkOnly, IIf(IsNull([TblDbMaintenanceADS]!
> [DateDelivery]),2,1) AS sort_DateDelivery, IIf([TblDbMaintenanceADS]!
> [Pickup]=-1,"Pick Up",IIf([TblDbMaintenanceADS]!
> [VehicalType]="Direct","Direct",IIf([TblDbMaintenanceADS]!
> [VehicalType]="Crate","Fulton",""))) AS Pickup1,
> IIf(IsNull([DateEstDelivery]) Or
> IsNull([PackingLeadTime]),Null,Workday([DateEstDelivery],
> [PackingLeadTime])) AS InTransit1, IIf(IsNull([TblDbMaintenanceADS]!
> [DateDelivery]),2,1) AS Prio1, TblDbMaintenanceADS.PackingDate,
> TblDbMaintenanceADS.DateOnSiteComp, TblDbMaintenanceADS.Packed,
> TblDbMaintenanceADS.MaintADSType,
> TblDbMaintenanceADS.DateOnSiteRequest,
> TblSuburbListingsBranch.InransitLeadTime,
> TblSuburbListingsBranch.BranchLeadTime,
> IIf(IsNull([DateOnSiteRequest]),"N/A",Workday([DateOnSiteRequest],-
> [BranchLeadTime])) AS BranchReq, IIf([BranchReq]="N/A","N/
> A",Workday([BranchReq],-[InransitLeadTime])) AS ReqTransit,
> TblDbMaintenanceADS.MaintADSId, TblDbMaintenanceADS.DateADSMaintComp,
> TblSuburbListingsBranch.LocationID,
> TblTempPackingSchedule_Step3.Complete,
> IIf(IsNull([Complete]),"Complete","") AS Complete1,
> TblDBADSFloor.Process AS ManufRespon,
> Q_TblCrateIDAvailability.CrateID, TblDbMaintenanceADS.TransitDone,
> TblDbMaintenanceADS.InTransit,
> TblDbMaintenanceADS.TransittedTimestamp, TblDbMaintenanceADS.Pickup,
> TblDbMaintenanceADS.DeliverConf, TblDbMaintenanceADS.BranchRecDone,
> Q_TblCrateIDAvailability.DocketPrinted,
> TblDbMaintenanceADS.NationalReceiveDone,
> TblDbMaintenanceADS.NationalReceiveDate,
> TblDbMaintenanceADS.BranchRequestDone,
> TblDbMaintenanceADS.BranchRequestDate,
> TblDbMaintenanceADS.NationalTransitDone,
> TblDbMaintenanceADS.NationalTransitDate,
> TblDbMaintenanceADS.TransitDocketNo,
> TblDbMaintenanceADS.SelectForTransit, TblDbMaintenanceADS.BanchRec,
> InStr([CustCompanyDimension3],"Dist") AS test, TblDBADSFloor.Qty AS
> Packages, TblDBADSFloor.PackageConfirmation, TblDBADSFloor.BayNo,
> TblDBADSFloor.MaintNumber AS MaintNo, TblDBADSFloor.Process AS
> Department, TblDBADSFloor.ADSDone,
> IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location]))))
> AS Location1
> FROM (((((((tblClaytonsJobData LEFT JOIN TblClaytonsJobsDetails ON
> tblClaytonsJobData.JobNumber = TblClaytonsJobsDetails.JobNumber) LEFT
> JOIN TblSuburbListingsBranch ON
> TblClaytonsJobsDetails.DedicatedLocation =
> TblSuburbListingsBranch.LocationID) LEFT JOIN TblDbCustomerCompany ON
> tblClaytonsJobData.Customer = TblDbCustomerCompany.CustCompanyName)
> RIGHT JOIN TblDBADSFloor ON tblClaytonsJobData.JobNumber =
> TblDBADSFloor.JobNumber) LEFT JOIN TblDBADSDPAndFloor ON
> TblDBADSFloor.MaintNumber = TblDBADSDPAndFloor.MaintNumber) LEFT JOIN
> TblDbMaintenanceADS ON TblDBADSDPAndFloor.MaintADSID =
> TblDbMaintenanceADS.MaintADSId) LEFT JOIN TblTempPackingSchedule_Step3
> ON TblDbMaintenanceADS.MaintADSId =
> TblTempPackingSchedule_Step3.MaintADSId) LEFT JOIN
> Q_TblCrateIDAvailability ON TblDbMaintenanceADS.MaintADSId =
> Q_TblCrateIDAvailability.MaintNo
> WHERE (((TblDbMaintenanceADS.MaintADSType)="E" Or
> (TblDbMaintenanceADS.MaintADSType)="F") AND
> ((TblDbMaintenanceADS.SiteWorkOnly) Is Null Or
> (TblDbMaintenanceADS.SiteWorkOnly)=0) AND
> ((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
> ((TblSuburbListingsBranch.LocationID)<>1) AND
> ((TblDbMaintenanceADS.TransitDone)=0 Or
> (TblDbMaintenanceADS.TransitDone) Is Null) AND
> ((InStr([CustCompanyDimension3],"Dist"))=0 Or
> (InStr([CustCompanyDimension3],"Dist")) Is Null) AND
> ((TblDBADSFloor.ADSDone)=True)) OR
> (((TblDbMaintenanceADS.MaintADSType)="H") AND
> ((TblDbMaintenanceADS.SiteWorkOnly)=True) AND
> ((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
> ((TblSuburbListingsBranch.LocationID)<>1) AND
> ((TblDbMaintenanceADS.TransitDone)=0 Or
> (TblDbMaintenanceADS.TransitDone) Is Null) AND
> ((InStr([CustCompanyDimension3],"Dist"))=0 Or
> (InStr([CustCompanyDimension3],"Dist")) Is Null) AND
> ((TblDBADSFloor.ADSDone)=True))
> ORDER BY IIf(IsNull([TblDbMaintenanceADS]![DateDelivery]),2,1),
> IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location])))),
> TblDbMaintenanceADS.DateDelivery, TblDbMaintenanceADS.PackingDate;
> .
>

From: Daniel on 15 Feb 2010 20:54

On Feb 16, 1:43 am, Jerry Whittle
<JerryWhit...(a)discussions.microsoft.com> wrote:
> That is one ugly monster!
>
> Is the TblTempPackingSchedule_Step3.MaintADSId field the primary key or
> indexed?
>
> The same question goes for TblDbMaintenanceADS.MaintADSId?
>
> What happens when you remove either or both of these two lines:
> TblTempPackingSchedule_Step3.Complete,
> IIf(IsNull([Complete]),"Complete","") AS Complete1,
>
> I'm especially interested in the second line as it looks like you are
> dealing with nulls plus displaying the same data twice.
>
> One thing that might really speed things up is to create a view in SQL
> Server that joins all the tables needed there into one query. That way a lot
> of the work would be done by the SQL Server engine and you wouldn't need to
> bring so much data into Access for it crunch.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
>
> "Daniel" wrote:
> > On Feb 15, 2:13 pm, Jerry Whittle
> > <JerryWhit...(a)discussions.microsoft.com> wrote:
> > > Please post the SQL statement for this query.
> > > --
> > > Jerry Whittle, Microsoft Access MVP
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> > > "Daniel" wrote:
> > > > Hi All,
>
> > > > I have got a query with 7-8 tables. Some of them are linked from SQL
> > > > server and some of them are local Access table. I just updated this
> > > > query and added another creteria and it became very slow (about 10
> > > > minutes) to return the result. If I remove one of the local table, it
> > > > just needs around 5-8 seconds. But I do need this table in the query.
>
> > > > There are three fields in the table, one of Number type field is
> > > > primary key. The creteria is nothing to do with the fields in the
> > > > local table.
>
> > > > What's the possible reason for that?
>
> > > > Any help will be appreciated.
>
> > > > Thanks
> > > > Daniel
> > > > .- Hide quoted text -
>
> > > - Show quoted text -
>
> > It is a big query, TblTempPackingSchedule_Step3 is the local table I
> > mentioned. I have compact and repaired the database but no luck.
>
> > SELECT TblDbMaintenanceADS.DateDelivery,
> > TblDbMaintenanceADS.PackingSelection,
> > TblDbMaintenanceADS.DateEstDelivery, TblDbMaintenanceADS.MaintADSType,
> > TblDbMaintenanceADS.MaintADSRef, TblDbMaintenanceADS.MaintADSRefNo,
> > TblDbMaintenanceADS.SiteWorkOnly, IIf(IsNull([TblDbMaintenanceADS]!
> > [DateDelivery]),2,1) AS sort_DateDelivery, IIf([TblDbMaintenanceADS]!
> > [Pickup]=-1,"Pick Up",IIf([TblDbMaintenanceADS]!
> > [VehicalType]="Direct","Direct",IIf([TblDbMaintenanceADS]!
> > [VehicalType]="Crate","Fulton",""))) AS Pickup1,
> > IIf(IsNull([DateEstDelivery]) Or
> > IsNull([PackingLeadTime]),Null,Workday([DateEstDelivery],
> > [PackingLeadTime])) AS InTransit1, IIf(IsNull([TblDbMaintenanceADS]!
> > [DateDelivery]),2,1) AS Prio1, TblDbMaintenanceADS.PackingDate,
> > TblDbMaintenanceADS.DateOnSiteComp, TblDbMaintenanceADS.Packed,
> > TblDbMaintenanceADS.MaintADSType,
> > TblDbMaintenanceADS.DateOnSiteRequest,
> > TblSuburbListingsBranch.InransitLeadTime,
> > TblSuburbListingsBranch.BranchLeadTime,
> > IIf(IsNull([DateOnSiteRequest]),"N/A",Workday([DateOnSiteRequest],-
> > [BranchLeadTime])) AS BranchReq, IIf([BranchReq]="N/A","N/
> > A",Workday([BranchReq],-[InransitLeadTime])) AS ReqTransit,
> > TblDbMaintenanceADS.MaintADSId, TblDbMaintenanceADS.DateADSMaintComp,
> > TblSuburbListingsBranch.LocationID,
> > TblTempPackingSchedule_Step3.Complete,
> > IIf(IsNull([Complete]),"Complete","") AS Complete1,
> > TblDBADSFloor.Process AS ManufRespon,
> > Q_TblCrateIDAvailability.CrateID, TblDbMaintenanceADS.TransitDone,
> > TblDbMaintenanceADS.InTransit,
> > TblDbMaintenanceADS.TransittedTimestamp, TblDbMaintenanceADS.Pickup,
> > TblDbMaintenanceADS.DeliverConf, TblDbMaintenanceADS.BranchRecDone,
> > Q_TblCrateIDAvailability.DocketPrinted,
> > TblDbMaintenanceADS.NationalReceiveDone,
> > TblDbMaintenanceADS.NationalReceiveDate,
> > TblDbMaintenanceADS.BranchRequestDone,
> > TblDbMaintenanceADS.BranchRequestDate,
> > TblDbMaintenanceADS.NationalTransitDone,
> > TblDbMaintenanceADS.NationalTransitDate,
> > TblDbMaintenanceADS.TransitDocketNo,
> > TblDbMaintenanceADS.SelectForTransit, TblDbMaintenanceADS.BanchRec,
> > InStr([CustCompanyDimension3],"Dist") AS test, TblDBADSFloor.Qty AS
> > Packages, TblDBADSFloor.PackageConfirmation, TblDBADSFloor.BayNo,
> > TblDBADSFloor.MaintNumber AS MaintNo, TblDBADSFloor.Process AS
> > Department, TblDBADSFloor.ADSDone,
> > IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location]))))
> > AS Location1
> > FROM (((((((tblClaytonsJobData LEFT JOIN TblClaytonsJobsDetails ON
> > tblClaytonsJobData.JobNumber = TblClaytonsJobsDetails.JobNumber) LEFT
> > JOIN TblSuburbListingsBranch ON
> > TblClaytonsJobsDetails.DedicatedLocation =
> > TblSuburbListingsBranch.LocationID) LEFT JOIN TblDbCustomerCompany ON
> > tblClaytonsJobData.Customer = TblDbCustomerCompany.CustCompanyName)
> > RIGHT JOIN TblDBADSFloor ON tblClaytonsJobData.JobNumber =
> > TblDBADSFloor.JobNumber) LEFT JOIN TblDBADSDPAndFloor ON
> > TblDBADSFloor.MaintNumber = TblDBADSDPAndFloor.MaintNumber) LEFT JOIN
> > TblDbMaintenanceADS ON TblDBADSDPAndFloor.MaintADSID =
> > TblDbMaintenanceADS.MaintADSId) LEFT JOIN TblTempPackingSchedule_Step3
> > ON TblDbMaintenanceADS.MaintADSId =
> > TblTempPackingSchedule_Step3.MaintADSId) LEFT JOIN
> > Q_TblCrateIDAvailability ON TblDbMaintenanceADS.MaintADSId =
> > Q_TblCrateIDAvailability.MaintNo
> > WHERE (((TblDbMaintenanceADS.MaintADSType)="E" Or
> > (TblDbMaintenanceADS.MaintADSType)="F") AND
> > ((TblDbMaintenanceADS.SiteWorkOnly) Is Null Or
> > (TblDbMaintenanceADS.SiteWorkOnly)=0) AND
> > ((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
> > ((TblSuburbListingsBranch.LocationID)<>1) AND
> > ((TblDbMaintenanceADS.TransitDone)=0 Or
> > (TblDbMaintenanceADS.TransitDone) Is Null) AND
> > ((InStr([CustCompanyDimension3],"Dist"))=0 Or
> > (InStr([CustCompanyDimension3],"Dist")) Is Null) AND
> > ((TblDBADSFloor.ADSDone)=True)) OR
> > (((TblDbMaintenanceADS.MaintADSType)="H") AND
> > ((TblDbMaintenanceADS.SiteWorkOnly)=True) AND
> > ((TblDbMaintenanceADS.DateOnSiteComp) Is Null) AND
> > ((TblSuburbListingsBranch.LocationID)<>1) AND
> > ((TblDbMaintenanceADS.TransitDone)=0 Or
> > (TblDbMaintenanceADS.TransitDone) Is Null) AND
> > ((InStr([CustCompanyDimension3],"Dist"))=0 Or
> > (InStr([CustCompanyDimension3],"Dist")) Is Null) AND
> > ((TblDBADSFloor.ADSDone)=True))
> > ORDER BY IIf(IsNull([TblDbMaintenanceADS]![DateDelivery]),2,1),
> > IIf(Trim([Location])="Queensland","QLD",IIf(Trim([Location])="Victoria","VIC",IIf(Trim([Location])="Tasmania","TAS",Trim([Location])))),
> > TblDbMaintenanceADS.DateDelivery, TblDbMaintenanceADS.PackingDate;
> > .- Hide quoted text -
>
> - Show quoted text -

Hi Jerry,

Thank you for your reply. I know that's ugly. Hopefully I can find
some time to re-do it in the future. But currently, I just find
another way to fix this problem. Ranther than enter another creteria
in another line, I use

Expr1: IIf([MaintADSType]="E" Or [MaintADSType]="F",
[SiteWorkOnly],IIf([MaintADSType]="H",False,True))

to replace SiteWorkOnly Field and set creteria to false. Then it
return the result pretty quick.

Thanks again!

Daniel

| Next | Last
Pages: 1 2
Prev: Access 2003 and Windows 7
Next: When is the latest version not the latest version