Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Location
    USA
    Posts
    14

    Question Unanswered: Using Date Criteria 6 months

    I could use some help on a report that is driving me nuts.

    We are using Access 97 currently on a network that will have approximately 10 users.

    I need a report to show all information completed in the month 6 months from the past month. My form provides a to and from date fields that are entered. I tried a hidden text box that calculates 180 days from the system date, but that provides errors as information left out and information added that is for the next month. Skewing the results of the report.

    So if you enter October 1, 2003 in the "To" text box and "October 31, 2003" in the "From" text box the report should then pull all completed item six months from October, which would be everything completed in April 2003. I am using the criteria: =DateAdd("m",6,Date()) The is under the Date of Removal field.

    I should receive 45 results of this, but receive 0.
    Any advice or where to look for a better way would be much appreciated.

    Thank you in advance.
    =DateAdd("m",6,Date())

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    try -6 to go back 6 months

  3. #3
    Join Date
    Apr 2003
    Location
    USA
    Posts
    14
    Originally posted by jmrSudbury
    try -6 to go back 6 months

    I tried that. my results ended up being for the month of May. Do you have any ideas on how to obtain the results for the month of April?
    The supervisors will be pulling the information at the beginning of the 7th month. I replaced the 6 with a 7 but received zero results.

  4. #4
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    what happened when you replaced it with -7?

  5. #5
    Join Date
    Apr 2003
    Location
    USA
    Posts
    14
    I did not receive any results. I checked the master table only to find thier is results thier.

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    I just made this in a new module:

    Public Function testdt()
    Dim x
    x = DateAdd("m", -7, Date)
    End Function

    x ends up getting 2003/04/05.

  7. #7
    Join Date
    Apr 2003
    Location
    USA
    Posts
    14
    Originally posted by jmrSudbury
    I just made this in a new module:

    Public Function testdt()
    Dim x
    x = DateAdd("m", -7, Date)
    End Function

    x ends up getting 2003/04/05.

    How do I get the query to use the module as criteria? I have only added criteria to under the field in design view.

  8. #8
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Oh! For criteria in a query! For the column put:

    format([strDate],"yyyymm")

    and the criteria would be:

    format(dateadd("m",-7,date()),"yyyymm")

    This will give you all records where strDate is in April if the current month is November.

Posting Permissions

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