Optimize code 1

[dkwong]

I've created some code to insert records into a temp table to be referenced in a pick list. The output would be as follows:

MonthYear	StoredDate
July 2002	2002/07/31
August 2002	2002/08/31
September 2002	2002/09/30
October 2002	2002/10/31
November 2002	2002/11/30
December 2002	2002/12/31
January 2003	2003/01/31
February 2003	2003/02/28
March 2003	2003/03/31
April 2003	2003/04/30
May 2003	2003/05/31
June 2003	2003/06/30

I have 2 questions. 1. How can I optimize the code below as it seems to run slowly and 2. Where should I put the code? (It would need to be run at the beginning of each month.)
Much appreciated!

-------- begin ----------

    'Create table containing date values to be used for "Date Planned" of a course
    
    DoCmd.RunSQL ("DELETE FROM DateTemp;") 'clear the DateTemp table
    Dim sMonthNumber As String, sYear As String, sCurrentYear As String, iMonthCount As Integer, sSQL As String  'declarations
    Dim sMonth As String, bWillIncrement As Boolean, bHasIncremented As Boolean, sMonthYear As String, StoredDate As String 'declarations
    
    sMonthNumber = Month(Date) 'retrieve the month number of the current number
    sYear = Year(Date) 'retrieve the current year - to be incremented
    sCurrentYear = Year(Date) 'retrieve the current year to be used as a reference
    bWillIncrement = False 'initialize
    bHasIncremented = False 'initialize
    
    For iMonthCount = 1 To 12 'insert a record for the next 12 months
        sSQL = "INSERT INTO DateTemp (MonthYear, StoredDate) VALUES ("
        sMonth = DLookup("[MonthName]", "MonthLookup", "[MonthNo] = " & sMonthNumber) 'retrieve the name of the current month by referencing the MonthLookup table
        If sMonthNumber = 1 And iMonthCount <> 1 Then 'will not increment the year if the current month is January
            bWillIncrement = True 'the year will increment in the next iteration of the loop
        End If
        If bWillIncrement Then
            sYear = sYear + 1 'increment the year if the month has changed over to January
            bWillIncrement = False 're-initialize
            bHasIncremented = True 'to be used when inserting the value into the StoredDate field
        End If
        sMonthYear = &quot;'&quot; & sMonth & &quot; &quot; & sYear & &quot;'&quot; 'prepare value to inserted into the MonthYear field
        sSQL = sSQL & sMonthYear & &quot;, &quot; 'insert value into MonthYear field
        
        'insert the end of month date into StoredDate field for a given month year
        Select Case sMonthNumber
            Case &quot;1&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-01-31#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-01-31#&quot;
                End If
            Case &quot;2&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-02-28#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-02-28#&quot;
                End If
            Case &quot;3&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-03-31#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-03-31#&quot;
                End If
            Case &quot;4&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-04-30#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-04-30#&quot;
                End If
            Case &quot;5&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-05-31#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-05-31#&quot;
                End If
            Case &quot;6&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-06-30#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-06-30#&quot;
                End If
            Case &quot;7&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-07-31#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-07-31#&quot;
                End If
            Case &quot;8&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-08-31#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-08-31#&quot;
                End If
            Case &quot;9&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-09-30#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-09-30#&quot;
                End If
            Case &quot;10&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-10-31#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-10-31#&quot;
                End If
            Case &quot;11&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-11-30#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-11-30#&quot;
                End If
            Case &quot;12&quot;
                If bHasIncremented Then
                    StoredDate = &quot;#&quot; & sYear & &quot;-12-31#&quot;
                Else
                    StoredDate = &quot;#&quot; & sCurrentYear & &quot;-12-31#&quot;
                End If
        End Select
             
                
        sSQL = sSQL & StoredDate & &quot;);&quot; 'insert value into StoredDate field
                
        DoCmd.RunSQL (sSQL) 'insert the record into the DateTemp table
            
        If sMonthNumber = &quot;12&quot; Then
            sMonthNumber = &quot;1&quot; 'go to January
        Else
            sMonthNumber = sMonthNumber + 1 'increment the month number
        End If
    Next

-------- end ----------

 

[MichaelRed]

Hmmmmmmmmmmmmmmmmmmmm!

That is a LOT of code to generate a dozen date!!!!!!!

Of course the following is not the most efficient either, and it does use an older (sturdier and FASTER) dataaccess mechanisim. Further, it DOES NOT include the standard error checking, salidation dtuffffffffff (like whata ya gonna do when the table doesnt exist????? ...) but within reason, you won't be able to know that it is executing.

Public Function basMakeMnthEnd()

    'Michael Red    7/9/02
    'Create a table of the month ending dates for
    'the current month and the following 11 (One Years worth)

    Dim Idx As Integer              'Counter
    Dim ThisDate As Date
    Dim MySql As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("DateTemp", dbOpenDynaset)

    MySql = "DELETE FROM DateTemp;"

    DoCmd.SetWarnings False
    DoCmd.RunSQL (MySql)
    DoCmd.SetWarnings True
    ThisDate = Date
    Idx = 0                         'Init Counter
    While Idx <= 11                 'Loop Control

        With rst
            .AddNew
                !MonthYear = Format(ThisDate, &quot;mmmm yyyy&quot;)
                !StoredDate = DateSerial(Year(ThisDate), Month(ThisDate) + 1, 0)
            .Update
        End With

        ThisDate = DateSerial(Year(ThisDate), Month(ThisDate) + 1, 1)
        Idx = Idx + 1

    Wend

    Set rst = Nothing
    Set dbs = Nothing

End Function

StoredDate MonthYear
7/31/02 July 2002
8/31/02 August 2002
9/30/02 September 2002
10/31/02 October 2002
11/30/02 November 2002
12/31/02 December 2002
1/31/03 January 2003
2/28/03 February 2003
3/31/03 March 2003
4/30/03 April 2003
5/31/03 May 2003
6/30/03 June 2003 MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[TrojanRabbit]

You would want it in Autoexec or a startup form's Open event.

You're using some variables that you don't need. Your 2 booleans can be done away with. You also don't need sCurrentYear since you'll be using sYear after the increment anyway. Where you have your 2 if statements involving bWillIncrement, all you need is
If sMonthNumber = 1 And iMonthCount <> 1 Then
sYear = sYear + 1
End If

You don't need the if statements in your case statements. All you need is
StoredDate = &quot;#&quot; & sYear & &quot;-01-31#&quot;
Again, after incrementing sYear, that's what you'll be using. Before, sYear will be the same as sCurrentYear.

There's a function in Access referring to the first day of the month. I'm sure there's one for the last day of the month as well. That would do away with the need for case statements.