Results 1 to 4 of 4
  1. #1
    Icky_Joe is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Washington, DC
    Posts
    7

    IIF Date query giving same results for all records

    Hi again. I have used this site on several occassions to solve seemingly impossible results - so I am putting my lack of IIF knowledge out here hoping I will get to stamp this one "SOLVED".

    Here is my dilema:

    I need to create a column that will let me know what quarter the work was accomplished during the fiscal year.

    Here is my query:



    Reporting Quarter: IIf([Effective Date]=">#10/01/2011# and <#12/31/2011#","FY 12 Quarter 1",IIf([Effective Date]=">#01/01/2012# and <#03/31/2012#","FY 12 Quarter 2",IIf([Effective Date]=">#04/01/2012# and <#06/30/2012#","FY 12 Quarter 3",IIf([Effective Date]=">#07/01/2012# and <#09/30/2012#","FY 12 Quarter 4","Not Yet"))))

    All the results = "Not Yet"

    HELP!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, if [Effective Date] is a date/time field, you don't want the quotes around the values. Secondly, a test with And in it has to repeat the field:

    [Effective Date]=>#10/01/2011# and [Effective Date] <#12/31/2011#

    You may find the Switch function simpler, or best would probably be a table with Start/End dates and the quarter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Icky_Joe is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Washington, DC
    Posts
    7
    Thanks a million Paul.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. DCount on a Query giving #Name? error
    By Huddle in forum Access
    Replies: 9
    Last Post: 06-20-2012, 11:40 AM
  2. Access giving wrong result using sql query
    By amlanroy in forum Access
    Replies: 3
    Last Post: 05-10-2012, 10:11 AM
  3. Replies: 2
    Last Post: 01-27-2012, 09:49 PM
  4. Replies: 1
    Last Post: 03-09-2011, 02:04 AM
  5. Replies: 5
    Last Post: 10-08-2009, 05:15 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