View Only
  • 1.  Query Help

    Posted 05-03-2022 15:16

    I have a table with the following fields:
    • Program Name (Type: Text)
    • Health Date (Type: Date)
    • Health Color (Type: Text)
    I would like to add a field that would automatically return the health color from the previous month. The desired end result would look something like:

    I'm guessing this would be a query formula but so far I haven't had any luck. Any help is much appreciated! Thank you.

    Addie Brunson

  • 2.  RE: Query Help

    Posted 05-06-2022 19:45
    I may be able to help, but would like a little more info about the health color updates.  Does it update more than once a month?

    Paul Peterson

  • 3.  RE: Query Help

    Posted 05-06-2022 20:10
    Edited by Addie Brunson 05-06-2022 20:11
    Hi Paul, 

    No. It updates once per month. 


  • 4.  RE: Query Help

    Posted 05-08-2022 21:54
    How is it updated?  Is it updated at the same time each month?

    Paul Peterson

  • 5.  RE: Query Help

    Posted 05-09-2022 10:09

    As long as you never duplicate a Health Date for a Program, this should be straightforward.

    Your query would look something like

    var text query ="{FID_Health_Date.GTE.'" & [Health Date]& "'' AND {FID_Health_Date.LTE. '"& [Health Date] &"''}";

    var textlist  Color= GetFieldValues( GetRecords( $query, [_Table_Name] ) , FID_Health_Color );

    The use the Color variable in your report.

    Don Larson