Functiosn run too Slow

[Guest]

I work at company where we have to run queries every month where the dates
have to be changed manually. For most of these reports the date is always
the previous months fist day, last day or some such relationship. I came up
with these functions to avoid have to manually change the dates in 50
different places each month. The only problem is that for some reason the
queries run MUCH slower using these functions instead of typing in the
dates.(It's a very large database) Is there anything I can do? I seems like
a huge wast of time to type these dates into the queries each month and also
subject to human error. Any help greatly appreciated.

Thanks,
Billy

Dallas,TX


Option Compare Database

Public Function EOPrevMonth()

'''''''''''''''''''''''''''''''''''''''''''''
'' Returns Last date of the previous month ''
'''''''''''''''''''''''''''''''''''''''''''''

Dim PriorMonth As Integer
Dim NewYear As Integer

PriorMonth = Month(DateAdd("m", -1, Date))
PMYear = Year(DateAdd("m", -1, Date))


Select Case PriorMonth
Case 1, 3, 5, 7, 8, 10, 12
EOPrevMonth = DateSerial(PMYear, PriorMonth, 31)
Case 4, 6, 9, 11
EOPrevMonth = DateSerial(PMYear, PriorMonth, 30)
Case 2
If Int(NewYear / 4) = NewYear / 4 Then
EOPrevMonth = DateSerial(PMYear, PriorMonth, 29)
Else
EOPrevMonth = DateSerial(PMYear, PriorMonth, 28)
End If
End Select

End Function


Public Function BOPrevMonth()

''''''''''''''''''''''''''''''''''''''''''
'' Returns First Date of Previous Month ''
''''''''''''''''''''''''''''''''''''''''''

BOPrevMonth = Format(DateAdd("m", -1, Date), "mm" & "/1/" & "YYYY")



End Function

Public Function BOPrevQtr()

''''''''''''''''''''''''''''''''''''''''''''
'' Returns First Date of Previous Quarter ''
''''''''''''''''''''''''''''''''''''''''''''

Dim Month As Integer
Dim CurYear As Integer


Month = CInt(Format(Date, "mm"))
CurYear = CInt(Format(Date, "yyyy"))

Select Case Month

Case 1 To 3
BOPrevQtr = "10/01/" & (CStr(CurYear - 1))

Case 4 To 6

BOPrevQtr = "01/01/" & (CStr(CurYear))

Case 7 To 9

BOPrevQtr = "04/01/" & (CStr(CurYear))

Case 10 To 12

BOPrevQtr = "07/01/" & (CStr(CurYear))
End Select
End Function

Public Function EOPrevQtr()


'''''''''''''''''''''''''''''''''''''''''''
'' Returns Last Date of Previous Quarter ''
'''''''''''''''''''''''''''''''''''''''''''

Dim Month As Integer
Dim CurYear As Integer


Month = CInt(Format(Date, "mm"))
CurYear = CInt(Format(Date, "yyyy"))

Select Case Month

Case 1 To 3
EOPrevQtr = "12/31/" & (CStr(CurYear - 1))

Case 4 To 6

EOPrevQtr = "03/31/" & (CStr(CurYear))

Case 7 To 9

EOPrevQtr = "06/30/" & (CStr(CurYear))

Case 10 To 12

EOPrevQtr = "09/30/" & (CStr(CurYear))
End Select
End Function


Public Function BOCurMonth()
BOCurMonth = Format(Date, "mm" & "/01/" & "yyyy")

End Function
Public Function BOTwoMonthsAgo()



''''''''''''''''''''''''''''''''''''''''''
'' Returns First Date of Two Months Ago ''
''''''''''''''''''''''''''''''''''''''''''

BOTwoMonthsAgo = Format(DateAdd("m", -2, Date), "mm" & "/1/" & "YYYY")

End Function

 

[Guest]

I rewrote some of these as formulas instead of functions. Maybe this will be
faster?





Beginning of Prior Month

Format(DateAdd("m",-1,Date()),"mm/""01""/yyyy")


Begininng of Two Months Ago

Format(DateAdd("m",-2,Date()),"mm/""01""/yyyy")

Begininng of Current Month

Format(Date(),"mm/""01""/yyyy")

End of Prior Month

DateAdd("d",-1,Format(Date(),"mm/""01""/yyyy"))

Beginning of Two Months Ago

DateAdd("d",-1,Format(DateAdd("m",-1,Date()),"mm/""01""/yyyy"))

 

[Marshall Barton]

I rewrote some of these as formulas instead of functions. Maybe this will be
faster?

Beginning of Prior Month

Format(DateAdd("m",-1,Date()),"mm/""01""/yyyy")


Begininng of Two Months Ago

Format(DateAdd("m",-2,Date()),"mm/""01""/yyyy")

Begininng of Current Month

Format(Date(),"mm/""01""/yyyy")

End of Prior Month

DateAdd("d",-1,Format(Date(),"mm/""01""/yyyy"))

Beginning of Two Months Ago

DateAdd("d",-1,Format(DateAdd("m",-1,Date()),"mm/""01""/yyyy"))

I doubt it, but be careful of using the Format function in
this kind of situation, Format returns a string, which may
not be auto converted to the date you intended. I think
these are the common expressions for what you are trying to
do.

Beginning of Prior Month
DateSerial(Year(Date()), Month(Date()) - 1, 1)
Beginning of Two Months Ago
DateSerial(Year(Date()), Month(Date()) - 2, 1)
Beginning of Current Month
DateSerial(Year(Date()), Month(Date()), 1)
End of Prior Month
DateSerial(Year(Date()), Month(Date()), 0)

BUT, regardless of how these are coded, I doubt that they
are the cause of the query running slowly. Slow queries are
usually caused by inadequate indexes for fields in the WHERE
or ON clauses. The use of Domain Aggregate functions can
also cause poor performance.

Regardless of your or my guesses at the source of the
problem, you will have to post a Copy/Paste of the query's
SQL view before anyone can begin to analyze it.

 

[Guest]

Thanks Marshall,

I appreciate the help. I'll try your suggestions.

 

[Guest]

I changed my functions to make sure they all returned a date instead of a
string and they still run very slow. When I say they run slow it has nothing
to do with indexes. I'm talking about comparing them to the speed of the
query when I simply type in the date. I have found the forumulas to work
just as fast as the actual dates, but the functions I built are terribly
slow.

I don't know how to write a formula for the last day of the previous month.
If anyone has that I would appreciate it.
--
Billy Rogers

Dallas,TX

Currently Using Office 2000

 

[John Spencer]

DateSerial(Year(Date()),Month(Date()),0)

Returns the last day of the prior month.

 

[strive4peace]

whewnever possible, put calculations in the query instead of
calling a function, it is faster

last day of the previous month:

DateSerial(year([datefield]), month([datefield]),0)

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com