Skip to main content
Question

Query Help

  • May 3, 2022
  • 0 replies
  • 41 views
Hello, 

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
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+2
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
------------------------------
Forum|alt.badge.img+15
  • DonLarson
  • Registered
  • May 9, 2022
Addie,

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
------------------------------
Terms & ConditionsAccessibility statement