N
NewbieSupreme
I have a relatively complex query that uses the WorkingDays function from
the MVP (or Ashish) site to figure turnaround time for several phases of
orders. The SQL of it is below, as is the code for the WorkingDays2
function, if it helps figure out this problem.
On a form, I have 12 textboxes whose recordsource is Dcount of different
criteria (ie. one is Dcount("*","qryTurnaroundMain","[EntryTurnaround]>2"),
which is a count of orders that took more than 2 days to enter, etc. There
are 12 of these that use Dcount and the > or < on various turnaround
calculations (expressions) from the query.
Now, if I enter the dates for the date range used in the query criteria, and
then just run the query itself (doubleclicking it in the database window),
the query executes pretty quickly. However, using the form to refresh the
textboxes that are the Dcounts takes about 50 seconds. In fact, even if I
delete all but one textbox, it still takes almost 10 seconds to show the
results. Is there something about Dcount that is taking so long, or is it
the complexity of the query, or is it the WorkingDays function, or what? Is
there a better way to design this query, or pupulate my Dcount boxes?
A secondary question would be how can I show an hourglas or progress meter
for Dcounts populating, because trying all the code I've seen as a
progress/hourglass for running a query doesn't work, which I would guess is
because it's not the query that takes a long time, but rather these Dcount
fields.
Thanks for any help, it's greatly appreciated.
Code for query is below:
SELECT ProductionData.OrderNum, ProductionData.CustNum,
CustomerList.CustName, ProductionData.DateRec, ProductionData.EnteredBy,
ProductionData.DateEntered, EmpList.EmpName, ProductionData.DesBy,
ProductionData.DateDes, OrderData.DateProd, EmpList_1.EmpName,
IIf(IsNull([DateEntered]),Null,WorkingDays2([DateRec],[DateEntered])) AS
EntryDoneTurnaround,
IIf(IsNull([DateEntered]),WorkingDays2([DateRec],Date()),Null) AS
EntryNotTurnaround,
IIf(IsNull([DateDes]),Null,WorkingDays2([DateEntered],[DateDes])) AS
DesDoneTurnaround,
IIf(IsNull([DateDes]),WorkingDays2([DateEntered],Date()),Null) AS
DesNotTurnaround,
IIf(IsNull([DateProd]),Null,WorkingDays2([DateDes],[DateProd])) AS
ProdDoneTurnaround,
IIf(IsNull([DateProd]),WorkingDays2([DateDes],Date()),Null) AS
ProdNotTurnaround,
IIf(IsNull([DateProd]),Null,WorkingDays2([DateRec],[DateProd])) AS
TotalDoneTurnaround,
IIf(IsNull([DateProd]),WorkingDays2([DateRec],Date()),Null) AS
TotalNotTurnaround
FROM (((ProductionData LEFT JOIN OrderData ON ProductionData.OrderNum =
OrderData.OrderNum) LEFT JOIN CustomerList ON ProductionData.CustNum =
CustomerList.CustID) LEFT JOIN EmpList ON ProductionData.EnteredBy =
EmpList.EmpID) LEFT JOIN EmpList AS EmpList_1 ON ProductionData.DesBy =
EmpList_1.EmpID
WHERE (((ProductionData.CustNum)=[Forms]![frmTurnaroundMain]![cboCustomer])
AND (Not (ProductionData.DateRec) Is Null And (ProductionData.DateRec)
Between [Forms]![frmTurnaroundMain]![StartDate] And
[Forms]![frmTurnaroundMain]![EndDate]) AND ((ProductionData.ABG)=0) AND
((ProductionData.EarlyBuy)=0)) OR ((Not (ProductionData.DateRec) Is Null And
(ProductionData.DateRec) Between [Forms]![frmTurnaroundMain]![StartDate] And
[Forms]![frmTurnaroundMain]![EndDate]) AND ((ProductionData.ABG)=0) AND
((ProductionData.EarlyBuy)=0) AND
(([Forms]![frmTurnaroundMain]![cboCustomer]) Is Null));
Code for WorkingDays2:
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays", dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
StartDate = StartDate + 1
Loop
WorkingDays2 = intCount
Exit_WorkingDays2:
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function
the MVP (or Ashish) site to figure turnaround time for several phases of
orders. The SQL of it is below, as is the code for the WorkingDays2
function, if it helps figure out this problem.
On a form, I have 12 textboxes whose recordsource is Dcount of different
criteria (ie. one is Dcount("*","qryTurnaroundMain","[EntryTurnaround]>2"),
which is a count of orders that took more than 2 days to enter, etc. There
are 12 of these that use Dcount and the > or < on various turnaround
calculations (expressions) from the query.
Now, if I enter the dates for the date range used in the query criteria, and
then just run the query itself (doubleclicking it in the database window),
the query executes pretty quickly. However, using the form to refresh the
textboxes that are the Dcounts takes about 50 seconds. In fact, even if I
delete all but one textbox, it still takes almost 10 seconds to show the
results. Is there something about Dcount that is taking so long, or is it
the complexity of the query, or is it the WorkingDays function, or what? Is
there a better way to design this query, or pupulate my Dcount boxes?
A secondary question would be how can I show an hourglas or progress meter
for Dcounts populating, because trying all the code I've seen as a
progress/hourglass for running a query doesn't work, which I would guess is
because it's not the query that takes a long time, but rather these Dcount
fields.
Thanks for any help, it's greatly appreciated.
Code for query is below:
SELECT ProductionData.OrderNum, ProductionData.CustNum,
CustomerList.CustName, ProductionData.DateRec, ProductionData.EnteredBy,
ProductionData.DateEntered, EmpList.EmpName, ProductionData.DesBy,
ProductionData.DateDes, OrderData.DateProd, EmpList_1.EmpName,
IIf(IsNull([DateEntered]),Null,WorkingDays2([DateRec],[DateEntered])) AS
EntryDoneTurnaround,
IIf(IsNull([DateEntered]),WorkingDays2([DateRec],Date()),Null) AS
EntryNotTurnaround,
IIf(IsNull([DateDes]),Null,WorkingDays2([DateEntered],[DateDes])) AS
DesDoneTurnaround,
IIf(IsNull([DateDes]),WorkingDays2([DateEntered],Date()),Null) AS
DesNotTurnaround,
IIf(IsNull([DateProd]),Null,WorkingDays2([DateDes],[DateProd])) AS
ProdDoneTurnaround,
IIf(IsNull([DateProd]),WorkingDays2([DateDes],Date()),Null) AS
ProdNotTurnaround,
IIf(IsNull([DateProd]),Null,WorkingDays2([DateRec],[DateProd])) AS
TotalDoneTurnaround,
IIf(IsNull([DateProd]),WorkingDays2([DateRec],Date()),Null) AS
TotalNotTurnaround
FROM (((ProductionData LEFT JOIN OrderData ON ProductionData.OrderNum =
OrderData.OrderNum) LEFT JOIN CustomerList ON ProductionData.CustNum =
CustomerList.CustID) LEFT JOIN EmpList ON ProductionData.EnteredBy =
EmpList.EmpID) LEFT JOIN EmpList AS EmpList_1 ON ProductionData.DesBy =
EmpList_1.EmpID
WHERE (((ProductionData.CustNum)=[Forms]![frmTurnaroundMain]![cboCustomer])
AND (Not (ProductionData.DateRec) Is Null And (ProductionData.DateRec)
Between [Forms]![frmTurnaroundMain]![StartDate] And
[Forms]![frmTurnaroundMain]![EndDate]) AND ((ProductionData.ABG)=0) AND
((ProductionData.EarlyBuy)=0)) OR ((Not (ProductionData.DateRec) Is Null And
(ProductionData.DateRec) Between [Forms]![frmTurnaroundMain]![StartDate] And
[Forms]![frmTurnaroundMain]![EndDate]) AND ((ProductionData.ABG)=0) AND
((ProductionData.EarlyBuy)=0) AND
(([Forms]![frmTurnaroundMain]![cboCustomer]) Is Null));
Code for WorkingDays2:
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
On Error GoTo Err_WorkingDays2
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolDate] FROM Holidays", dbOpenSnapshot)
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
intCount = 0
Do While StartDate <= EndDate
rst.FindFirst "[HolDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If
StartDate = StartDate + 1
Loop
WorkingDays2 = intCount
Exit_WorkingDays2:
Exit Function
Err_WorkingDays2:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select
End Function