Query Help

Question

Hello,&nbsp;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------------------------------

paulpeterson2 · Answer

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
------------------------------

addiebrunson · Answer

Hi Paul,&nbsp;

No. It updates once per month.&nbsp;

Thanks!

paulpeterson1 · Answer

How is it updated?&nbsp; Is it updated at the same time each month?------------------------------Paul Peterson------------------------------

donlarson · Answer

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.'" &amp; [Health Date]&amp; "'' AND {FID_Health_Date.LTE. '"&amp; [Health Date] &amp;"''}";

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

The use the Color variable in your report.------------------------------Don Larson------------------------------

Forum Discussion

Query Help