Jump to content
Sign in to follow this  
alex005

Search within Dates

Recommended Posts

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 ...

Share this post


Link to post
Share on other sites

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 by Guest
Added explanation

Share this post


Link to post
Share on other sites

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

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.

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

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 by Guest
Re-worded a bit

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

))

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. :wink2:

LaREtta

Share this post


Link to post
Share on other sites

I merged your two threads.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 ]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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[].

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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