Date range overlap - different angle

[Greg Hains]

Hi,

Once again I seek the expertise of this forum to prevent driving myself mad. Last year, Comment assisted me with a formula to achieve this same result but it was in the contact of an If/EndIf in a loop - Omitting records. I need to achieve this same thing but in Find mode.

A table with four fields relating to a medical treatment environment:
- Episodes:DateOpened  (date field to record when a client was admitted)
- Episodes:DateClosed   (date field to record when a client was discharged. If this field is blank, then the client is still in the clinic)
- Episodes:SearchDateFrom   (global date field for the beginning of the search window)
- Episodes:SearchDateTo   (global date field for the end of that search window - used for date ranges)

If I only use Episodes:SearchDateFrom then I’m only looking at a ‘single date’.
If I use Episodes:SearchDateTo as well, then I’m checking a date range.

I want to check if the client’s stay falls on the single date or within the date range through a Set Field in Find Mode.
My current attempts are clumsy and ugly so I won’t embarrass myself by printing them here. I’m sure there is a logical (literally) and clean method to do this.

Any help would be appreciated.
Thank you.
Greg

Edited April 11, 2016 by Greg Hains
Posted before complete. My error.

[comment]

Try:

Enter Find Mode[]
Set Field [ Episodes::DateOpened; "≤" & Episodes::gSearchDateTo ]
Set Field [ Episodes::DateClosed; "≥" & Episodes::gSearchDateFrom ]
Perform Find[]

To cover the possibility of gSearchDateTo field being empty, use:

If ( Episodes::gSearchDateTo ; Episodes::gSearchDateTo ; gSearchDateFrom )

instead of Episodes::gSearchDateTo in the first Set Field[] step.

[Greg Hains]

Thank you again Comment.   Thats great. So simple.