Results 1 to 7 of 7
  1. #1
    wilsoa is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2012
    Posts
    4

    Query Date Range Based on Calculated Fields

    I currently have a Query that calculates out a date based on a field in my primary table. I want to have another field query on that one to bring in dates from other fields in my primary table. Here is what I have so far:



    AnnualCalc: IIf([LastAnnualDate]>=DateSerial(Year(Date()),Month(Date()),1) And [LastAnnualDate]<DateSerial(Year(Date()),Month(Date())+1,1),[AnnualValue],"")

    The LastAnnualDate is calculated on the AdmissionDate, and the AnnualValue returns the calculations from text back to date form. What I would like to have happen is to have the AnnualCalc pull dates from LastAnnualDate between 10 and 14 months prior to the current month. If there is a date that falls between the 10 and 14 months prior, then I want the AnnualValue to show, otherwise, nothing. Ideally, I would like to have the same parameters for another field, N_Annual.

    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Try DateAdd function:

    IIf([LastAnnualDate] BETWEEN DateAdd("m",-14,DateSerial(Year(Date()),Month(Date()),1)) AND DateAdd("m",-10,DateSerial(Year(Date()),Month(Date())+1,1)),[AnnualValue],"")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    wilsoa is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2012
    Posts
    4

    Almost there...

    Quote Originally Posted by June7 View Post
    Try DateAdd function:

    IIf([LastAnnualDate] BETWEEN DateAdd("m",-14,DateSerial(Year(Date()),Month(Date()),1)) AND DateAdd("m",-10,DateSerial(Year(Date()),Month(Date())+1,1)),[AnnualValue],"")

    I tried the DateAdd function, and it worked beautifully! Thank you. However, I need to add the same function, but have an OR in there. Somthing like:

    (IIf([LastAnnualDate] BETWEEN DateAdd("m",-14,DateSerial(Year(Date()),Month(Date()),1)) AND DateAdd("m",-10,DateSerial(Year(Date()),Month(Date())+1,1)),[AnnualValue],"")) OR (IIf([N_Annual] BETWEEN DateAdd("m",-14,DateSerial(Year(Date()),Month(Date()),1)) AND DateAdd("m",-10,DateSerial(Year(Date()),Month(Date())+1,1)),[AnnualValue],""))

    When I put the above in, I get the result of -1 ...

    *******
    After looking at another query of mine, I revised the above and came up with this:

    IIf([LastAnnualDate] BETWEEN DateAdd("m",-14,DateSerial(Year(Date()),Month(Date()),1)) AND DateAdd("m",-10,DateSerial(Year(Date()),Month(Date())+1,1)),[AnnualValue],IIf([N_Annual] BETWEEN DateAdd("m",-14,DateSerial(Year(Date()),Month(Date()),1)) AND DateAdd("m",-10,DateSerial(Year(Date()),Month(Date())+1,1)),[AnnualValue],""))

    However, this particular function returns the same dates needed as the first function (posted by June7), but I get an #Error for all others. I separated out both IIf functions and ran them separately and they worked as needed, but I would like to nest them together.

    *******
    Update
    *******
    I was looking over the calculated fields I have, and went another way by basing the function off a different calc field instead of [LastAnnualDate]. That worked good, but when I convert the calc field with the IIf function, I get the #Error for records that are left blank. If anyone has a solution for me on this, that would be helpful. For the time being, I have replaced the "" in the IIf function with having it return "1/1/1900", which is far before any date I will request. :-)

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Could try Null instead of the "" or 1/1/1900.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    wilsoa is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2012
    Posts
    4

    Changes...

    Quote Originally Posted by June7 View Post
    Could try Null instead of the "" or 1/1/1900.
    I have been working with my query some more, and I almost have what I need. Here is what I am struggling with:

    AnnualCalc:
    IIf(
    [AnnualDue]<(Date())
    And
    [N_Annual] Between DateAdd("m",-2,DateSerial(Year(Date()),Month(Date()),1)) And DateAdd("m",+2,DateSerial(Year(Date()),Month(Date( ))+1,1))
    And
    [LastAnnualDate] Between DateAdd("m",-2,DateSerial(Year(Date()),Month(Date()),1)) And DateAdd("m",+2,DateSerial(Year(Date()),Month(Date( ))+1,1)))
    ,DateAdd("yyyy",1,[AnnualDue]),[AnnualDue])

    I am able to get the last two conditions to work perfectly, and return what I want for the AnnualCalc. However, I can't seem to include the first condition. What I would like to have happen is when the last two conditions are met, AND if the first is true [AnnualDue]<(Date()), then I want it to perform the DateAdd portion to the query, otherwise return AnnualDue.

    For example:
    Annual Due: 1/3/12
    N_Annual: 1/2/12
    LastAnnualDate: 1/1/12
    Date(): 3/8/12
    Annual Calc: (Should return) 1/3/13

    Currently, the Annual Calc is returning: 1/3/12

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Looks like an extra paren to me, marked in red:

    AnnualCalc:
    IIf(
    [AnnualDue]<(Date())
    And
    [N_Annual] Between DateAdd("m",-2,DateSerial(Year(Date()),Month(Date()),1)) And DateAdd("m",+2,DateSerial(Year(Date()),Month(Date( ))+1,1))
    And
    [LastAnnualDate] Between DateAdd("m",-2,DateSerial(Year(Date()),Month(Date()),1)) And DateAdd("m",+2,DateSerial(Year(Date()),Month(Date( ))+1,1))
    )
    , DateAdd("yyyy",1,[AnnualDue]), [AnnualDue])

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    wilsoa is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2012
    Posts
    4

    Sweet!

    Quote Originally Posted by June7 View Post
    Looks like an extra paren to me, marked in red:

    AnnualCalc:
    IIf(
    [AnnualDue]<(Date())
    And
    [N_Annual] Between DateAdd("m",-2,DateSerial(Year(Date()),Month(Date()),1)) And DateAdd("m",+2,DateSerial(Year(Date()),Month(Date( ))+1,1))
    And
    [LastAnnualDate] Between DateAdd("m",-2,DateSerial(Year(Date()),Month(Date()),1)) And DateAdd("m",+2,DateSerial(Year(Date()),Month(Date( ))+1,1))
    )
    , DateAdd("yyyy",1,[AnnualDue]), [AnnualDue])

    June,

    Thank you for your eyes! I was racking my brain trying to get it to work, and became cross-eyed. :-)

    I also changed the DateAdd's to -4 to include more months for "Past due" annuals. Everything is working like it should!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Range Report with Multiple Date Fields
    By StevenCV in forum Reports
    Replies: 13
    Last Post: 02-29-2012, 08:29 AM
  2. joins based on date range
    By broecher in forum Database Design
    Replies: 4
    Last Post: 11-04-2011, 06:32 PM
  3. Run query by date range
    By ARickert in forum Access
    Replies: 2
    Last Post: 06-23-2011, 10:54 AM
  4. Replies: 13
    Last Post: 09-27-2010, 03:10 PM
  5. Replies: 3
    Last Post: 09-29-2009, 07:08 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums