Alpha DevCon 2018
Results 1 to 6 of 6

Thread: Lookup Predefined Date Range

  1. #1
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,319

    Default Lookup Predefined Date Range

    I noticed on the Web side there is a tool for doing this already: Whereby the user selects 'This Month' from a list and a date range is returned for startdate and enddate args that can then be used in a query. I would like to replicate this feature in a lookup on the desktop.

    Here is my lookup list with the return values (vRng) - another lookup list selects the year (vYear):
    <Show All>|0
    Today|T
    This Week|W
    Last Week|L
    January|1
    February|2
    March|3
    April|4
    May|5
    June|6
    July|7
    August|8
    September|9
    October|10
    November|11
    December|12
    1st Quarter|Q1
    2nd Quarter|Q2
    3rd Quarter|Q3
    4th Quarter|Q4

    My Select Case statement should be able to test vRng and get the start & end dates for vYear. So far I have the Quarters only:

    Code:
        case vRng = "Q1"
            q = date_quarterstartend(val(vYear),1)        
        case vRng = "Q2"
            q = date_quarterstartend(val(vYear),2)
        case vRng = "Q3"
            q = date_quarterstartend(val(vYear),3)
        case vRng = "Q4"
            q = date_quarterstartend(val(vYear),4)
    If vRng is Q4 and vYear is 2017 then I can get this to set my range:
    ?q.startdate
    ={10/01/2017}
    ?q.enddate
    ={12/31/2017}
    vBegDate = q.startdate
    vEndDate = q.enddate

    Any ideas on how to get the others - like for the weeks and months?

    Edit: found this for the months
    Code:
    case vRng = between(vRng,"1","12")
    	vBegdate = date_value(val(vYear),val(vRng),1)
    	vEnddate = month_end(vBegdate)
    Now for the weeks...
    Last edited by MoGrace; 12-17-2017 at 01:20 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,854

    Default Re: Lookup Predefined Date Range

    What is a "week"? Sunday through Saturday?

    ? date()-dow(date())+1
    = {12/17/2017}
    ? date()-dow(date())+7
    = {12/23/2017}
    There can be only one.

  3. #3
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,319

    Default Re: Lookup Predefined Date Range

    Well there goes 4 hours I could have spent better - thanks Stan! You always make it look simpler than it does to me...

    Then to get the previous week:

    ? (date()-dow(date())+1)-7
    = {12/10/2017}
    ? (date()-dow(date())+7)-7
    = {12/16/2017}

    Perfect!
    Last edited by MoGrace; 12-17-2017 at 06:01 PM.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  4. #4
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,319

    Default Re: Lookup Predefined Date Range

    Syntax error in this code for the month - note that Between() is either T or F ...:
    Code:
    case vRng = between(vRng,"1","12")
    vBegdate = date_value(val(vYear),val(vRng),1)
    vEnddate = month_end(vBegdate)
    But Between() will not find numbers entered as characters (returns .F.) - but this works:

    Code:
    case between(val(vRng),1,12)
    vBegdate = date_value(val(vYear),val(vRng),1)
    vEnddate = month_end(vBegdate)
    And I needed to move the case statement to be before the others.
    Last edited by MoGrace; 12-17-2017 at 07:09 PM. Reason: still wrong...
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,854

    Default Re: Lookup Predefined Date Range

    case vRng = between(vRng,"1","12")
    Shouldn't that be

    case between(vRng,"1","12")

    Code:
    But Between() will not find numbers entered as characters (returns .F.)
    It will but the usual left to right character comparison affects the outcome

    vrng = "06"
    ? between(vrng,"01","12")
    = .T.
    There can be only one.

  6. #6
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,319

    Default Re: Lookup Predefined Date Range

    normally when I use numbers as characters I do use padL(), but in this case val(vRng) works just fine.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

Similar Threads

  1. Can't get value of from date in date range search
    By frankbicknell in forum Application Server Version 11 - Web/Browser Applications
    Replies: 3
    Last Post: 10-14-2012, 06:09 PM
  2. Date Range Fields in Search doesnt respect Grid Properties Date Format or Textbox For
    By aburningflame in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 04-25-2011, 12:38 PM
  3. Date Range (Non-Programmer)
    By SailF20 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 5
    Last Post: 11-04-2010, 04:18 PM
  4. Date - Predefined display formatting
    By catplus in forum Alpha Five Version 7
    Replies: 8
    Last Post: 06-05-2006, 07:20 AM
  5. Lookup on Predefined Table
    By Roy Irby in forum Alpha Five Version 5
    Replies: 0
    Last Post: 04-25-2003, 01:35 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
  •