1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007/Filter Report By Date Range

Discussion in 'Business Applications' started by computerman29642, May 29, 2013.

Thread Status:
Not open for further replies.
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    I have an access report that I would like users to be able to enter a start and end date. The report will be filtered to display data that falls between the two dates.

    I have already created a form with date fields. I'm having a hard time figuring out how to use the form to filter the report. I also would like the report to display all data if teh user decides not to enter any dates.

    How can this be done?

    Any help will be greatly appreciated.
     
  2. WearerOfManyHats

    WearerOfManyHats

    Joined:
    May 28, 2013
    Messages:
    5
    There are many ways to do it, but I would set the filter in the OpenReport method when the report is launched, and provide logic to either set the start and end dates to extreme values (like 1/1/1904 thru 1/1/2999), which programming purists would tell you to never do, or not set the filter at all if the user doesn't enter a date.

    In the code for the button that launches the report:

    dtStart = Nz(Me.StartDate,#1/1/1904#)
    dtEnd = Nz(Me.EndDate,#1/1/2999#)
    DoCmd.OpenReport "Report1", acViewReport, "", "[MyDate]>=#" & dtStart & "# And [MyDate]<= #" _
    & dtEnd & "#", acNormal

    --- OR ---

    If Isnull(Me.StartDate) or Isnull(Me.EndDate) then
    DoCmd.OpenReport "Report1", acViewReport, "", , acNormal
    Else
    DoCmd.OpenReport "Report1", acViewReport, "", "[MyDate]>=#" & Me.StartDate & "# And [MyDate]<= #" & Me.EndDate & "#", acNormal
    End If

    The tricky part is getting tripped up by not including the #, designating a date literal.

    WearerOfManyHats
     
  3. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    I just add 2 controls to the form and if you use a query for your report then just use this criteria...

    >=[Forms]![Form1]![txtstart] And <=[Forms]![Form1]![txtend]


    and 2007 has the built in date picker so you will be all set.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1099975

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice