FINDING a DATE RANGE misses the last day

[RPM765]

I'm using Global fields to accept a start date and an end date for selecting a range of dates for printed reports. The scripted find request uses this syntax:

Enter Find Mode

Set field DisplayDate (to this result:)

GetAsText(MAIN TABLE::G_DateStart & "..." & (MAIN TABLE::G_DateEnd))

The find works, however the found set never includes the last date. For example, if I enter "01/01/07" as the start date and "01/31/07" as the end date, I get records through 01/30/07, but NOT the records generated on 1/31/07. This same pattern holds true no matter what the end date may be.

I have tried adding a "+ 1" in the calculation like this:

GetAsText(MAIN TABLE::G_DateStart & "..." & (MAIN TABLE::G_DateEnd [color:red]+ 1))

but with this code, the find does not work at all and returns all the records all the time.

What am I doing wrong and how to I get the last date included in the find?

[mr_vodka]

Date ( Month (MAIN TABLE::G_DateEnd ) ; Day (MAIN TABLE::G_DateEnd ) + 1; Year (MAIN TABLE::G_DateEnd ) )

[Lee Smith]

The find should work without adding a date. The ... represents the from and through of the range.

I thought that the Set Field Function was change in v7 so that you could do date ranges,

Set Field [ [color:red]DisplayDate; MAIN TABLE::G_DateStart & "..." & MAIN TABLE::G_DateEnd ]

if not, try

Insert Calculated Result [ [color:red]DisplayDate; MAIN TABLE::G_DateStart & "..." & MAIN TABLE::G_DateEnd ]

[ Select ]

Assuming the Date Field being searched is [color:red]DisplayDate

HTH

Lee

[comment]

Did you patch your version up to 7.0v3?

[The Shadow]

Is the field really a date, or is it a timestamp??

[RPM765]

Thanks for your help so far. I'll answer a few questions.

Mr. Vodka, I tried your suggestion, however the Find did not work at all with that code change. I suspect it is because of the way I have handled this "date" field. Read on, please.

Yes, I have updated to 7.0v3. I also just ordered the 8.5 upgrade. How will that change this situation?

The global date start and date end fields are text fields, not dates or timestamps. The date data actually imports into Filemaker formatted like this: 20070216. I parse that data into separate Month, Day and Year text fields, then combine them so I can display that same date in a more "readable" way, as in 02/16/2007. It is this DisplayDate calculation text field that I am using for the global start and end dates. I suspect that my apoproach is at fault, but I don't know what to do to change it.

[Lee Smith]

Your global fields need to be Dates

Lee

[comment]

Oh, no - and you didn't consider this important enough to include in your initial post?

Well, it's no use trying to analyze the situation, because the easy solution is to convert the imported data into REAL Filemaker dates. I will only make a few short notes:

1. "some text" + 1 is a rather meaningless expression. Filemaker is actually a good sport, and tries to make something out of it, so given "1/31/07" + 1 it will return "13108";

2. Text sorts differently than numbers, and this of course affects range finds;

3. When a scripted find does not produce the expected results, it is good practice to insert a pause just before performing the find, and inspect the actual find criteria.

Now, since you already know how to extract the various elements from the imported data, just change your calculation field result type to Date, and use the Date() function to put those elements back together into a valid date. Then change your global fields type into Date as well, and use:

Enter Find Mode

Set Field [ DisplayDate ; MAIN TABLE::G_DateStart & "..." & MAIN TABLE::G_DateEnd ]

[Lee Smith]

How does your calculation differ from the one I posted 5 post ago?

[comment]

It doesn't. Do you think it would be more helpful if I wrote: "... and then use the calculation posted by Lee 5 posts ago - I mean the first one, not the second one"?

[RPM765]

PROBLEM SOLVED! This forum is amazing.

Changing my Global DateStart and DateEnd fields to DATE fields solved the problem.

Thanks to all of you who replied...and so quickly, too!

Comment, I didn't include the field format information in my initial post because...(now go and read the tag line under my signature!)

[comment]

Changing my Global DateStart and DateEnd fields to DATE fields solved the problem.

If that's ALL you did, then I guarantee you the problem is not solved.

Edited February 16, 2007 by Guest