Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2015
    Posts
    17

    Unanswered: Problem with Access report with entered date range

    I'm trying to create a report that will show data based on entered date ranges.

    As an example, I want to show data based on a time/date field called SaleDate.

    I've tried to make this happen in a couple of different ways.

    Firstly, I've tried including code that will prompt for start and finish dates on the report
    itself.

    The code I'm using is
    " & [EnterStartDate] & " to " & [EnterEndDate]

    This works for the end date but the start date is not limiting i.e if I enter 2015 it finds everything before and after that date.

    I've also tried including the code in the query using the following instructions:

    To create the parameter query:

    Create a query to use as the RecordSource of your report.
    In query design view, in the Criteria row under your date field, enter:
    >= [StartDate] < [EndDate] + 1
    Choose Parameters from the Query menu, and declare two parameters of type Date/Time:
    StartDate Date/Time
    EndDate Date/Time
    To display the limiting dates on the report, open your report in Design View, and add two text boxes to the Report Header section. Set their ControlSource property to =StartDate and =EndDate respectively.

    Again, this works for the end date but again the start date is not limiting at all. I've checked the obvious things - the field type for the date is Date/Time etc but for the life of me I can't work out what I am doing wrong, or why I have encountered the same problem twice. I'm using Access 2013.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Presuming saledate IS of type date, then between should work.
    But if it is a date you need to supply s date value, not a year. So if you want a years worth of data either supply the DATE band (start: #01/01/2015#, finish: #12/31/2015#)
    Or use the year construct
    Year(saledate) = 2015
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Presuming saledate IS of type date, then between should work.
    But if it is a date you need to supply s date value, not a year. So if you want a years worth of data either supply the DATE band (start: #01/01/2015#, finish: #12/31/2015#)
    Or use the year construct
    Year(saledate) = 2015
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I see that your user name is dbasekiwi with the latter part suggesting that you might be 'down under,' as it were; is this correct? Whenever I see problems with Dates originating from that part of the world I have to wonder about the date format being used...as MS Access is US-oriented, when it comes to Date Formatting! You might have a look at guru Allen Browne's article on handling this kind of thing:

    International Dates in Access

    Allen is from Perth, I believe it is, and has a good handle on this.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    139
    Allen Browne has a small function (SQLDate) that forces your data into the date format used by JET SQl. It works (solved my problems.

    Function SQLDate(varDate As Variant) As String
    'Purpose: Return a delimited string in the date format used natively by JET SQL.
    'Argument: A date/time value.
    'Note: Returns just the date format if the argument has no time component,
    ' or a date/time format if it does.
    'Author: Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
    If DateValue(varDate) = varDate Then
    SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
    Else
    SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
    End If
    End If
    End Function

    Regards

    J Smith
    Aylmer, Quebec, Canada

Tags for this Thread

Posting Permissions

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