Records between 2 dates

[ianmuir]

Hi, I have two fields Start_Date and End_date.

say a job starts 23/01/2010 and ends 30/01/2010

if I do a search 23/01/2010... 30/01/2010 it works ok.

if I do a search 24/01/2010... 30/01/2010 it does not show the record. I need to search from start_date to End_date and records that are between the 2 dates to be shown.

any ideas?

[efen]

That's because the record does not fit the search criterion? Are you searching both start and end date by one field?

In your search use one field for the start date and another for the end date.

[ianmuir]

The date fields are separate. Putting the 1st date in one and the second date in the other the doing a find shows no records in between.

[LaRetta]

Putting a date in each field will only tell FM that you want a record which matches exactly with same start date and same end date (it will not do a range).

If manual find, put >=22/1/2010 in start_date and <=31/1/2010 in end_date. If scripted, you can handle it like this (without requiring global date fields). Attach this script to FIND button:

Enter Find Mode [ pause ] ... so the User can put in their dates then they hit [enter[

If [ not start_date or not end_date ]

Show Custom Dialog [ OK ; "You must enter both start and end dates" ]

Enter Browse Mode

Exit Script

End If

Set Field [ start_date ; ">=" & start_date ]

Set Field [ end_date ; "<=" & end_date ]

Set Error Capture [ On ]

Perform Find

(put your error trapping process here)

UPDATE: If manual find, you can select :great: from Symbols while in Find Mode instead of typing >=. Same with script; same with <=. FM converts them properly.

Edited January 24, 2010 by Guest
Added script step

[comment]

Set Field [ start_date ; ">=" & start_date ]

Set Field [ end_date ; "<=" & end_date ]

That will only find records entirely contained within the searched range. To find all records that overlap the searched range, use:

StartDate ≤ RangeEnd

and

EndDate ≥ RangeStart

[LaRetta]

I see no RangeEnd or RangeStart. I thought it was wanted as: start_date & "..." and end_date. I guess I missed something!

[comment]

Well, you are searching for records within a given range, so you need to input that range somewhere. If you want to use the existing fields in Find mode, then (roughly):

Enter Find Mode [ Pause ]

Set Variable [ $start ; StartDate ]

Set Field [ StartDate ; "≤" & EndDate ]

Set Field [ EndDate ; "≥" & $start ]

Perform Find []

[LaRetta]

Using my script, if I have start_date (us) 1/3/2010 and end_date 3/15/2010, I get records of:

start - end

1/3/2010 - 3/15/2010

2/4/2010 - 2/17/2010

2/14/2010 - 3/14/2010

... because all of those records are within the range (meet both criteria). And I do not get 1/4/2010 - 4/4/2010. If I run your process, I get:

12/31/2009 - 1/14/2010

1/1/2010 - 4/11/2010

1/4/2010 - 4/4/2010 and also the found records above.

And I think I see what you mean. If every one of these dates (whether start or end) were in single date field, and we ran start_date & "..." & end_date, we'd get your results. It still puzzles me on what would be wanted here but now I see the difference in concepts. Thank you for explaining! :wink2:

[comment]

How about a simpler example? Say you are searching for jobs in February: your method will find jobs that start in February and end in February. My method will find those and also:

1. Jobs that start in January and end in February;

2. Jobs that start in February and end in March;

3. Jobs that start in January and end in March.

Unfortunately, OP's example is not clear enough to determine which one of the two he wants.

[ianmuir]

Thanks chaps, this one worked a treat!