Results 1 to 6 of 6
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    First and Last Dates Of Week

    Hi All, I am trying to output records to Excel for every day of the week, how do i find first date of this week number and last date of this week number ? So the end result would be on the Excel pages

    Dim mWeek as Long
    Dim mStart A Date, mEnd As Date
    Dim dt1 as Date, dt2 as Date, dt3 as Date, dt4 as Date, dt5 as Date, dt6 as Date

    mWeek = Format(Now(), "ww")
    mStart = Find Sundays date from mWeek ??
    mEnd = Find Saturdays date from mWeek ??
    dt1 = Format(mStart,"ddd-dd-mm-yy")
    dt2 = Format(mStart,"ddd-dd-mm-yy") + 1 will this result in Mon-02-12-18 ?
    dt3 = Format(mStart,"ddd-dd-mm-yy") + 2 will this result in Tue-03-12-18 ?

    Sheet1 = "Week" & " " & (mWeek) & " " & "Overview"
    Sheet2 = dt1
    Sheet3 = dt2
    Sheet4 = dt3
    Sheet5 = dt4


    Sheet6 = dt5
    Sheet7 = dt6
    Sheet8 = dt7

    I can then set my recordset between mStart and mEnd For the overview Sheet1

    Then set the other recordsets where date = #" dt1 & "#" etc etc.....

    I can then do all recordsets to suit

    thanks in advance

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    When using US week definition
    First day of current week:
    Code:
    =Date()-Weekday(Date())+1
    Last day of current week:
    Code:
    =Date()+7-Weekday(Date())

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Fairly common topic. Review https://www.tek-tips.com/faqs.cfm?fid=3103
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Thanks for info, so would i have this correct now that would give me first and last dates of this week ?

    dt1 = Me.txtUpdate - Weekday(Me.txtUpdate) + 1
    dt2 = Me.txtUpdate + 1 - Weekday(Me.txtUpdate)
    dt3 = Me.txtUpdate + 2 - Weekday(Me.txtUpdate)
    dt4 = Me.txtUpdate + 3 - Weekday(Me.txtUpdate)
    dt5 = Me.txtUpdate + 4 - Weekday(Me.txtUpdate)
    dt6 = Me.txtUpdate + 5 - Weekday(Me.txtUpdate)
    dt7 = Me.txtUpdate + 6 - Weekday(Me.txtUpdate)

  5. #5
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    This is what i am stuck with ????, the Excel file opens and sheet 1 says 2018 Week 49 Overview but Sheet2 doesn't say Mon-02-18.....

    Dim dt1 As Date, dt2 As Date, dt3 As Date, dt4 As Date, dt5 As Date, dt6 As Date, dt7 As Date
    Dim dt1n As Date, dt2n As Date, dt3n As Date, dt4n As Date, dt5n As Date, dt6n As Date, dt7n As Date


    mYear = DatePart("yyyy", Me.txtUpdate)
    mMonth = DatePart("m", Me.txtUpdate)
    mWeek = DatePart("ww", Me.txtUpdate)




    '=Date()-Weekday(Date())+1
    '=Date()+7-Weekday(Date())


    dt1 = Me.txtUpdate - Weekday(Me.txtUpdate) + 1
    dt2 = Me.txtUpdate + 1 - Weekday(Me.txtUpdate)
    dt3 = Me.txtUpdate + 2 - Weekday(Me.txtUpdate)
    dt4 = Me.txtUpdate + 3 - Weekday(Me.txtUpdate)
    dt5 = Me.txtUpdate + 4 - Weekday(Me.txtUpdate)
    dt6 = Me.txtUpdate + 5 - Weekday(Me.txtUpdate)
    dt7 = Me.txtUpdate + 6 - Weekday(Me.txtUpdate)


    dt1n = Format(dt1, "dd-mm-yy")
    dt2n = Format(dt2, "dd-mm-yy")
    dt3n = Format(dt3, "dd-mm-yy")
    dt4n = Format(dt4, "dd-mm-yy")
    dt5n = Format(dt5, "dd-mm-yy")
    dt6n = Format(dt6, "dd-mm-yy")
    dt7n = Format(dt7, "dd-mm-yy")


    mStart = dt1
    mEnd = dt7
    mStatus = "Pending"
    mShipDate = Forms!frmMainMenu!frmIndex1!cboShipmentDateIndex1
    strPathName = "T:" & "DMT DELIVERIES" & ".xlsx"
    If Len(Dir(strPathName)) > 0 Then
    Kill strPathName
    End If
    Cust = Forms!frmMainMenu!frmIndex1!cboCustomer
    Src = "Acc"
    mYear = DatePart("yyyy", mStart)
    Set ApXL = CreateObject("Excel.Application")
    Set XLWb = ApXL.Workbooks.Add
    intSheets = XLWb.Worksheets.Count
    Set xlSheetLast = XLWb.Worksheets(intSheets)
    Set xlSheet = XLWb.Worksheets.Add(, xlSheetLast, 1, xlWorksheet)
    ApXL.ActiveWorkbook.SaveAs (strPathName)
    Set XLWb = ApXL.Workbooks.Open(strPathName)
    ApXL.Visible = True
    Set XlWs1 = XLWb.Worksheets("Sheet1")
    XlWs1.Name = "Year" & " " & mYear & "-" & "Week" & " " & mWeek & " " & "Overview"
    Set xlWs2 = XLWb.Worksheets("Sheet2")
    xlWs2.Name = dt1n
    Set xlWs3 = XLWb.Worksheets("Sheet3")
    xlWs3.Name = dt2n
    Set xlWs4 = XLWb.Worksheets("Sheet4")
    xlWs4.Name = dt3n
    Set xlWs5 = XLWb.Worksheets("Sheet5")
    xlWs5.Name = dt4n
    Set xlWs6 = XLWb.Worksheets("Sheet6")
    xlWs6.Name = dt5n
    Set xlWs7 = XLWb.Worksheets("Sheet7")
    xlWs7.Name = dt6n
    Set xlWs8 = XLWb.Worksheets("Sheet8")
    xlWs8.Name = dt7n


    Set rs = CurrentDb.OpenRecordset("SELECT tblEdit.SONumber, tblEdit.DelTo, tblEdit.PONumber, tblEdit.LiftType, tblEdit.LiftNo, tblEdit.Status, tblEdit.ShipmentDate FROM tblEdit WHERE ShipmentDate = #" & mShipDate & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY Status;")
    Set rs2 = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate = #" & dt1 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs3 = CurrentDb.OpenRecordset("SELECT tblCollections.SONumber, tblCollections.DelTo, tblCollections.PONumber, tblCollections.LiftType, tblCollections.LiftNo, tblCollections.Status, tblCollections.CollectedDate FROM tblCollections WHERE CollectedDate = #" & dt1 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs4 = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate = #" & dt2 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs5 = CurrentDb.OpenRecordset("SELECT tblCollections.SONumber, tblCollections.DelTo, tblCollections.PONumber, tblCollections.LiftType, tblCollections.LiftNo, tblCollections.Status, tblCollections.CollectedDate FROM tblCollections WHERE CollectedDate = #" & dt2 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs6 = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate = #" & dt3 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs7 = CurrentDb.OpenRecordset("SELECT tblCollections.SONumber, tblCollections.DelTo, tblCollections.PONumber, tblCollections.LiftType, tblCollections.LiftNo, tblCollections.Status, tblCollections.CollectedDate FROM tblCollections WHERE CollectedDate = #" & dt3 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs8 = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate = #" & dt4 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs9 = CurrentDb.OpenRecordset("SELECT tblCollections.SONumber, tblCollections.DelTo, tblCollections.PONumber, tblCollections.LiftType, tblCollections.LiftNo, tblCollections.Status, tblCollections.CollectedDate FROM tblCollections WHERE CollectedDate = #" & dt4 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs10 = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate = #" & dt5 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs11 = CurrentDb.OpenRecordset("SELECT tblCollections.SONumber, tblCollections.DelTo, tblCollections.PONumber, tblCollections.LiftType, tblCollections.LiftNo, tblCollections.Status, tblCollections.CollectedDate FROM tblCollections WHERE CollectedDate = #" & dt5 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs12 = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate = #" & dt6 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs13 = CurrentDb.OpenRecordset("SELECT tblCollections.SONumber, tblCollections.DelTo, tblCollections.PONumber, tblCollections.LiftType, tblCollections.LiftNo, tblCollections.Status, tblCollections.CollectedDate FROM tblCollections WHERE CollectedDate = #" & dt6 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs14 = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate = #" & dt7 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")
    Set rs15 = CurrentDb.OpenRecordset("SELECT tblCollections.SONumber, tblCollections.DelTo, tblCollections.PONumber, tblCollections.LiftType, tblCollections.LiftNo, tblCollections.Status, tblCollections.CollectedDate FROM tblCollections WHERE CollectedDate = #" & dt7 & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "' ORDER BY SONumber;")


    Set XLWb = ApXL.Workbooks.Open(strPathName)
    With XLWb
    .Worksheets(1).Cells(1) = "PRESS AND HOLD 'CTRL'"
    .Worksheets(1).Cells(2, 1) = "AND PRESS 'F' KEY"
    .Worksheets(1).Cells(3, 1) = "WHEN THE SEARCH BOX APPEARS"
    .Worksheets(1).Cells(4, 1) = "TYPE YOUR SEARCH"
    .Worksheets(1).Cells(1, 3) = "SL-NUMBER"
    .Worksheets(1).Cells(1, 4) = "DEALER"
    .Worksheets(1).Cells(1, 5) = "PO-NUMBER"
    .Worksheets(1).Cells(1, 6) = "LIFT TYPE"
    .Worksheets(1).Cells(1, 7) = "LIFT DETAILS"
    .Worksheets(1).Cells(1, 8) = "STATUS"
    .Worksheets(1).Cells(1, 9) = "DELIVERY"
    .Worksheets(1).Cells(3, 3).CopyFromRecordset rs
    .Worksheets(1).Range("C3:I3").Borders(xlEdgeTop).L ineStyle = xlContinuous
    .Worksheets(1).Cells.EntireColumn.AutoFit
    .Worksheets(1).Cells.EntireColumn.HorizontalAlignm ent = xlLeft

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    It looks to me as too complex way to do things. Consider:

    1. Create a Calendary table in Access (In your Access database, or in separate Access application and link it into your database, or in separate Access application and you export it into your database. The idea with separate application is, that you can to use it in several different databases, and whenever you extend this Calendary table, you have to edit it once, and it is easily updated in all databases using it.). This calendar table has date as DI-field, and it can be filled for any number of years into future you find reasonable. The table has additional columns with various info about every day in range of filled dates (year number, month number, quarter number, weekday number, week number, holiday ID, working hours, whatever other). You can use this table in various reports or queries;
    2. In your database, create a stored query based on tblCollections, and on Calendary table;
    3. In Excel workbook, create an ODBC query, which reads data from stored query in Access database. The query will be use entry in certain Excel cell as parameter. E.g. in Access stored query you have month number stored in format "yyyymm", and you enter the month number in same format into excel cell. The query returns all entries for this month from Access database automatically whenever you edit the cell where month number is stored . When you need e.g. separate yearly or weekly reports, you can have a sheet for every different type with own query. Or when the design of all reports is same for all types, then you can have a single report with start and end date as parameters instead of e.g. month number.

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

Similar Threads

  1. Print Reports by day for next week with Correct Dates
    By LogicalHamster in forum Reports
    Replies: 8
    Last Post: 09-20-2018, 08:41 AM
  2. Query dates for current week and prior
    By farmerde in forum Queries
    Replies: 3
    Last Post: 01-28-2016, 03:58 PM
  3. Replies: 9
    Last Post: 06-19-2015, 03:37 PM
  4. Show start of week between dates
    By Perceptus in forum Reports
    Replies: 1
    Last Post: 11-04-2014, 03:48 PM
  5. Converting Dates into week numbers (custom)
    By Bleep-Blop in forum Programming
    Replies: 5
    Last Post: 10-04-2013, 03:29 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