Results 1 to 3 of 3
  1. #1
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42

    Need DB to generate weekly dates from a start and end date

    Hi.



    I am trying to create a simple database for my partners Dance school.

    I have tables for my classes and term dates.

    I want to be able to create registers that will give me a box for each class.

    ie; If the class is on say a Wednesday, the term starts on 02/09/2015 and ends on 1/1/2016 I want to work out the date of every Wednesday between.

    Cheers

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    these tables:

    tClasses
    ----------
    ClassID (autonum)
    ClassName
    DateStart
    DateEnd
    Instructor

    tWorkouts
    -------------------
    WorkID (auto)
    ClassID (long)
    ClassDate


    tClassRoster
    ---------------
    RosterID (autonum)
    ClassID (long) FK from tClasses
    NameID

    tNames
    --------------
    NameID
    FirstName
    LastNAme
    Address


    then in a form , put text boxs for StartDate and EndDate
    and a button to execute
    in the button CLICK event paste this code for it to run

    Code:
    Public Sub AddDateRecs()
    Dim vDate
    Dim sSql As String
    
    DoCmd.SetWarnings False
    vDate = txtStartDate
    GoSub PostDate   'post 1st day
    
    If Format(vDate, "ddd") <> "Wed" Then
          'get 1st wednesday
        While Format(vDate, "ddd") <> "Wed"
          vDate = DateAdd("d", 1, vDate)
        Wend
        GoSub PostDate
    End If
    
       'post EVERY wednesday
    While vDate < txtEndDate
       vDate = DateAdd("d", 7, vDate)
       GoSub PostDate
    Wend
    DoCmd.SetWarnings True
    Exit Sub
    
    PostDate:
    sSql = "insert into tWorkouts ClassDate ([ClassDate]) VALUES (" & vDate & ")"
    DoCmd.RunSQL sSql
    Return
    End Sub

  3. #3
    Tommo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Sep 2015
    Posts
    42
    Thanks mate.

    Will try in a bit and post back.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. How to start setting this DB up... (Dates)
    By gangel in forum Access
    Replies: 8
    Last Post: 04-30-2015, 09:07 AM
  3. Replies: 3
    Last Post: 12-06-2014, 03:59 AM
  4. Replies: 3
    Last Post: 11-03-2014, 03:30 PM
  5. List Dates between Start and Finish Dates
    By Buddus in forum Queries
    Replies: 1
    Last Post: 07-26-2013, 01:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums