Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    1

    Question Unanswered: Report or Query based on the date

    I can't seem to find out how to do this report.

    Q: I need to find entries within a table field dated within a month of the <b>current date</b> or report/query date.

    My database is as follows.

    donors | donations | project

    where donors to donations is a one-to-many. one donor makes many donations. donations to project is also a 1-M. The donation is applied to a requested project/event.

    A report needs to be run listing the donors within the last 30, 60, 90 etc. days. But, how would I do this? The date the report is being run is always changing, and is a variable.

    As of now, I have a report listing <b>all</b> donors. How would I constrain this? a WHERE statement?

    Suggestions?

    Thanks,
    htmlbuddy

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I'm assuming you do have a Date field in your Table and it contains a Data Type of Date/Time.

    Try this...

    Code:
    'Declare a String variable named WhereStrg
    Dim WhereStrg As String
    
    'Fill the WhereStrg string variable with criteria for the
    'OpenReport method.
    WhereStrg = "[yourTablesDateField] BETWEEN #" & DateAdd("d", -30, Date) & "# AND #" & Date & "#"
    
    'Open the Report with supplied criteria held in WhereStrg...
    DoCmd.OpenReport "youReportName", acViewPreview, , WhereStrg
    Notice within the DateAdd Function there is a -30. This will tells the engine to look back 30 days from todays date. If you change this to -90 then it will look back 90 days from todays date. If you change it to -120 then it will look back 120 days from todays date...and so on.

    So what the query is saying, "Give us only records that contain a date in the table Date field which are 30 days from todays date up to and including todays date.

    You can also use a variable in place of the -30 if you like.

    that way you could perhaps have something like:

    Code:
    'Declare Variables
    Dim WhereStrg As String
    Dim DaysBack, DBack As Integer
    
    'Get the number of days to go back for report from the User...
    DaysBack = InputBox("How Many Days Back Do You Want To Base Report From (i.e.:  30, 90, 120, etc.)?", _
                    "Look How Far Back", 30)
       
    'If the User selected Cancel from the Input Box or entered 
    'anything other than a number then exit...don't show report.
    'The use in programming for robotics comes in handy here so
    'that you can make a robotic arm come out and slap the User
    'in the back of the head.   ;)
    If DaysBack = "" Or IsNumeric(DaysBack) = False Then
       'Get outta here.
       Exit Sub   '(or Exit Function...whatever)
    Else   'A valid entry was made.
       'Fill the DBack Integer variable with the supplied number 
       'the User but first ensure it is converted to an Integer Type. 
       DBack = CInt(DaysBack)
    End If
    
    'If the User entered a positive number then
    'convert it to a negitive number but if the 
    'User actually entered say, -30 then leave it.
    If DBack > 0 Then DBack = Not DBack
    
    'Fill the WhereStrg string variable with criteria for the
    'OpenReport method.
    WhereStrg = "[yourTablesDateField] BETWEEN #" & DateAdd("d", DBack, Date) & "# AND #" & Date & "#"
    
     'Open the Report with supplied criteria held in WhereStrg...
    DoCmd.OpenReport "youReportName", acViewPreview, , WhereStrg
    Hope this helps...
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Feb 2004
    Posts
    214

    ...

    if I understand what you are asking correctly, you simply put the following in the date criteria in a query
    >=Date()-30 and <=Date()
    This will pull all dates that occured in the last 30 days
    You just change the 30 to 60 or 90 depending on how far back you want to go

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, depending on requirements, htere is a Month() function...
    Code:
    WHERE Month(yourDateField) = Month(Date())
    But remember that it might also be an idea to check the year as well - January is still January, no matter what the year
    George
    Home | Blog

Posting Permissions

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