HMGforum.com

I can't seem to think of a simple birthdate filter for my .dbf file.

I need to set a filter so I can send out birthday notices.
I have two form fields
Form1.begdate
Form1.endate

The problem is when I need to have a date window at the end of the year.
eg: 12/1/2015 - 01/1/2016

Some filters fail me

SET FILTER TO month(customer->birthday) >= month(Form1.begdate.value) .and. day(customer->birthday) >= day(Form1.begdate.value) ;
and month(customer->birthday) <= month(Form1.endate.value) .and. day(customer->birthday) <= >= day(Form1.enddate.value)

What am I doing wrong here? Surely there is a simpler method.

--Russ

I think I'd get around it by writing a UDF to convert the birthday and the Form1 dates to 20151201 / 20160101 format, which should make the filter much simpler/cleaner. Hope that helps!

Please Use this...

SET FILTER TO if( year( form1.begdate.value ) == year( form1.enddate.value ), month(customer->birthday) >= month(Form1.begdate.value) .and. day(customer->birthday) >= day(Form1.begdate.value) ;
and month(customer->birthday) <= month(Form1.endate.value) .and. day(customer->birthday) <= day(Form1.enddate.value), (month(customer->birthday) >= month(Form1.begdate.value) .and. day(customer->birthday) >= day(Form1.begdate.value)) or ( month(customer->birthday) <= month(Form1.endate.value) .and. day(customer->birthday) <= day(Form1.enddate.value) ) )

Dind't you try to use this phrase:

I did write a UDF to convert the birthday year to current. This makes the year the same as my comparison. However, it doesn't work NEXT year when my end date is in January.

My goal was to make the filter simple and fast to execute. HMG is MUCH faster than clipper was.

mol wrote:Dind't you try to use this phrase:

Code: Select all

Set filter to dtos(customer)->birthday >= dtos(Form1.begdate.value) .and. to dtos(customer)->birthday <= dtos(Form1.enddate.value)

This might be a great idea. I could ignore the first 4 bytes of DTOS, the year and compare only month/day.
eg: If SUBS(DTOS(birthday),5,4) >= SUBS(DTOS(Form1.begdate.value),5,4)
It still fails when I'm comparing December 12 to January 01 birthdates.

Have you tried to use my condition?

Rathinagiri wrote:Have you tried to use my condition?

Yes.
Your filter works perfectly! I was afraid such a long condition would slow down the execution, however it wasn't noticeably slower.

My fear was that my form DATEPICKERr has
ONCHANGE bdaycount()
Where bdaycount() is a function that sets the filter and displays the number of matching records. The table has 70,000 records and would take some time to perform the count. With your filter, It takes approximately 2 seconds which does not make the user wait too long.

Excellent! I thank you sir. Very much appreciated.
I couldn't get my head to write such a long condition.

This forum is excellent! I have been coding in Xbase for years and never had such a great place to come and get such great help.
I'm new to HMG and am loving this. Not only does my code get new life, it seems to execute so much faster.

--Russ

Thank you for your kind words Russ.

Instead of 'onchange' you can use 'onlostfocus'.

RussBaker wrote:I can't seem to think of a simple birthdate filter for my .dbf file.

Did you tried DATECALC() or FT_ELAPSED()

You're an open library of hmg, my friend !!
vivaclipper...