Results 1 to 13 of 13

CASE WHEN DATEFROMPARTS result?

  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    401

    CASE WHEN DATEFROMPARTS result?

    Hello,
    Can someone tell me what result this produces?
    I am getting no records.

    Code:
    CASE WHEN MONTH(GETDATE()) <= 7 THEN DATEFROMPARTS(YEAR(GETDATE()) , 6 , 30) ELSE DATEFROMPARTS(YEAR(GETDATE()) + 1 , 6 , 30) END OR IS NULL
    I want to see all records with EndDates as specified in the criteria but it is returning no results.
    However, before 2017 I was getting results.


    I think that it's saying to display records where the EndDates are less than July ending 6/30/ of current year otherwise display where the EndDates are in the future year ending 6/30/ next year
    So if today is less than the month of July it looks at EndDates with 6/30/2017 otherwise looks at EndDates with 6/30/2018 or if itís NULL

  2. #2
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    2,809
    That would be my guess, but it's just a guess since you provide no clue as to the values returned by what look like two functions (another guess). If there is a way to debug.print the value of DATEFROMPARTS when a given year is passed to it, I'd do that to confirm. I could be all wet - maybe those aren't UDF's but are part of ODBC syntax. I presume you're using this against a non-Access db since Access doesn't support CASE WHEN and you say it worked in the past.
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    401
    Why this is posted to the SQL Server section

  4. #4
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    2,809
    Sorry. Didn't notice. I alwayshould get to the forum via my bookmark and go to new posts. Don't know how I got to your post if I did that.

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    401
    More information to help what's expected

    It's against a field called EndDate (expiration date of a membership) Memberships are from 7/1/## to 6/30/## following year for a 1 year membership.
    What I don't understand is, it was working in 2016 and now that it's 2017, it's not returning any records when there are many records (memberships) ending in 6/30/2018.
    Expectation is to see all records with a payment date range of whatever the user inputs in Excel or CRW or whatever external file linked to the SQL View having the EndDate clause of membership records.
    Example: payment date range of 12/1/2016 through 1/5/2017 should be producing membership records with EndDates on 6/30/2017. And once July 2017 comes it'll produce records with EndDates on 6/30/2018.

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    17,017
    Quote Originally Posted by aellistechsupport View Post
    it's not returning any records when there are many records (memberships) ending in 6/30/2018.
    At this moment it returns 2017-06-30; are there records with that end date? It sounds like you have this in the criteria of something?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    401
    It is a criteria against the field EndDate.
    Yes, there are memberships with 6/30/2018 EndDates.
    It was producing results in 2016, now that it's 2017, it is not producing any records.
    Including payment date ranges where it was producing results before 2017.

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    17,017
    You keep saying it has 2018 dates; are there 2017 dates? That's what it's looking for now.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    401
    Yes there are records with EndDate 6/30/2017

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    17,017
    What's the full SQL? Are there other criteria that may be excluding the 2017 records? There's no time component in the date?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  11. #11
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    401
    Unfortunately I can't access the server to get the full SQL however there is not other criteria that would limit the EndDate range.
    There is no time components.

    The way the CASE WHEN is written it should be producing records with memberships ending on 6/30/2017
    then come July, start producing membership records ending on 6/30/2018

    Why it's not producing any records is odd to me and just starting now that we're in 2017

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    17,017
    I'm stumped, without access to play. This returns the same 6/30/17 date, so nothing really changed with the new year:

    select CASE WHEN MONTH('2016/12/20') <= 7 THEN DATEFROMPARTS(YEAR('2016/12/20') , 6 , 30) ELSE DATEFROMPARTS(YEAR('2016/12/20') + 1 , 6 , 30) END
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  13. #13
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    401
    Thank you. Yea, STUMPED too. not sure what it could be ...

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, 09:56 AM
  2. Replies: 3
    Last Post: 10-27-2014, 06:37 AM
  3. Case for in case field corrupt
    By Ruegen in forum Forms
    Replies: 9
    Last Post: 08-03-2014, 06:56 PM
  4. Replies: 5
    Last Post: 10-23-2012, 02:55 PM
  5. Case Else
    By FinChase in forum Programming
    Replies: 3
    Last Post: 02-01-2012, 09:45 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
  •  
Tech Forums: Microsoft Office Forums