473,323 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

create wk no, date, day

hi,
I had code written for me to create week no and start date increament every 7 days for each week but i would now like to create daily dates with week numbers and the day.
I have managed to create the daily dates but I cant seem to create the week no in intervals of 7 days of the week and the Day.
ie the results I need are
Wk No Date Day Year
1 01/01/10 fri 2010
1 02/01/10 sat 2010
1 03/01/10 sun 2010 ...etc
then for wk no 2
2 08/01/10 fri 2010
2 09/01/10 sat 2010
2 010/01/10 sun 2010 ...etc

my Current Result
WeekNo Date Day YearDate
1 01/01/2009 0 2010
2 02/01/2009 0 2010
3 03/01/2009 0 2010
4 04/01/2009 0 2010
5 05/01/2009 0 2010
6 06/01/2009 0 2010
7 07/01/2009 0 2010
8 08/01/2009 0 2010
9 09/01/2009 0 2010
10 10/01/2009 0 2010
11 11/01/2009 0 2010

My Code

Sub DailyDate()

'This section sets up all the variables used in the procedure
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim frm As Form
Dim intCount As Integer
Dim dteStart As Date
Dim dteEnd As Date
Dim dteStartDate As Date
Dim dteEndDate As Date
Dim dteYearDate
Dim txtBranchID As Recordset
Dim liRecordIndex As Integer

On Error GoTo Error_Handler

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("DailyDate")
Set frm = Forms("frmCreateDay")

'This section gets the values from the textboxes on the form
'and sets intCount to 1 (intCount tracks the week number)
dteStartDate = frm.Controls("txtStart")
dteEndDate = frm.Controls("txtEnd")
dteYearDate = frm.Controls("txtYear")

intCount = 1

'This section sets the start and end dates for the first week
dteStart = dteStartDate
dteEnd = dteStart + 1

'This loops through until the end date is reached
'If your end date doesn't fall properly at the end of a week it will
'cut off at the last week end date BEFORE the total date is reached
'Each time it loops through it adds one record to the table


Do Until dteEnd > dteEndDate
With rst
.AddNew
!WeekNo = intCount
!Date = dteStart
!YearDate = (dteYearDate)
.Update
End With
'Increments the start and end dates and the week number

intCount = intCount + 1
dteStart = dteStart + 1
dteEnd = dteStart
Loop

Set frm = Nothing
Set rst = Nothing
Set dbs = Nothing

intCount = MsgBox("Weeks created.", vbOKOnly)

Exit_Sub:
Exit Sub

Error_Handler:
If Err.Number = 3022 Then
MsgBox "Weeks already exist!", vbOKOnly + vbCritical, "Error!"
Else
MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly + vbCritical, "Error!"
End If
Exit Sub


End Sub

Can you please help as I am not familiar with vb to much


Thanks in advance

Farid
Attached Files
File Type: txt Result.txt (719 Bytes, 303 views)
File Type: txt Code.txt (2.0 KB, 299 views)
Mar 29 '10 #1
0 852

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Ken Fine | last post by:
I want my application to maintain a directory tree based on months and years, e.g.: 2004 January file file file February file
2
by: Nick | last post by:
Loop to create an array from a dynamic form. I'm having trouble with an application, and I'll try to explain it as clearly as possible: 1. I have a form with two fields, say Apples and...
1
by: YONETANI Tomokazu | last post by:
Hello. After upgrading a test database to V8.2 with FixPak7a, too see if the production servers can be safely upgraded, I noticed that inserts on an insertable view which consists of multiple...
1
by: poohnie08 | last post by:
i have a excel spreadsheet showing staff name, date,work hour, ot hour, slot1, slot2, slot3, slot4 and others). The "()" will keep repeating from day 1 until end of month. eg in excel spreadsheet,...
2
by: Alicia | last post by:
Does anyone know why I am getting a "Syntax error in Create Table statement". I am using Microsoft Access SQL View to enter it. Any other problems I may run into? CREATE TABLE weeks (...
9
by: Marc Miller | last post by:
Hi all, I have 2 dev. machines, the 1st is Win 2000 with .NET 7.0 and the 2nd is XP Pro with .NET 2003. My Web Server is Win 2000 Server with IIS 5.0. I can create a new project on my test...
9
by: Bob Achgill | last post by:
I would like to use the timestamp on files to manage the currency of support files for my VB windows application. In this case I would only put the timestamp of the file in the management database...
10
by: Henrik Dahl | last post by:
Hello! I have an xml schema which has a date typed attribute. I have used xsd.exe to create a class library for XmlSerializer. The result of XmlSerializer.Serialize(...) should be passed as the...
4
by: ringer | last post by:
I have a db where each record is a certain task to be done. Each record includes the date the task should be completed. Some of the tasks are one time only but some are recurring. I have been having...
3
by: creative1 | last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.