Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date ranges (2000(sr3))

    I have a report that needs to filter on a date field (or two) in order to choose active clients. There is a Start Date and an End Date and they are separated by 6 months. So if the start date is Jan 15 the end date is July 15. So if I am printing the report for January, the client is active and if I am printing for August, they are not. I cannot, for the life of me, wrap my head around the math so that I get the correct output. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

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

    Re: Date ranges (2000(sr3))

    Are Start Date and End Date both fields in the table? Or is Start Date a field in the table and should End Date be calculated from it? Or something else?

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date ranges (2000(sr3))

    Currently they are both in the table. End is calculated in an afterupdate event from Start and again from Length if they change it from the default 6 months. I suppose there is no good reason to store end in the table now that you mention it and it certainly isn't too late to change it if there's a strong reason to do so.

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

    Re: Date ranges (2000(sr3))

    If it is a calculated value, you shouldn't store it in the table - you should calculate it on the fly in a query.

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

    Re: Date ranges (2000(sr3))

    To get back to your original question, where does the date for the report come in? Is it always the current date, or is the user prompted for it, or can the user enter the date on a form, or ...?

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date ranges (2000(sr3))

    The user will ultimately enter it via a form for choosing reports to print and there will be a reference to that field in the query. Currently I am using a prompt as I haven't decided on form design yet. Feel free to give me a formula for either. I can adapt it from there.
    I take your point in your other post. Recording the end date seemed like a good idea at the time...

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

    Re: Date ranges (2000(sr3))

    Enter your parameter prompt in the Field row in an empty column in the query, e.g. [Enter Date]
    Access will change it to something like Expr1: [Enter Date] but you don't have to worry about that.
    Clear the Show check box for the column.
    Enter an expression like this in the Criteria line for this column:

    Between [Start Date] And DateAdd("m",[Length],[Start Date])

    where Start Date is the name of the start date field and Length is the name of the duration field, assumed to be expressed in months.
    If you use a form, replace the parameter [Enter Date] with a reference to the start date text box, e.g. [Forms]![frmParam]![txtStartDate]

Posting Permissions

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