Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 10th, 2007, 12:12 PM
Registered User
Join Date: Nov 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default ACCESS : Auto incrementing date

How can a table may be filled running append query and providng a starting date and a range.
I mean If I provide a range = 7 on a form and starting date = 21/12/2007 then I want to run an append table query by clicking a button on the form and hope to get following table

1 21/12/2007
2 22/12/2007
3 23/12/2007
7 27/12/2007

How to accomplish this


Old November 14th, 2007, 04:02 PM
Authorized User
Join Date: Oct 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

Here's a working (yes, I DID test it this time!) example.

I created 2 textboxes (txtStartDate and txtDateRowCount) and
1 button (cmdGenerateRows). I also created a sample table named
DATE_TABLE with a Date/Time column named DATE_COL.

Here is the OnClick() code for the cmdGenerateRows button:

Private Sub cmdGenerateRows_Click()

    Dim cnn As New ADODB.Connection

    Dim CalcDate As Date
    Dim Index As Integer
    Dim SQL As String

    ' Verify the start date format
    If Not IsDate(Me.txtStartDate) Then
        MsgBox "Start date must be a date value!", vbExclamation, "ERROR"
        Exit Sub
    End If

    ' Verify the row count format
    If IsNumeric(Me.txtDateRowCount) And Me.txtDateRowCount > 0 Then
        MsgBox "Row count must be a positive numeric value!", vbExclamation, "ERROR"
        Exit Sub
    End If

    ' Open the connection to the current database
    Set cnn = CurrentProject.Connection

    ' Initialize the first calc date value
    CalcDate = Me.txtStartDate

    ' Generate a row for each date in the table
    For Index = 1 To Me.txtDateRowCount Step 1

        ' Build the INSERT SQL statement
        SQL = ""
        SQL = SQL & "(DATE_COL) "
        SQL = SQL & " VALUES "
        SQL = SQL & "(#" & CalcDate & "#)"

        ' Execute the INSERT statement
        cnn.Execute SQL

        ' Increment the calc date by 1
        CalcDate = DateAdd("d", 1, CalcDate)

    Next Index

    ' Close the connection

End Sub

Once both the starting date and number of rows fields are verified
and retrieved, you can loop using the For/Next I used in the example,
running a cnn.Execute with an INSERT query.

The trick to increment the date is to define a variable (I called
mine CalcDate). I first set CalcDate to the starting date specified
in the txtStartDate field) and then run the loop.

Once the SQL has been built using the CalcDate value and executed,
I then increment CalcDate using a DateAdd() function. In the example,
the "d" in the DateAdd function indicates to add the value in Days
(you can also specify minutes, years, weeks, etc). The second value
in the DateAdd function indicates the number of days in this case,
and the third value is the date variable that I am adding to.

If your table requires more columns than I specified using my
example, simply add the columns as needed. You can get more info
by looking up INSERT in any SQL book or on the web.

If you do need to store dates in DD/MM/YYYY format within the
table as you had indicated in your original question, you will
need to change the data type of DATE_COL in the DATE_TABLE from
Date/Time to Text; otherwise, Access automatically switches it
from DD/MM/YYYY format to MM/DD/YYYY. Also, you will need to
modify the portion of the INSERT query from:

        SQL = SQL & "(#" & CalcDate & "#)"


        SQL = SQL & "('" & Format(CalcDate, "dd/mm/yyyy") & "')"

Hope that helps!


Old November 21st, 2007, 08:36 AM
Registered User
Join Date: Jul 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

I have a timekeeping piece that "Automatically" fills rows on a form with for days not entered while also adding records to the table. (add new records for days not entered yet). The only thing I HATE about it is having records I don't really need for employee days off. The event driving this is changing payroll periods from a dropdown box.
I will paste the code here in case this is something you were looking for.

Private Sub CompleteRecords(dteStart As Date, dteEnd As Date)
'If (within sfrmTimeKeepWeeks) there aren't 7 displayed records for each week
'we need to insert a child record based on the missing relevant date(s)
'as the form needs to match an existing Time entry form showing a record for each
'day in chosen payperiod. We'll just insert the required fields: PayPeriod, SSnbr, and UsageDate

Dim db As Database, strSQl As String
Dim rstWeekDates As Recordset

Dim intRecCount As Integer
Dim dtePayDate As Date
Dim sfrmWeeks As Form

Set db = CurrentDb()
Set sfrmWeeks = Me.Form![sfrmTimeKeepWeeks].Form

    intRecCount = sfrmWeeks.RecordsetClone.RecordCount
    If intRecCount = 7 Then GoTo Exit_CompleteRecords

    'Create a recordset based on subform's Clone to search for missing date
    Set rstWeekDates = sfrmWeeks.RecordsetClone
      With rstWeekDates
        For dtePayDate = dteStart To dteEnd
          .FindFirst ("[UsageDate] = #" & dtePayDate & "#")
            If .NoMatch Then
              'Insert a new record for chosen employee into tblTimeUsage
              strSQl = "INSERT INTO tblTimeUsage ( PayPeriod, SSnbr, UsageDate ) " _
                     & " SELECT """ & Me!cboPayPeriod & """ " _
                     & " , """ & Me!cboSSNbr & """ " _
                     & " , """ & dtePayDate & """ "

                db.Execute strSQl
            End If
        Next dtePayDate
      End With


    Set rstWeekDates = Nothing

    sfrmWeeks.Visible = True
    Set sfrmWeeks = Nothing

End Sub

Hope this helps!

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to auto generate date? okboy SQL Server 2000 3 December 12th, 2006 08:17 PM
Auto delete by date XXL PHP How-To 1 March 29th, 2006 01:32 AM
asp/sql auto email on specified date keyvanjan Classic ASP Professional 2 March 5th, 2006 09:14 PM
asp/sql auto email on specified date keyvanjan Classic ASP Databases 0 February 26th, 2006 03:34 AM
Using Date and Time to auto post recordset pablohoney Classic ASP Basics 7 March 7th, 2005 09:56 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.