Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA 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
  #1 (permalink)  
Old November 16th, 2007, 07:15 AM
Friend of Wrox
Points: 718, Level: 10
Points: 718, Level: 10 Points: 718, Level: 10 Points: 718, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: Ennis, Clare, Ireland.
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default Append Query (Dates)

Hi,

I have a 'dbo_Week_Commencing' table which contains 3 columns:

ID (Long) Primary Key
Date (Short Date)
Week Commencing (Short Date)

The 'Date' field contains sequential records by date.
The 'Week Commencing' field contains records where the 'Date' column FirstDayOfTheWeek is Sunday.

For Example:
Code:
ID     Date         Week Commencing
6631    20/12/2009    20/12/2009
6632    21/12/2009    20/12/2009
6633    22/12/2009    20/12/2009
6634    23/12/2009    20/12/2009
6635    24/12/2009    20/12/2009
6636    25/12/2009    20/12/2009
6637    26/12/2009    20/12/2009
6638    27/12/2009    27/12/2009
6639    28/12/2009    27/12/2009
6640    29/12/2009    27/12/2009
6641    30/12/2009    27/12/2009
6642    31/12/2009    27/12/2009
6643    01/01/2010    27/12/2009
6644    02/01/2010    27/12/2009
The above extraction shown the last 14 (2 week) records in the table.
Note the 1st and 8th rows contain the Sunday (FirstDayOfTheWeek) for both the 'Date' and 'Week Commencing' Columns.

Question
How do I write an append query to add another consecutive 365 (1 year) records for both columns?

Thanks in advance,



Neal

A Northern Soul
__________________
Neal

A Northern Soul
  #2 (permalink)  
Old November 16th, 2007, 09:10 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I don't understand what you are asking here? Do you have 365 records for 2009 and you want 365 records for 2010? If this is what you want, I can code it for you, but I am not sure how to do an append query with this.

mmcdonal

Look it up at: http://wrox.books24x7.com
  #3 (permalink)  
Old November 18th, 2007, 12:44 PM
Friend of Wrox
Points: 718, Level: 10
Points: 718, Level: 10 Points: 718, Level: 10 Points: 718, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: Ennis, Clare, Ireland.
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default

Yes, I do want to add another 365 records (to both columns). I thought that there might be a way of appending by a query.

But if not then any help would be appreciated.

Many Thanks,



Neal

A Northern Soul
  #4 (permalink)  
Old November 19th, 2007, 10:19 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is how I would do this (assuming your ID is autonumber):

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim dtStart As Date
Dim dtEnd As Date
Dim dtWeek As Date
Dim i As Integer 'check for runaway loop

i = 0
dtStart = (input start date here like "01/01/2009")
dtEnd = (input end date here, plus one day, like "01/01/2010")

sSQL = "SELECT * FROM tblYourTableName"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until dtStart = dtEnd Or i = 400
    If DatePart("w", dtStart) = 1 Then
        dtWeek = dtStart
    End If

    rs.AddNew
        rs("Date") = dtStart
        rs("Week Commencing") = dtWeek
        rs.Update

    dtStart = DateAdd("d", 1, dtStart)
    i = i + 1
Loop

rs.Close

This will add all but the first 6 or less Week Commencing dates unless 2009 starts on a Sunday. You will only have to fill those in by hand.

Also, "Date" is a reserved name, and should not be used for a field name.

Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com
  #5 (permalink)  
Old November 19th, 2007, 01:17 PM
Friend of Wrox
Points: 718, Level: 10
Points: 718, Level: 10 Points: 718, Level: 10 Points: 718, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: Ennis, Clare, Ireland.
Posts: 131
Thanks: 10
Thanked 0 Times in 0 Posts
Default

Many thanks, that is basically it.

I put it into a sub-routine and gave it a spin.



Neal

A Northern Soul
  #6 (permalink)  
Old November 20th, 2007, 09:08 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Just some observations.

If your field is called DATE, change the name. It will intefere with the Access function Date() in calculations and statements.

Even if there is more data besides date and date commencing in your table. You should not actually storing date commencing. You should be just recalculating it on the fly when you need it based on the date. You're storing extra data that's bloating your dB size.

The Sunday prior to any date is simply

Date Commencing = DateAdd("d", -1 * Weekday([YourDateField]) + 1, [YourDateField])


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division


Similar Threads
Thread Thread Starter Forum Replies Last Post
Append Query bright_mulenga Access 1 January 3rd, 2007 01:33 PM
Append Query issue gherkin Access 3 August 7th, 2006 10:18 AM
Append Query Cybersurfer Access 1 February 13th, 2006 01:02 PM
append query? bph Access 2 November 23rd, 2004 12:44 PM
append query stoneman Access 2 November 12th, 2003 09:17 PM





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