Birthdate filter

Moderator: Rathinagiri

User avatar
RussBaker
Posts: 51
Joined: Wed Jul 22, 2015 9:44 am

Birthdate filter

Post by RussBaker »

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
User avatar
CalScot
Posts: 303
Joined: Thu Mar 21, 2013 12:22 am
Location: California

Re: Birthdate filter

Post by CalScot »

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!
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: Birthdate filter

Post by Rathinagiri »

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) ) )
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: Birthdate filter

Post by mol »

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)
User avatar
RussBaker
Posts: 51
Joined: Wed Jul 22, 2015 9:44 am

Re: Birthdate filter

Post by RussBaker »

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.
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: Birthdate filter

Post by Rathinagiri »

Have you tried to use my condition?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
RussBaker
Posts: 51
Joined: Wed Jul 22, 2015 9:44 am

Re: Birthdate filter

Post by RussBaker »

Rathinagiri wrote:Have you tried to use my condition?
Yes. :D
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
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: Birthdate filter

Post by Rathinagiri »

Thank you for your kind words Russ.

Instead of 'onchange' you can use 'onlostfocus'.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
esgici
Posts: 4543
Joined: Wed Jul 30, 2008 9:17 pm
DBs Used: DBF
Location: iskenderun / Turkiye
Contact:

Re: Birthdate filter

Post by esgici »

RussBaker wrote:I can't seem to think of a simple birthdate filter for my .dbf file.
Did you tried DATECALC() or FT_ELAPSED()
Viva INTERNATIONAL HMG :D
User avatar
quartz565
Posts: 667
Joined: Mon Oct 01, 2012 12:37 pm
Location: Thessaloniki, Greece
Contact:

Re: Birthdate filter

Post by quartz565 »

You're an open library of hmg, my friend !!
vivaclipper... :D
Best Regards,
Nikos.

os: Windows Server 2019 - 64
Post Reply