Function Killing Query Speed - PLEASE HELP!

[archie1_za]

Hi

1. I have a date calculation function that calculates dates excluding weekends & holidays & also calculates the difference in two dates excluding weekends & holidays.

2. I am busy testing a query that has 30 date fields. 6 of the dates are from an existing table and the remaining are calculated date fields (which use the function).
3. I also have 4 status (delayed, completed, etc) fields that are created/calculated using the dates.

My problem:

The query runs OK. Its not super fast and I would like to speed it up. The BIG problem though is that after the query runs and I'm in datasheet view, the whole query just completely bogs down. If I try and click in any field or even try and scroll left/right to see the results it just hangs. It takes forever to scroll just one click.
I have tried creating a form from the query and that is even worst. If I click in a field it takes forever to populate the fields and if I decide to click in another field then it just gets worst. It seems that the function is either constantly running or recalculating the entire SQL code everytime I click anywhere.



I have attached the code for the function.

I hope I am making sense - Im a novice user. Please be patient.

Regards
Archie

[ranman256]

your functions have loops. these can take time.

[archie1_za]

Thank You - Can you suggest how I can modify the function to take out these loops?
Will it affect my query/calculations in any way?

Or is there a way to get it to calculate only the row I am working on or a single row that i select - so that it doesnt run through the entire list of records which will be in excess of 5000-7000.

[aytee111]

If you want only one record in the query then set criteria. Another option is to change the function to add records to a temp table and run it first, before the query. Then the query looks at the table to get the days. Another option is to add the days to the original table. It is generally not good practice to store calculated fields on a table, but in this case an exception can be made.

[archie1_za]

Hi There

I have done some reading online and apparently I can do the date calculation within SQL and not have to use a VB function which would theoretically speed things up.

I am not a programmer and dont know either SQL/VB.

Below is an example of what I have found.

How would I include this in my SQL (attached)

I also have a table with public holidays called "Holidays" with a date/time field HolidayDate

I need to exclude these as well. There is also a a function where I calculate the difference between days (excluding w/ends and p/holidays) to give me a status.I humbly request assistance with this as well

I know I am asking alot but I really need the assistance.

Thanking you in advance.



CREATE FUNCTION [dbo].[DAYSADDNOWK](@addDate AS DATETIME, @numDays AS INT)
RETURNS DATETIME
AS
BEGIN
IF @numDays > 0
WHILE @numDays>0
BEGIN
SET @addDate=DATEADD(d,1,@addDate)
IF DATENAME(DW,@addDate)='saturday' SET @addDate=DATEADD(d,1,@addDate)
IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,1,@addDate)
SET @numDays=@numDays-1
END
ELSE IF @numDays < 0
WHILE @numDays < 0
BEGIN
SET @addDate=DATEADD(d,-1,@addDate)
IF DATENAME(DW,@addDate)='saturday' SET @addDate=DATEADD(d,-1,@addDate)
IF DATENAME(DW,@addDate)='sunday' SET @addDate=DATEADD(d,-1,@addDate)
SET @numDays=@numDays+1
END
RETURN CAST(@addDate AS DATETIME)
END
GO

[June7]

Posted code is not valid in Access which is why a custom VBA function is utilized.

A form is probably not the best vehicle for presenting this manipulated data. Probably should use a report.

[archie1_za]

Can the function be modified to execute on only the current record (record on focus)

If so, how (function code is attached to original post)

[June7]

Possible to call a function if only a given condition is met. As example, if there is a Yes/No field named IsActive that indicates record is 'active' or 'inactive', expression could be: IIf([IsActive], MyFunction(), Null). Otherwise, function will run for every record.

[knarfreppep]

Here's an idea.

Create Table1 with two fields dtmDate and bytWorkDay.

Populate Table1 with a year or so of sequential dates with bytWorkDay set to 0 for Sat, Sun, and holiday and 1 for every other day ... 30 minutes work for you to avoid infinite work for your code.

Then getting the number of days is as simple as -
lngDays = DSum("bytWorkDay", "Table1", "dtmDate Between #" & Format(dtmFirstDate, "mm/dd/yy") & "# And #" & Format(dtmLastDate, "mm/dd/yy") & "#") - 1

To speed up the results, append them to a 'results' table.

[knarfreppep]

Here's code to put 5 years of work days into Table1 (in around 30 seconds).

All you'd have to do after that is set bytWorkday to 0 for every holiday date (or, better yet, just delete holiday dates).


Public Sub gsubEnterDates()

Dim dtmDate As Date

dtmDate = Date

DoCmd.SetWarnings False

Do
DoCmd.RunSQL _
"INSERT INTO Table1 ( dtmDate, bytWorkDay )" & _
" SELECT #" & Format(dtmDate, "mm/dd/yy") & "#, " & Weekday(dtmDate)
dtmDate = dtmDate + 1
Loop Until dtmDate = Date + 1827

DoCmd.RunSQL _
"DELETE FROM Table1" & _
" WHERE bytWorkday IN (1,7)"

DoCmd.RunSQL _
"UPDATE Table1" & _
" SET bytWorkday = 1"

DoCmd.SetWarnings True

MsgBox "Done."

End Sub

[archie1_za]

Hi There

Thank you for the post. Sorry, had PC problems yesterday. I had already created the table as mentioned in your previous post with the two fields dtmDate and bytWorkDay for the next 1000 days. I also included the holidays for the same period.

What Im having a problem with is calling the function that you suggested.

In my code (attached in the first post), I use two functions, dateAddWeekday to calculate the date excluding the weekends and holidays and calcDays to calculate the number of days between two dates excluding weekends and holidays.

What code would I put into a module?

Would it simply be this : lngDays = DSum("bytWorkDay", "Table1", "dtmDate Between #" & Format(dtmFirstDate, "mm/dd/yy") & "# And #" & Format(dtmLastDate, "mm/dd/yy") & "#") - 1

This function is called lngDays right?

And then in my query SQL how would I call it and what would the syntax be?

This is how I call them currently:

IIf(PlanFinConfgCrStartDate Is Not Null And FinConfigCrStAD Is Not Null,Calcdays(PlanFinConfgCrStartDate,FinConfigCrStAD), 0) AS FinConfigCrDaysDelay,
IIf(PlanNIpamRqStartDate Is Not Null,dateAddWeekday(PlanNIpamRqStartDate,0)) AS PlanNIpamRxStartDate,

Regards
Archie

[knarfreppep]

Your function will be -

Function lng_gfctCalculateDays(dtmStart As Date, dtmEnd As Date) As Long
lng_gfctCalculateDays = DSum("bytWorkDay", "Table1", "dtmDate Between #" & Format(dtmStart , "mm/dd/yy") & "# And #" & Format(dtmEnd, "mm/dd/yy") & "#") - 1
End Function

You can set bytWorkDay to 0 for Sats, Suns, and hols or just delete records for those days.

Indeed, if you delete records for those days you can also delete the bytWorkDay field and your function would then be -

Function lng_gfctCalculateDays(dtmStart As Date, dtmEnd As Date) As Long
lng_gfctCalculateDays = DCount("dtmDate", "Table1", "dtmDate Between #" & Format(dtmStart , "mm/dd/yy") & "# And #" & Format(dtmEnd, "mm/dd/yy") & "#") - 1
End Function

If you're still worried about speed, when you have a query which renders the matrix you want, use a final query to append its records to a temporary table and show users that temporary table (which will 'fly').

PS You won't need these functions : dateAddWeekday doHolidays, doWeekends.

[archie1_za]

Hi

Tried it but keep getting the following error:

Run Time Error : 2766
The object doesn't contain the Automation Object dtmDate

Any Suggestions?

[knarfreppep]

What are the field names in your Table1?

[archie1_za]

Ok. Sorted that out. My dtmDate field name had a space after it for some reason

Now I get the error Run Time Error 94 Invalid use Of Null