Date Range in a find

[violet13]

Hi, I am new here, and I've poured over the forums but haven't found a solution that works for my particular database structure. I currently have a event registration database.

I have two fields called Start Date and End Date. I've created a display field that strings them together (i.e. "6/28/2010-7/5/2010") using a basic calculation. Note that this field displays as text in order for this to work.

I'd like the user to be able to search for all events that either occur on a given date, or occur within a date range. I've tried setting up global fields, and I've tried writing a script that allows one to search the Start Date and End Date range.

What the user needs is to retrieve a found set of events that are occurring concurrently...the search above ("6/28/2010-7/5/2010") should retrieve any event that falls on either side of the dates searched, for example, a seminar that starts on 6/21/2010 and ends on 6/28/2010. In addition to events that fall completely within the dates being searched.

Many of the solutions discussed in the forum come close to solving this, but ultimately didn't retrieve all existing records in the range. This is certainly due to my idiosyncratic database design & attempting to search a range across two fields incorrectly. Any help would be greatly appreciated. I'm STUMPED!

[jdu98a]

I would start by creating two new fields: StartDate_search and EndDate_search. Make these fields global. You can place the fields on the regular layout or have them on another layout that the user navigates to; however you prefer it.

Without entering find mode the user will enter the desired date range in these two search fields. Then provide a button that will execute a script that will do the following:

[color:gray]

Show All Records

[color:gray]Go To Record [First]

Loop

[color:gray]IF (StartDate_search >= Start Date and StartDate_search <= End Date)

[color:gray]Go To Record [Next; exit after last]

[color:gray]Else IF (EndDate_search >= Start Date and EndDate_search <= End Date)

[color:gray]Go To Record [Next; exit after last]

[color:gray]Else

[color:gray]Omit Record [current]

End Loop

After this script is run perhaps have the resulting found set show up in some kind of list view. I believe this should produce the results you are looking for.

[comment]

See if this helps:

http://fmforums.com/forum/showtopic.php?tid/213048/post/349103/#349103

[violet13]

Thanks so much, jdu98a.

This makes sense to me, but I've tried it,

and received an "Invalid script step" warning.

I believe that maybe there needs to be an "End If" step before the "End Loop"?

Also, for the "Omit Record" step, I was unable

to specify Current as per your directions.

Even with adding the speculative "End IF" step,

then entering dates into those new fields (not within find mode),

the script hung up for a long time before

I finally hit the command key to exit.

Any thoughts?

Perhaps I missed something...

[violet13]

Thank you comment. I studied this post

and tried the solution before I posted,

but it didn't work for me. I had the

same question that LaRetta had: where

do the fields RangeEnd and RangeStart

come from? Then, when I used your suggested script:

Enter Find Mode [ Pause ]

Set Variable [ $start ; StartDate ]

Set Field [ StartDate ; "≤" & EndDate ]

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

Perform Find []

But it only pulled out 4 records that matched

exactly the start dates and end dates that

I entered. I have never worked with setting a

local variable, so perhaps I set that up incorrectly. (?)

WAIT! It worked. The problem was entirely on my

end...my StartDate and EndDates were not set to

Date (were set to Text)...because I am an

IDIOT You don't know how long I've

been stuck on this...!

Thank you so much for posting this and re-directing me to it.

[jdu98a]

You are correct that there should be an "End If" I was just writing out the script off the top of my head and forgot it, my apologies. Here is the corrected script:

[color:gray]

Show All Records

[color:gray]Go To Record [First]

Loop

[color:gray]IF (StartDate_search >= Start Date and StartDate_search <= End Date)

[color:gray]Go To Record [Next; exit after last]

[color:gray]Else IF (EndDate_search >= Start Date and EndDate_search <= End Date)

[color:gray]Go To Record [Next; exit after last]

[color:gray]Else

[color:gray]Omit Record

[color:gray]End If

End Loop

The "current" that I included originally was more just a note, there is no parameter. As far as the loop hanging for a long time, this would depend greatly on the number of records that you are working with. It was simply a guess on my part that when dealing with something like an event calendar it was not likely that there would be more than 300-500 records. It this case a script like this one will finish fairly quickly. If this is not the case and you are going through several thousand records it may take longer.

haha, I just read your response to the second suggestion. Not having your date fields set to "date" would not allow my script to judge any < or > criteria. So, the result would be a forever-looping script. You're not an idiot. I can't tell you how many times I've been pulling my hair out wondering why a global field was not passing correct information only to find out that I had never actually set the field to be global!

Edited April 22, 2010 by Guest