Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Date range (Access 2000)

    I have the following two parameter fields in a totals query to allow entry of a date range.

    [From date: (Enter = 1/1)]
    Where
    Is Null Or <=[Date]
    (not shown)

    [To date: (Enter = 31/12)]
    Where
    Is Null Or >=[Date]
    (not shown)

    1. How can I modify this to enable the same date to be entered for both parameters so that only data from that one date is included? Currently, if I enter the same date for both parameters, no records are selected.

    2. How can I include the dates in the title of a report based on the query? They do not appear in the field list.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range (Access 2000)

    1. As far as I can see, you should get records for which [Date] equals [From date: (Enter = 1/1)] if you enter the same date for [From date: (Enter = 1/1)] and [To date: (Enter = 31/12)]. Are you sure there are records for the date you enter?

    2. You can set the Control Source of a text box on the report to =[From date: (Enter = 1/1)] and set the Format of the text box to the desired date format (otherwise, the date will be displayed exactly as entered by the user). You can also use something like

    =IIf(IsNull([From date: (Enter = 1/1)]), "", "Data from " & Format([From date: (Enter = 1/1)], "dddd d mmmm yyyy"))

    or similar.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Date range (Access 2000)

    [HV:] 1. As far as I can see, you should get records for which [Date] equals [From date: (Enter = 1/1)] if you enter the same date for [From date: (Enter = 1/1)] and [To date: (Enter = 31/12)]. Are you sure there are records for the date you enter?

    Yes, there are records for the single date that I enter in both From and To date parameter fields, but no records are displayed. If I enter From = 1 July, To = 2 July, then I get records for 1 July only, whereas in that case I want to display records for 1 and 2 July. In other words, both From and To parameters should be inclusive.

    I also get #Error in the From and To dates in report headers (see below) if I enter the same date for the From and To parameters.

    [HV:] 2. You can set the Control Source of a text box on the report to =[From date: (Enter = 1/1)] and set the Format of the text box to the desired date format (otherwise, the date will be displayed exactly as entered by the user).

    I tried this but the date is not formatted according to the Format that I set, even if I enter the full date ("01/07/2005"), although I usually only want to enter an abbreviated date like "1/7".

    [HV:] You can also use something like =IIf(IsNull([From date: (Enter = 1/1)]), "", "Data from " & Format([From date: (Enter = 1/1)], "dddd d mmmm yyyy")) or similar.

    I tried this and it works fine with abbreviated dates, thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range (Access 2000)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Date range (Access 2000)

    I am unable to post a copy of the database, but here is an illustration to clarify what is happening.

    Data:
    [Item][Date]
    1, 01/07/2005
    2, 01/07/2005
    3, 02/07/2005
    4, 02/07/2005

    "From 1/7 To 1/7" lists no records, but should list:
    1, 01/07/2005
    2, 01/07/2005

    "From 1/7 To 2/7" lists:
    1, 01/07/2005
    2, 01/07/2005

    but should list:
    1, 01/07/2005
    2, 01/07/2005
    3, 02/07/2005
    4, 02/07/2005

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range (Access 2000)

    Are you absolutely sure that your criteria contain "less than or equal to" and "greater than or equal to" as in your first post in this thread?

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Date range (Access 2000)

    Yes, I've checked the logical operators, but I've just realized what may be the problem: the date field is actually a date/time, so presumably I need to compare just the date part of this. I tried various things to isolate the date part (Int, DatePart), but they didn't work; however, if I just subtract 1 from the To date, that seems to work, but would it be including all and only the times on the entered date?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range (Access 2000)

    You could also use Int([Date]) instead of [Date] in the criteria.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Date range (Access 2000)

    When I use "Int([Orders.CreatedDateTime])" in the criteria, I get the error: "This expression is typed incorrectly, or it is too complex to be evaluated...." It is typed correctly, but the field is in a linked SQL table: would this be causing the problem with Int?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range (Access 2000)

    It might just be that the calculation within the criteria makes it too complicated.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Date range (Access 2000)

    I have now added these From date and To date parameter fields to a new crosstab query, but I get an error message: "The MS Jet database engine does not recognize '[From Date: (Enter = First)]' as a valid field name or expression." I have not had this error before with other queries. Is this a limitation of crosstab queries?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date range (Access 2000)

    Select Query | Parameters...
    Type the first parameter [From Date: (Enter = First)] exactly as used in the criteria, and select Data/Time as data type.
    Type the second parameter exactly as used in the criteria, and select Data/Time as data type.
    Click OK.

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Re: Date range (Access 2000)

    Thanks, Hans. Is this required only with some query types?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

Posting Permissions

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