Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146

    Unanswered: Reporting between two dates

    I have a database that is being used to track classified ads in a weekly newspaper which is released every Thursday. The entry form allows the operator to enter the 'Start Date' and the 'Number of Weeks' and the 'Stop Date' is mathematically calculated. (Each date is also associated with a unique 'Issue Number' so that 11/04 - Issue 19, 11/11 - 20, 11/18 - 21 and so on) Here is where the dilema comes into play. At the end of each week, which actually falls on the preceding Tuesday, I need a way to output all of the classifieds that would be active for that week to a report. I can easily do this for the week in which the classified was entered because that information is stored, but I don't know how to reference classifieds after that initial date. In other words, I need a way to get all classifieds between the two dates and including the 'Stop Date'... something like this:

    Active Classifieds = Between [StartDate] & [StopDate] including [StopDate]

    I don't know the best way to handle this, so I was hoping you guys could give some suggestions. I've also considered using Date() to create a query criteria similar to this:

    [StopDate] = ">= Date()"

    This would output all classifieds that are less than or equal to the current date... the only problem is that if the [StopDate] is null (as it may be), the query fails to pull that classified. Any help or suggestions on this would be greatly appreciated and I will certainly provide more examples or a sample copy of the DB if it will clarify my rambling. TIA.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Active Classifieds = Between [StartDate] AND [StopDate] including [StopDate]


    First off, your statement is indicating "Greater than OR Equal To" Date() rather than "Less than OR Equal To" Date() (corrected below). The Nz function utilized below will handle Null for [StopDate]. If [StopDate] is Null then it will automatically be considered Current Date.

    [StopDate] = Nz([StopDate], Date()) <= Date()

    A sample of your DB would be a real asset here:



  3. #3
    Join Date
    Sep 2004
    Location
    Raleigh, NC
    Posts
    146
    Ok, this is the database that I have to work with. It was started by someone else and I've been trying to fix it so I can't answer all questions about it. I've been having to hold SHIFT when I open it because the login doesn't work properly. The main form for the entire thing is called "Accounts". In other words, it's a mess and I apologize.
    Attached Files Attached Files
    Last edited by smacdonaldnc; 11-07-04 at 13:59.

Posting Permissions

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