Forum Discussion

AddieBrunson's avatar
Qrew Member
3 years ago

Query Help


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

4 Replies

  • 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
      • PaulPeterson1's avatar
        Qrew Assistant Captain
        How is it updated?  Is it updated at the same time each month?

        Paul Peterson
  • DonLarson's avatar
    Qrew Commander

    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