Module Question

[blondie41]

I added the following module which seemed to work correctly for a few days, but is now causing the query to "lock up" my PC (the query runs forever). Can anyone help me understand why this might be happening? The module is:

Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer

Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer

intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0

For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbMonday) < 6 Then
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
NetWorkdays = NetWorkdays + 1
End If
End If
Next i

End Function

I use it with a query and the following is in one of the columns:

Fax Date Difference: NetWorkDays([StartDate],[EndDate])

I have a table in the query "Holidays" with the fields Holidays and HolDate.

Any help would be appreciated.

 

[dhookom]

I have a table in the query "Holidays" with the fields Holidays and HolDate.

I don't think the Holidays table should be in your query. Try remove it.

Duane
Hook'D on Access
MS Access MVP

 

[blondie41]

I removed the table 'Holidays', but it still locked up.

Any other ideas?

 

[dhookom]

Any chance you could provide the SQL view of the query and the number of rows it contains?

Duane
Hook'D on Access
MS Access MVP

 

[blondie41]

Yes - and thank you so much for your help!

SELECT DISTINCT Fax1.CareAdvocate, Fax1.EndDate, Fax1.StartDate, Fax1.ApptDate, Fax1.FinalDisposition AS Expr1, Fax1.[Last Certed Date] AS Expr2, Fax1.DischargeLOC, Fax1.Boards AS Expr3, Fax1.Count, ([ApptDate]-[StartDate]) AS [Appointment Date Difference], ([EndDate]-[StartDate]) AS FaxDateDiff
FROM Fax1;


Number of Rows = 80

 

[dhookom]

Your SQL doesn't contain the function NetWorkDays(). Why would your SQL contain "Expr1" etc?

Duane
Hook'D on Access
MS Access MVP

 

[blondie41]

So sorry - I have been banging my head against my monitor all morning - below is the correct SQL:

SELECT DISTINCT Fax1.CareAdvocate, Fax1.EndDate, Fax1.StartDate, Fax1.ApptDate, Fax1.DischargeLOC, Fax1.Count, ([ApptDate]-[StartDate]) AS [Appointment Date Difference], NetWorkdays([StartDate],[EndDate]) AS [Fax Date Difference]
FROM Fax1;

 

[dhookom]

Are all of the StartDate and EndDate fields populated with real dates or are some possibly null/empty?

Duane
Hook'D on Access
MS Access MVP

 

[blondie41]

All are populated with real dates

 

[PHV]

Hopefully HolDate is indexed in Holidays.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

In addition to the making sure of the index, this might be quicker. It assumes you don't have any HolDates that are also weekend dates.

Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer

Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer

intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0

For i = 0 To intGrossDays
   dteCurrDate = dteStart + i
   If Weekday(dteCurrDate, vbMonday) < 6 Then
     NetWorkdays = NetWorkdays + 1
   End If
Next i
NetWorkdays= netWorkDays - _
    DCount("[HolDate]", "Holidays", "[HolDate] Between #" & dteStart & "# AND #" & dteEnd & "#")

End Function

Duane
Hook'D on Access
MS Access MVP

 

[blondie41]

Thanks to everyone for your help. Between the new code and the index (reminder), things seem to be working. It is still not as quick as other queries, but I am assuming that is the nature of using a module? If anyone has any other tips about query speed while using a module I would really appreciate it.

Thanks again to everyone - I appreciate the help more than you will ever know!

 

[dhookom]

There is probably a nearly pure SQL method with a table containing all numbers or dates in a large range that could be left joined to a table of holidays. You could query this result for dates between your start and end dates that aren't a holiday and aren't a weekend date. I expect this would be much quicker for large numbers of records.

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

there is a faq on the subject. your code appears to be an attempt to simplify it. the line

the code posted by

dhookom

works. issues regarding the speed of a process are not (usually) easy to identify. the process is generally refered to as "profiling" and / or "instrumenting". these are often accomplished by including provisions for various procedures reporting their execution times in some manner (often a temp table or a simple text file (I like to use a table, as I can then do some analysis of the postings).

Modules do ALWAYS slow down the execution of MS Access processes.

DLookup ALWAYS, ALWAYS, ALWAYS, ALWAYS slows everything. all of the domain functions ALWAYS, ALWAYS, ALWAYS, ALWAYS, ALWAYS, ALWAYS, ALWAYS, ALWAYS slow down everthing. Conceptually the domain function act as a wrapper for a dynamically generated query. Loosly translated, this says that you have requested MS Access to interpert (parse) the arguments supplied and generate an ad-hoc query suitable (but VERY conservative) to the arguments, execute the query and reyurn the results.

so, one brief suggestion is to avoid the domain aggregates in favor of at least an embedded query.

for your 'exercise', replace the table & field names in the query below with your names and see if it makes any difference.

SELECT Count(tblHolidays.HolidayName) AS NumHolidays
FROM tblHolidays
WHERE (((tblHolidays.HolidayDate) Between [dtStart] And [dtEnd]));
HAVING (((Count(tblHolidays.HolidayDate)) Between [dtStart] And [dtEnd]));

MichaelRed

 

[MichaelRed]

obsesive / compulsive complex today:

Public Function basNetWorkDays(dtStart As Date, dtEnd As Date) As Long

    Dim intGrosDays As Integer
    Dim dtCurrDate As Date
    Dim Idx As Integer
    Dim rst As DAO.Recordset

    Dim strSQL As String

    intGrosDays = DateDiff("d", dtStart, dtEnd)
    strSQL = "SELECT Count(HolidayName) AS NumHolidays " _
           & "FROM tblHolidays " _
           & "WHERE (HolidayDate Between #" & dtStart & "# And #" & dtEnd & "# );"
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    Idx = rst!NumHolidays
    basNetWorkDays = intGrosDays - Idx

End Function

Ideally, the embedded query would be set up as a parameter query, so it xcoould be used throughout the app. Today it is just for illustrative purposes.




MichaelRed

 

[dhookom]

Here is the no-code method...
Setup:
Table of all holidays
[tt][blue]
tblHolidays
================
Holidate (date/time field PK)
HoliTitle (text)
[/blue][/tt]

Table of all numbers (at least 1 - max of date range)
[tt][blue]
tblNums
================
Num (integer field PK)
[/blue][/tt]

Table with two date fields to find work days between
[tt][blue]
tblStartEndDates
================
DateID (Autonumber field PK)
StartDate (Date)
EndDate (Date)
[/blue][/tt]

Query to list all dates with the between dates
[tt][blue]
qselAllDatesBetween
===================
SELECT tblStartEndDates.DateID, tblStartEndDates.StartDate, tblStartEndDates.EndDate, [Startdate]+([Num]-1) AS AllDates, Weekday([Startdate]+([Num]-1)) AS DOW
FROM tblStartEndDates, tblNums
WHERE ((([Startdate]+([Num]-1))<=[EndDate]))
ORDER BY tblStartEndDates.DateID, [Startdate]+([Num]-1);
[/blue][/tt]

Query to count all work dates between dates
[tt][blue]
qgrpWorkDaysCalc
===================
SELECT qselAllDatesBetween.DateID, qselAllDatesBetween.StartDate, qselAllDatesBetween.EndDate, Count(qselAllDatesBetween.DateID) AS CountOfDateID, DateDiff("d",[StartDate],[EndDate]) AS TotalDays
FROM qselAllDatesBetween LEFT JOIN tblHolidays ON qselAllDatesBetween.AllDates = tblHolidays.HolidayDate
WHERE (((tblHolidays.HolidayDate) Is Null) AND ((qselAllDatesBetween.DOW) Between 2 And 6))
GROUP BY qselAllDatesBetween.DateID, qselAllDatesBetween.StartDate, qselAllDatesBetween.EndDate, DateDiff("d",[StartDate],[EndDate])
ORDER BY qselAllDatesBetween.DateID;
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmm ... mmm

While I don't see any code, I also fail to 'see' how the various table type recordsets are populated ... Then, The original issue generated a NUMBER which the user applied somehow/somewehere/sometime/someplace and I am still looking for it ...

Obviously, the holidays thing is updated seperatly and apart from the overall process, but tblNums and tblStartEndDates aooear to need to populated by some mechanisim? startdate and enddate could (reasnably?) be entered from a form or just used as 'pop-up' parameters for the query? What about table nums? Is this expected to be (statically) populated with sufficuent 'records' for all possible data ranges?

intGrossDays NumHolidays Rtn
365 11 354

from

SELECT First(DateDiff("d",[dtStart],[dtEnd])) AS intGrossDays, Count(tblHolidays.HolidayName) AS NumHolidays, [intGrossDays]-[NumHolidays] AS Rtn
FROM tblHolidays
WHERE (((tblHolidays.HolidayDate) Between [dtStart] And [dtEnd]));

Of course, the fields [intGrossDays] and [NumHoildays] are included just to (sort of) to illustrate the calculations internally. The input was via the parameters dtStart and dtEnd as simple pop-ups, with #1/1/2008# and #12/31/2008# with the 11 holidays for the year.

As mentioned previously, it is just one of those obsesive compulsive days. Shouldn't have even gotten started on this track, duane is a lot better at this.





MichaelRed

 

[dhookom]

tblNums is a table I include in many of my apps. It just has numbers 1, 2, 3, 4,... 1000. In this case, it must include all numbers from 1 to the max raw days between the start and end dates.

tblHolidays is the table of all entered holidays similar to the OP's Holidays table.

My final query was similar to the OP's

SELECT DISTINCT Fax1.CareAdvocate, Fax1.EndDate, Fax1.StartDate, Fax1.ApptDate, Fax1.DischargeLOC, Fax1.Count, ([ApptDate]-[StartDate]) AS [Appointment Date Difference], NetWorkdays([StartDate],[EndDate]) AS [Fax Date Difference]
FROM Fax1;

Your code (basNetWorkDays) while removing the DLookup() and DCount() doesn't account for weekends.

Let OCD rule Fridays....


Duane
Hook'D on Access
MS Access MVP