I am having problems with what appears to be a simple task but it's taking me forever to solve it. I am trying to find records on a relatively small database (~800 records) that fall within a date range. I have a conference date field and will like to find all those records that have a conference date occurring within a date range, for example 01/03/3002 to 31/03/2002. Manually, I just type 01/03/2002...31/03/2002 in the conference date field and there they are, but the user will like to search for any date range so I thought a script would be a good idea. So far I haven't been able to do this. Recently I set up two global fields (start date and end date) and used

Enter Find Mode[ ]

Set Field [conference date, "start date & "..." & end date"]

Perform Find[ ]

and all I got were records that had a conference date = start date only. I have also tried Insert Calculated Result, a 'no records match this request' message is displayed. Please help as I'm pulling my hair out.

You can't use set field to enter that result. The reason is because the field you are trying to Set is a Date, the result of "start date & "..." & end date" is not a single valid result.

Use the "Insert Calculated Result" script step and change your calculation to:

DateToText(start date) & "..." & DateToText(end date)

What I have done in one of my applications was to have a calculation field of the two dates called DateRange then copy and paste the result into the conference date field when in Find mode. You need to use DateToText for the find to work.

DateRange = If(End Date = "", DateToText(Start Date), DateToText(Start Date) & "..." & DateToText(End Date))

The reason I use the IF statement is for the user who doesn't fill in an end date. I also hide the DateRange field from the user by setting the text color to white. You still see the outline of the field in Layout mode, but not in Browse or Find mode.

HTH

Mike

Finding a Range of Dates Using ScriptMaker, Article: 104055

http://www.filemaker.com/ti/104055.html