Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: Building Filtering Strings for Reports

    I wrote code to build a filter string when two dates are entered. for this example I used 01/01 for date 1 and 02/02 for date 2.
    Code:
    ( (([PubList].[MonthPublished] > '01/01')) AND (([PubList].[MonthPublished] < '02/02')) )
    When the filter is applied to the report, the date 06/01 is not displayed like it should be.
    Code:
    DoCmd.OpenReport "AllData", acViewPreview, , (ABOVE FILTER STRING AS A VARIABLE)
    I'm new to filtering reports, so any help will be great. thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    Your dates in your filter appear to be coded as strings rather than dates, so you won't get the result you expect. Try replacing them with dates in this form

    #mm/dd/yyyy#
    Roger Hampson
    XI - ecs (UK) Ltd

  3. #3
    Join Date
    Aug 2002
    Posts
    21
    Stripped down, this is essentially the code:

    Code:
    strHold = "(([PubList].[MonthPublished] > '" & Me!DateOne & "')) AND (([PubList].[MonthPublished] < '" & Me!DateTwo & "')) "
    Code:
    DoCmd.OpenReport "AllData", acViewPreview, , strHold
    DateOne and DateTwo are textboxes formatted for short date and have an input mask. PubList.MonthPublished (original field in table) is in Date/Time.

    Am I not formatting the filter string correctly?


    Originally posted by xiecsuk
    Your dates in your filter appear to be coded as strings rather than dates, so you won't get the result you expect. Try replacing them with dates in this form

    #mm/dd/yyyy#

  4. #4
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    No I don't think you are. Try

    strHold = "(([PubList].[MonthPublished] > #" & Me!DateOne & "#)) AND (([PubList].[MonthPublished] < #" & Me!DateTwo & "#)) "

    If that doesn't work correctly, then use

    Format(Me!DateOne, "mm/dd/yyyy")

    and similarly for Me!DateTwo.
    Roger Hampson
    XI - ecs (UK) Ltd

  5. #5
    Join Date
    Aug 2002
    Posts
    21
    Thanks, that fixed it. I totally forgot about Format() too.

Posting Permissions

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