Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    18

    Unanswered: Generate weekly and monthly date ranges from button

    I have two unbound textboxes on a form, one for reportBegin and one for reportEnd that prints a report by date range. I am trying to add a few buttons that will enter into these two textboxes dates for today, the current week and the current month. I have found a sample code that does this, but it is for an Access 2000 database and I am using 2007. (Here is where I found it Date Parameters for generating Microsoft Access Reports | Database Solutions for Microsoft Access | databasedev.co.uk) I have the today button working, but the week and month I cannot get to work. Here is the code I have:

    Code:
    Private Sub Form_Load()
    reportBegin.SetFocus
    
    End Sub
    
    ____________________________________________________
    
    Private Sub cmdtoday_Click()
    'Sets the Date From and Date To text boxes
    'to Today's Date
    
    
    
        Me.reportBegin = Date
        Me.reportEnd = Date
    
    ExitAddDate:
    
    End Sub
    
    ____________________________________________________
    
    Private Sub cmdweek_Click()
    'Sets the Date From and Date To text boxes
    'to show complete working week (Mon - Fri)
        
        Dim today As Variant
    
        today = Weekday(Date)
        Me.reportBegin = DateAdd("d", (today * -1) + 2, Date)
        Me.reportEnd = DateAdd("d", 6 - today, Date)
    
    End Sub
    
    ____________________________________________________
    
    Private Sub cmdmonth_Click()
    'Sets the Date From and Date To text boxes
    'to show complete month (from start to end of current month)
    
        Me.reportBegin = CDate("01/" & Month(Date) & "/" & Year(Date))
        Me.reportEnd = DateAdd("d", -1, DateAdd("m", 1, Me.reportBegin))
    
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
        FirstDayOfWeek = DateAdd("d", -(DatePart("w", Now)) + 2, Now)
        LastDayOfWeek = DateAdd("d", 6 - DatePart("w", Now), Now)
        FirstDayOfMonth = CDate("01/" & Month(Now) & "/" & Year(Now))
        LastDayOfMonth = DateAdd("d", -1, DateAdd("m", 1, FirstDayOfMonth ))
    Have a nice day!

  3. #3
    Join Date
    Mar 2009
    Posts
    18
    The weekly code worked great, but the monthly code was giving me From March 3, 2009 To January 29, 1900 for some reason. I did get it working finally with the following code though. Thanks for your help!

    Code:
    Me.reportBegin = DateSerial(Year(Date), Month(Date), 1)
    Me.reportEnd = DateSerial(Year(Date), Month(Date) + 1, 0)

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    The reason why you received stange results is probably linked to the International Settings of the Control Pannel in Windows. This can be solved by using extra parameters when calling the DateAdd function.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •