Search within Dates

[alex005]

Hi,

Can someone please help a real green person perform this function.

I'd like to be able to, select two dates and then at the press of a button send the results of a search to a layout that will be able to produce a letter that is only for customers whos birthdays are within the two dates.

I've tried looking at other threads and kind of get them, but when I apply thier logic to my DB, it all goes wrong, I get confused, it goes more wrong and then in my heightened sense of confusion I find the delete DB button, and then I have to start over again. I've made some real progress this time, principly by avoiding this issue, and now I can't avoid it.

I'll bet someone has the answer, and in less characters than I've used on this post ...

[LaRetta]

Hi Alex, welcome to FM Forums!

You will need to be careful searching for date ranges when looking for birthdays. Because if you use wild cards for the year, you will get all dates*. Here's an example of what will work no matter the range requested. Note that I created a generic date to be used for searching. This field is NOT based upon the current date so it can also be indexed (and can be searched quickly).

It won't matter what year your User enters but it MUST be a valid date for it to work. You could also use theory of month/day only and search such as:

3/{1..15}/*

... but this wouldn't allow a specified range (crossing months) as it seems that you've requested.

* Explanation of 'all dates' ... if you search for 3/1/*..4/15/*, you will get all dates because the first * year could be 1900 and the second could be 2008 and that means all records in between. I didn't explain it very well but the year must be 'normalized' in some way to restrict the span.

FindBirthdays.zip

Edited December 29, 2008 by Guest
Added explanation

[LaRetta]

Ahem, it is probably wiser to use Year(4) within that cGenericDate calculation.

I gleaned the theory of normalizing dates from Comment (in many instances in several posts throughout the years). But I just wondered what would happen if someone was born on 2/29/2004 (besides being very young, ha ha). Their 'generic' birth date (if using Year 1) would become 3/1 (which is also correct, but may not be the best choice here because their birthday wouldn't be found if it was actually listed as 2/29).

I 'think' Comment used year 4 in a similar situation from few years ago but, after hunting for 2 hours, I can't find the post. I believe that using a year which is leap year would offer a bit more protection in this instance because, if birthday is 2/29 it will reflect as 2/29/0004 but those without birthdays on 2/29 can still be found when searching for all dates as well.

Alex, if this isn't what you need, please provide a bit more information and we can nail down what you want.

[comment]

I 'think' Comment used year 4 in a similar situation from few years ago but, after hunting for 2 hours, I can't find the post.

This one?

http://fmforums.com/forum/showtopic.php?tid/182474/

[LaRetta]

YES!! Thank you!

Problem is ... if a range is used, and if the User entered 2/1..2/29, it still might break if the current year isn't a leap year because of my IsValid() but even if it didn't exist, field-level validation should fail.

STRANGE ... field-level validation does NOT fail if field is set via script. In my example, if you disable the If[] to test the validity of the dates, and enter the gTo as 2/29/2007, it accepts it but (using debugger) it produces 2/1/0004...? and won't find any records.

Edited December 30, 2008 by Guest
Re-worded a bit

[alex005]

LaRetta,

Many thanks for the Birthday.zip. It's kind of what I'm looking for, I like the "find birthdays" button, but what I'd like to happen after that is the Date from to be auto infilled with the date for the day that the search is being run on, and the date from to be auto infilled with a date 7 days in advance with the option of being to change that date should the user wish to.

I see you have done alot of work on here from the amount of times I've come across your posts, keep up the good work.

[alex005]

Hi,

What I'm trying to do is find the records that have a date of birth between two dates. The custom script below is supposed to return a number that relates to how many days until the birthday of the record. But it doesn't work, it says I need an operator inbetween the £ sign in the code.

Case(

IsEmpty ( StartDate ) or IsEmpty ( EndDate ) ; "Please fill in both fields." ;

StartDate > EndDate ; "Start date must be earlier than end date." ;

( EndDate - StartDate ) > ( RangeLimit * 365 ) ; "Date range has exceeded the limit of " & RangeLimit & " years." ;

Case ( EndDate > StartDate ;

DateRange £(£ StartDate; EndDate - 1; RangeLimit) & "¶" & EndDate ; EndDate

))

[comment]

1. This is not a custom script, but a custom function.

2. It will probably work, if you just remove the £ characters.

3. AFAICT, it is designed to return a LIST of dates between StartDate and EndDate, not how many days there are in between them.

This seems to be a part of the same issue as your other thread:

http://www.fmforums.com/forum/showtopic.php?tid/200209/

and would be best resolved there.

[alex005]

This is pasted from my other post ...

Hi,

What I'm trying to do is find the records that have a date of birth between two dates. The custom script below is supposed to return a number that relates to how many days until the birthday of the record. But it doesn't work, it says I need an operator inbetween the £ sign in the code.

Case(

IsEmpty ( StartDate ) or IsEmpty ( EndDate ) ; "Please fill in both fields." ;

StartDate > EndDate ; "Start date must be earlier than end date." ;

( EndDate - StartDate ) > ( RangeLimit * 365 ) ; "Date range has exceeded the limit of " & RangeLimit & " years." ;

Case ( EndDate > StartDate ;

DateRange £(£ StartDate; EndDate - 1; RangeLimit) & "¶" & EndDate ; EndDate

))

The problem is that with the ( that is inbetween the ££ when I click the OK button to accept the code FM tells me that it expects something else.

[LaRetta]

I'd like to be able to, select two dates and then at the press of a button send the results of a search

Then simply prefill the dates at the beginning of the script I provided so that, when the custom dialog appears, it appears with the dates you wish. Then the User can change those dates accordingly.

So the gFrom would be set with [color:blue]Get ( CurrentDate) and the gTo would be set with [color:blue]Get ( CurrentDate ) + 7.

DateRange() is a custom function. Are you aware of that? It will return a multiline of dates and can't be used in a search. I have absolutely no idea why you are going with a 'custom script' such as this one. It isn't even close to what I suggested. It appears that you have searched this forum and put together bits and pieces of others' work to try to come up with what you want.

LaREtta

[Lee Smith]

I merged your two threads.

[alex005]

Thats great, but what do you mean. I get that rather than the user fill in the dates the code will get the info from the GET command but I don't get where to put that code, or how.

[LaRetta]

In the script, at the beginning before "Ask User for date range", add these two lines:

Set Field [ gStart ; Get ( CurrentDate ) ]

Set Field [ gEnd ; Get ( CurrentDate ) + 7 ]

[alex005]

I've tried putting those two lines in and I can get the first line to read Set Field but when I put the rest of the line in (where the specify button is) I then get either a ( is expected here or a operator is expected here, and it then highlights either the begining of the line or the last chunk of code.

[LaRetta]

Set Field [ gStart ; Get ( CurrentDate ) ]

Set Field [ gEnd ; Get ( CurrentDate ) + 7 ]

Only the [color:blue]Get ( CurrentDate ) goes in in the calculation portion of the FIRST Set Field[]. And only the [color:blue]Get ( CurrentDate ) + 7 goes in the calculation portion for the SECOND Set Field[].