Birthdate filter
Moderator: Rathinagiri
Birthdate filter
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 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
Re: Birthdate filter
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!
- 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
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) ) )
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.
South or North HMG is worth.
...the possibilities are endless.
Re: Birthdate filter
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)
Re: Birthdate filter
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.
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.
My goal was to make the filter simple and fast to execute. HMG is MUCH faster than clipper was.
This might be a great idea. I could ignore the first 4 bytes of DTOS, the year and compare only month/day.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)
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.
- 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
Have you tried to use my condition?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
Re: Birthdate filter
Yes.Rathinagiri wrote:Have you tried to use my condition?
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
- 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
Thank you for your kind words Russ.
Instead of 'onchange' you can use 'onlostfocus'.
Instead of 'onchange' you can use 'onlostfocus'.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
- esgici
- Posts: 4543
- Joined: Wed Jul 30, 2008 9:17 pm
- DBs Used: DBF
- Location: iskenderun / Turkiye
- Contact:
Re: Birthdate filter
Did you tried DATECALC() or FT_ELAPSED()RussBaker wrote:I can't seem to think of a simple birthdate filter for my .dbf file.
Viva INTERNATIONAL HMG
Re: Birthdate filter
You're an open library of hmg, my friend !!
vivaclipper...
vivaclipper...
Best Regards,
Nikos.
os: Windows Server 2019 - 64
Nikos.
os: Windows Server 2019 - 64