Results 1 to 6 of 6
  1. #1
    toothpaste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9

    SQL for loop between dates

    I have the following 2 tables:

    • table Guide which includes the guide's name and work day.
    • table Activity which has a date and the guide id who created the activity

    Given a guide id and 1 date range, I would like to automatically create activities on the work days in which the guide can work.
    for example: Joe works on Mondays. Chloe works on Thursdays

    given Joe, from date January 1st 2017 and February 1st 2017 -> 5 Activities will be created


    given Chloe, from date January 1st 2017 and February 1st 2017 -> 4 Activities will be created

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    I'm not sure I could do it in SQL. easy enough to create a table with the dates in vba and then query them against the days and the people that you given


    Sent from my iPhone using Tapatalk

  3. #3
    toothpaste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9
    Can you please elaborate?

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Code similar to this would fill in Table datatable with the date you want

    You'd need to create a table called Datetable with two field

    datefield (date/time)
    dayfield (string)


    Code:
    Sub Main()
        Dim myDatestart As Date
        Dim myDateString As String
        Dim mydateend As Date
        Dim strSQL As String
        myDatestart = #1/1/2017#
        mydateend = #2/1/2017#
        
        
        Do While myDatestart <= mydateend
            myDateString = Format(myDate, "yyyy-mm-dd")
                  strSQL = "INSERT INTO DateTable (DateField,dayfield) VALUES(#" & myDateString & "#, " _
     & "Format(#" & myDateString & "#," & Chr(34) & "dddd" & Chr(34) & "))"
    
            CurrentDb.Execute strSQL
            myDatestart = myDatestart + 1
        Loop
    End Sub
    You could then query this table with the tables which you have (if I understand it right)

    Either a crosstab to get the total days for each worker or a select query to get all dates with tasks

  5. #5
    toothpaste is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    9
    Thank you, I used your code to do something very similar:

    Code:
        Do While myDateStart <= mydateEnd    
            myDateString = Format(myDateStart, "mm-dd-yyyy")
            currentDay = Weekday(myDateString, 1)
            
            'Is today his usual day?
            If currentDay = usualDay Then
                'Yes, add a new activity
                strSQL = "INSERT INTO activities (guide, garden, activityDate)" _
                & "VALUES (" & cboGuide & "," & cboGarden & ", #" & myDateStart & "#)"
                CurrentDb.Execute strSQL
            End If
    
    
            myDateStart = myDateStart + 1
        Loop

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Excellent job


    Sent from my iPhone using Tapatalk

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

Similar Threads

  1. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  2. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  3. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  4. Replies: 8
    Last Post: 12-02-2013, 03:46 PM
  5. Replies: 3
    Last Post: 03-10-2013, 07:04 AM

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