Jump to content

FINDING a DATE RANGE misses the last day


This topic is 5701 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ]

Link to comment
Share on other sites

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!)

Link to comment
Share on other sites

This topic is 5701 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.