Results 1 to 6 of 6
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    Case when?

    Hello,
    So I'm trying to use MS Access criteria in SQL

    In Access, works great:
    Code:
    IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))
    In SQL, not working ... not sure what's wrong?
    Code:
    = (CASE WHEN MONTH(GETDATE()) <= 7 THEN DATEFROMPARTS(YEAR(GETDATE()) , 6 , 30) ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1 , 6 , 30) END)


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Access does not recognize CASE. Use IIf.

    VBA has Select Case.
    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
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by June7 View Post
    Access does not recognize CASE. Use IIf.

    VBA has Select Case.
    Hi June7,
    I think you misread my post ... I have the 1st code that's used in Access and it works fine.

    It's the SQL not working in SQL Server View, hence the post in the SQL Server forum

  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
    Offhand it looks okay. What does "not working" mean exactly? I'm not on a computer do can't test right now.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Hi pbaldy,
    So the result is not showing <=6/30/2017 even though there are records with <=6/30/2017

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you aren't testing records, you're testing today's date. If I do this:

    Code:
    select CASE WHEN MONTH(GETDATE()) <= 6 THEN DATEFROMPARTS(YEAR(GETDATE()) , 6 , 30) ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1 , 6 , 30) END as after
         , CASE WHEN MONTH(GETDATE()) <= 7 THEN DATEFROMPARTS(YEAR(GETDATE()) , 6 , 30) ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1 , 6 , 30) END as before
    I get what I would expect:

    after before
    2018-06-30 2017-06-30
    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. Change text case from upper to proper case
    By s.nolan in forum Access
    Replies: 1
    Last Post: 12-02-2015, 10:56 AM
  2. Replies: 3
    Last Post: 10-27-2014, 07:37 AM
  3. Case for in case field corrupt
    By Ruegen in forum Forms
    Replies: 9
    Last Post: 08-03-2014, 07:56 PM
  4. Replies: 5
    Last Post: 10-23-2012, 03:55 PM
  5. Case when
    By krufruf in forum Queries
    Replies: 2
    Last Post: 07-20-2012, 03:59 PM

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