RE: How do I get number of birthdays?
Why not just add subtract one day from the oldest date, then add one day to the newest date, THEN do a BETWEEN? !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Friday, November 24, 2006 2:42 PM To: CF-Talk Subject: How do I get number of birthdays? Hi, all.yes, some of us are working today. I'm trying to output client info for clients who have birthdates between two designated months and days.not years. For example, I need to return all clients who have a birthday including and between Nov 27 and Dec 3, no matter what year. I tried where Month(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Day(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Month (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and Day (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and that code works (MySQL) when the birthdates fall in the same month, such as Nov 5 through Nov 12, but when the birthdates span two months, such as Nov 27 through Dec 3, the query returns no results. using between has been suggested, but I need to include birthdates on the dates specified, not just between them, and if I'm not mistaken, between would only return dates between the specified dates. Suggestions? Thanks, Rick PS - MySQL and CF 4.5 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261708 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How do I get number of birthdays?
Use a date picker and get the full start date and full end date instead of just doing a select box for months and days... It's easier to work with dates instead of what you're doing... On 11/27/06, Andy Matthews [EMAIL PROTECTED] wrote: Why not just add subtract one day from the oldest date, then add one day to the newest date, THEN do a BETWEEN? !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Friday, November 24, 2006 2:42 PM To: CF-Talk Subject: How do I get number of birthdays? Hi, all.yes, some of us are working today. I'm trying to output client info for clients who have birthdates between two designated months and days.not years. For example, I need to return all clients who have a birthday including and between Nov 27 and Dec 3, no matter what year. I tried where Month(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Day(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Month (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and Day (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and that code works (MySQL) when the birthdates fall in the same month, such as Nov 5 through Nov 12, but when the birthdates span two months, such as Nov 27 through Dec 3, the query returns no results. using between has been suggested, but I need to include birthdates on the dates specified, not just between them, and if I'm not mistaken, between would only return dates between the specified dates. Suggestions? Thanks, Rick PS - MySQL and CF 4.5 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261720 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How do I get number of birthdays?
Hi, Greg... You're right...and I may just do that since someone in the jQuery community just created a new one that I saw this weekend. Thanks for the tip! Rick -Original Message- From: Greg Morphis [mailto:[EMAIL PROTECTED] Sent: Monday, November 27, 2006 9:41 AM To: CF-Talk Subject: Re: How do I get number of birthdays? Use a date picker and get the full start date and full end date instead of just doing a select box for months and days... It's easier to work with dates instead of what you're doing... ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261724 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: How do I get number of birthdays?
Thanks, Andy...that was going to be my next approach. Jim's solution took care of it for me, however... it took care of the need to include the dates the user specified, plus crossing over between years. Rick -Original Message- From: Andy Matthews [mailto:[EMAIL PROTECTED] Sent: Monday, November 27, 2006 9:20 AM To: CF-Talk Subject: RE: How do I get number of birthdays? Why not just add subtract one day from the oldest date, then add one day to the newest date, THEN do a BETWEEN? !//-- andy matthews web developer certified advanced coldfusion programmer ICGLink, Inc. [EMAIL PROTECTED] 615.370.1530 x737 --//- ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261725 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How do I get number of birthdays?
Man... do I feel like a complete idiot.. I think I should no longer consider myself even a beginner CF programmer! Jim, somewhere along the line I got the idea that the default values for Form.Earliest_Birthdate_Day was Day... it's not... it's 0! The same is true for the other birthdate form values... all 0... what a dummy! I don't even remember changing anything on the form page! AAAHHH! I don't feel better yet... it'll take a week to get over this one. But at least my world makes sense again... talk about self-inflicted pain! Rick -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Saturday, November 25, 2006 9:57 PM To: CF-Talk Subject: Re: How do I get number of birthdays? Rick, See above...I added in parentheses to group the birthdate stuff, so that it is evaluated together. The way you had it before you could have possibly had all (or any) of the parameters evaluate to false, but if the earliest month was greater than the latest month, that OR would have made it so records would be returned if that last part about the latest birthdate was true. Hopefully that makes sense...it's kind of hard to explain without pointing at it. Anyway, that is the only thing I can think of that would cause what you are describing. -jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261691 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: How do I get number of birthdays?
Jim... (or anyone else)... Do you see anything wrong with this code (part of a larger query) that would cause the query not to return no results if the following formfields, Form.Earliest_Birthdate_Day and Form.Earliest_Birthdate_Month and Form.Latest_Birthdate_Day and Form.Latest_Birthdate_Month are *not* used in the form (in other words, they CFIF at the beginning of this part of the query should exclude this section from consideration in the query). Here's the code: CFIF Form.Earliest_Birthdate_Day is not Day and Form.Earliest_Birthdate_Month is not Month and Form.Latest_Birthdate_Day is not Day and Form.Latest_Birthdate_Month is not Month and ((Month(Client_Birthdate) = #Form.Earliest_Birthdate_Month# and Day(Client_Birthdate) = #Form.Earliest_Birthdate_Day#) orMonth(Client_Birthdate) #Form.Earliest_Birthdate_Month#) CFIF Form.Earliest_Birthdate_Month GT Form.Latest_Birthdate_Month !---This takes care of cases where the months go over a new year--- or CFELSE and /CFIF ((Month(Client_Birthdate) = #Form.Latest_Birthdate_Month# andDay(Client_Birthdate) = #Form.Latest_Birthdate_Day#) orMonth(Client_Birthdate) #Form.Latest_Birthdate_Month#) /CFIF Thanks, Rick PS - This was working fine yesterday, then today it just started returning no results if the formfields Earliest_Birthdate_Day, Earliest_Birthdate_Month, Latest_Birthdate_Day, and Latest_Birthdate_Month are not used. ??? -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Friday, November 24, 2006 4:22 PM To: CF-Talk Subject: Re: How do I get number of birthdays? I think this will work...check the logic, though. WHERE ((Month(Client_Birth_Date) = #Form.Earliest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Earliest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Earliest_Birth_Month#) cfif Form.Earliest_Birth_Month GT Form.Latest_Birth_Month !---This takes care of cases where the months go over a new year--- OR cfelse AND /cfif ((Month(Client_Birth_Date) = #Form.Latest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Latest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Latest_Birth_Month#) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261659 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How do I get number of birthdays?
It looks to me like Day is a variable but you are comparing the value of #Form.Earliest_Birthdate_Day# to the actual WORD Day instead of the value of a variable named Day. Try this instead CFIF Form.Earliest_Birthdate_Day is not Day and Form.Earliest_Birthdate_Month is not Month and Form.Latest_Birthdate_Day is not Day and Form.Latest_Birthdate_Month is not Month ..:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com -Original Message- From: Rick Faircloth [mailto:[EMAIL PROTECTED] Sent: Saturday, November 25, 2006 1:16 PM To: CF-Talk Subject: RE: How do I get number of birthdays? Jim... (or anyone else)... Do you see anything wrong with this code (part of a larger query) that would cause the query not to return no results if the following formfields, Form.Earliest_Birthdate_Day and Form.Earliest_Birthdate_Month and Form.Latest_Birthdate_Day and Form.Latest_Birthdate_Month are *not* used in the form (in other words, they CFIF at the beginning of this part of the query should exclude this section from consideration in the query). Here's the code: CFIF Form.Earliest_Birthdate_Day is not Day and Form.Earliest_Birthdate_Month is not Month and Form.Latest_Birthdate_Day is not Day and Form.Latest_Birthdate_Month is not Month and ((Month(Client_Birthdate) = #Form.Earliest_Birthdate_Month# and Day(Client_Birthdate) = #Form.Earliest_Birthdate_Day#) orMonth(Client_Birthdate) #Form.Earliest_Birthdate_Month#) CFIF Form.Earliest_Birthdate_Month GT Form.Latest_Birthdate_Month !---This takes care of cases where the months go over a new year--- or CFELSE and /CFIF ((Month(Client_Birthdate) = #Form.Latest_Birthdate_Month# andDay(Client_Birthdate) = #Form.Latest_Birthdate_Day#) orMonth(Client_Birthdate) #Form.Latest_Birthdate_Month#) /CFIF Thanks, Rick PS - This was working fine yesterday, then today it just started returning no results if the formfields Earliest_Birthdate_Day, Earliest_Birthdate_Month, Latest_Birthdate_Day, and Latest_Birthdate_Month are not used. ??? -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Friday, November 24, 2006 4:22 PM To: CF-Talk Subject: Re: How do I get number of birthdays? I think this will work...check the logic, though. WHERE ((Month(Client_Birth_Date) = #Form.Earliest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Earliest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Earliest_Birth_Month#) cfif Form.Earliest_Birth_Month GT Form.Latest_Birth_Month !---This takes care of cases where the months go over a new year--- OR cfelse AND /cfif ((Month(Client_Birth_Date) = #Form.Latest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Latest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Latest_Birth_Month#) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261660 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How do I get number of birthdays?
Hi, Bobby, and thanks for the reply... I knew it was a long shot, but I tried it and got a query error. Actually, Day Month are not variables, but are the default values for selects. So, if the user doesn't specify another value for day or month, such as 1 for Day and Jan for Month, then the query is supposed to bypass another in the containing CFIF statement. The strangest thing is that it worked fine yesterday, but today won't work unless that code is taken out of the query or the 4 formfields involved in the CFIF wrapper have values specified other than the defaults. Under all other circumstances the query runs fine... So odd... Rick -Original Message- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Saturday, November 25, 2006 1:38 PM To: CF-Talk Subject: RE: How do I get number of birthdays? It looks to me like Day is a variable but you are comparing the value of #Form.Earliest_Birthdate_Day# to the actual WORD Day instead of the value of a variable named Day. Try this instead CFIF Form.Earliest_Birthdate_Day is not Day and Form.Earliest_Birthdate_Month is not Month and Form.Latest_Birthdate_Day is not Day and Form.Latest_Birthdate_Month is not Month ...:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261661 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How do I get number of birthdays?
IF the form field doesn't exist, an error is being thrown. You may not actually see the error depending on if a cftry is wrapped around the query. Or sometimes I've just had errors that don't get thrown correctly. On 11/25/06, Rick Faircloth [EMAIL PROTECTED] wrote: Jim... (or anyone else)... Do you see anything wrong with this code (part of a larger query) that would cause the query not to return no results if the following formfields, Form.Earliest_Birthdate_Day and Form.Earliest_Birthdate_Month and Form.Latest_Birthdate_Day and Form.Latest_Birthdate_Month are *not* used in the form (in other words, they CFIF at the beginning of this part of the query should exclude this section from consideration in the query). Here's the code: CFIF Form.Earliest_Birthdate_Day is not Day and Form.Earliest_Birthdate_Month is not Month and Form.Latest_Birthdate_Day is not Day and Form.Latest_Birthdate_Month is not Month and ((Month(Client_Birthdate) = #Form.Earliest_Birthdate_Month# and Day(Client_Birthdate) = #Form.Earliest_Birthdate_Day#) orMonth(Client_Birthdate) #Form.Earliest_Birthdate_Month#) CFIF Form.Earliest_Birthdate_Month GT Form.Latest_Birthdate_Month !---This takes care of cases where the months go over a new year--- or CFELSE and /CFIF ((Month(Client_Birthdate) = #Form.Latest_Birthdate_Month# andDay(Client_Birthdate) = #Form.Latest_Birthdate_Day#) orMonth(Client_Birthdate) #Form.Latest_Birthdate_Month#) /CFIF Thanks, Rick PS - This was working fine yesterday, then today it just started returning no results if the formfields Earliest_Birthdate_Day, Earliest_Birthdate_Month, Latest_Birthdate_Day, and Latest_Birthdate_Month are not used. ??? -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Friday, November 24, 2006 4:22 PM To: CF-Talk Subject: Re: How do I get number of birthdays? I think this will work...check the logic, though. WHERE ((Month(Client_Birth_Date) = #Form.Earliest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Earliest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Earliest_Birth_Month#) cfif Form.Earliest_Birth_Month GT Form.Latest_Birth_Month !---This takes care of cases where the months go over a new year--- OR cfelse AND /cfif ((Month(Client_Birth_Date) = #Form.Latest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Latest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Latest_Birth_Month#) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261663 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: How do I get number of birthdays?
All the involved formfields exist, with default values (all four are selects... No cftry's involved... maybe it's the wrapping, somehow. I'll look more closely at that. Thanks for the feedback, Matt. Rick -Original Message- From: Matt Williams [mailto:[EMAIL PROTECTED] Sent: Saturday, November 25, 2006 3:46 PM To: CF-Talk Subject: Re: How do I get number of birthdays? IF the form field doesn't exist, an error is being thrown. You may not actually see the error depending on if a cftry is wrapped around the query. Or sometimes I've just had errors that don't get thrown correctly. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261667 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How do I get number of birthdays?
Boy this is really started to get annoying... Still can't see why the query below returns 0 records if the selects, Form.Earliest_Birthdate_Day Form.Earliest_Birthdate_Month Form.Latest_Birthdate_Day Form.Latest_Birthdate_Month are the default values and the CFIF condition around them causes the birthdate sql to be skipped. If I put info in the birthdate forms, every works perfectly. Here's the entire query... anyone have a clue? Thanks, Rick CFQUERY Name=Get_Report_Data Datasource=#DSN# Select C.*, A.*, E.*, IC.* From clients C Left Join accounts A on C.Client_ID = A.Client_ID Left Join employers E on C.Employer_ID = E.Employer_ID Left Join insurance_companies IC on A.Company_ID = IC.Company_ID Where 1=1 CFIF Form.Client_ID is not All Clients and C.Client_ID = '#Form.Client_ID#' /CFIF CFIF Form.Client_City is not All Cities and C.Client_City = '#Form.Client_City#' /CFIF CFIF Form.Client_State is not All States and C.Client_State = '#Form.Client_State#' /CFIF CFIF Form.Client_Zip_Code is not All Zip Codes and C.Client_Zip_Code = '#Form.Client_Zip_Code#' /CFIF CFIF Form.Employer_ID is not All Employers and C.Employer_ID = '#Form.Employer_ID#' /CFIF CFIF Form.Earliest_Birthdate_Day is not Day and Form.Earliest_Birthdate_Month is not Month and Form.Latest_Birthdate_Day is not Day and Form.Latest_Birthdate_Month is not Month and ((Month(Client_Birthdate) = #Form.Earliest_Birthdate_Month# and Day(Client_Birthdate) = #Form.Earliest_Birthdate_Day#) or Month(Client_Birthdate) #Form.Earliest_Birthdate_Month#) CFIF Form.Earliest_Birthdate_Month GT Form.Latest_Birthdate_Month !---This takes care of cases where the months go over a new year--- or CFELSE and /CFIF ((Month(Client_Birthdate) = #Form.Latest_Birthdate_Month# and Day(Client_Birthdate) = #Form.Latest_Birthdate_Day#) or Month(Client_Birthdate) #Form.Latest_Birthdate_Month#) /CFIF Order by C.Client_Last_Name /CFQUERY ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261672 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: How do I get number of birthdays?
Rick Faircloth wrote: CFIF Form.Earliest_Birthdate_Day is not Day and Form.Earliest_Birthdate_Month is not Month and Form.Latest_Birthdate_Day is not Day and Form.Latest_Birthdate_Month is not Month and (((Month(Client_Birthdate) = #Form.Earliest_Birthdate_Month# and Day(Client_Birthdate) = #Form.Earliest_Birthdate_Day#) or Month(Client_Birthdate) #Form.Earliest_Birthdate_Month#) CFIF Form.Earliest_Birthdate_Month GT Form.Latest_Birthdate_Month !---This takes care of cases where the months go over a new year--- or CFELSE and /CFIF ((Month(Client_Birthdate) = #Form.Latest_Birthdate_Month# and Day(Client_Birthdate) = #Form.Latest_Birthdate_Day#) or Month(Client_Birthdate) #Form.Latest_Birthdate_Month#)) /CFIF Order by C.Client_Last_Name /CFQUERY Rick, See above...I added in parentheses to group the birthdate stuff, so that it is evaluated together. The way you had it before you could have possibly had all (or any) of the parameters evaluate to false, but if the earliest month was greater than the latest month, that OR would have made it so records would be returned if that last part about the latest birthdate was true. Hopefully that makes sense...it's kind of hard to explain without pointing at it. Anyway, that is the only thing I can think of that would cause what you are describing. -jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261675 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: How do I get number of birthdays?
The way you had it before you could have possibly had all (or any) of the parameters evaluate to false, Jim, the odd thing is that the CFIF statement should prevent any of the birthdate code from being processed at all if no days or months are selected. That's the oddest part. And I can't find any missing characters which would explain the problem. Usually, this sort of problem comes down to a typo, but I can't see it. Rick -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Saturday, November 25, 2006 9:57 PM To: CF-Talk Subject: Re: How do I get number of birthdays? Rick, See above...I added in parentheses to group the birthdate stuff, so that it is evaluated together. The way you had it before you could have possibly had all (or any) of the parameters evaluate to false, but if the earliest month was greater than the latest month, that OR would have made it so records would be returned if that last part about the latest birthdate was true. Hopefully that makes sense...it's kind of hard to explain without pointing at it. Anyway, that is the only thing I can think of that would cause what you are describing. -jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261676 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: How do I get number of birthdays?
Nice try... but it's a no go... It works the same way as before. This is just too odd... for it to have worked fine for a day, then the next day, without any changes, it begins to return different results. I guess I can always default the Earliest_Birthdate_Day to 1 and Earliest_Birthdate_Month to Jan and Latest_Birthdate_Day to 31 and Latest_Birthdate_Month to Dec... That way, if no changes are made to those fields, all client records will be returned. Thanks for your help Jim! Rick -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Saturday, November 25, 2006 9:57 PM To: CF-Talk Subject: Re: How do I get number of birthdays? Rick, See above...I added in parentheses to group the birthdate stuff, so that it is evaluated together. The way you had it before you could have possibly had all (or any) of the parameters evaluate to false, but if the earliest month was greater than the latest month, that OR would have made it so records would be returned if that last part about the latest birthdate was true. Hopefully that makes sense...it's kind of hard to explain without pointing at it. Anyway, that is the only thing I can think of that would cause what you are describing. -jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261677 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
How do I get number of birthdays?
Hi, all.yes, some of us are working today. I'm trying to output client info for clients who have birthdates between two designated months and days.not years. For example, I need to return all clients who have a birthday including and between Nov 27 and Dec 3, no matter what year. I tried where Month(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Day(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Month (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and Day (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and that code works (MySQL) when the birthdates fall in the same month, such as Nov 5 through Nov 12, but when the birthdates span two months, such as Nov 27 through Dec 3, the query returns no results. using between has been suggested, but I need to include birthdates on the dates specified, not just between them, and if I'm not mistaken, between would only return dates between the specified dates. Suggestions? Thanks, Rick PS - MySQL and CF 4.5 ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261617 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: How do I get number of birthdays?
Rick Faircloth wrote: Hi, all.yes, some of us are working today. I'm trying to output client info for clients who have birthdates between two designated months and days.not years. For example, I need to return all clients who have a birthday including and between Nov 27 and Dec 3, no matter what year. I tried where Month(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Day(Client_Birth_Date) = '#Form.Earliest_Birth_Date#' and Month (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and Day (Client_Birth_Date) = '#Form.Latest_Birth_Date#' and that code works (MySQL) when the birthdates fall in the same month, such as Nov 5 through Nov 12, but when the birthdates span two months, such as Nov 27 through Dec 3, the query returns no results. I think this will work...check the logic, though. WHERE ((Month(Client_Birth_Date) = #Form.Earliest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Earliest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Earliest_Birth_Month#) cfif Form.Earliest_Birth_Month GT Form.Latest_Birth_Month !---This takes care of cases where the months go over a new year--- OR cfelse AND /cfif ((Month(Client_Birth_Date) = #Form.Latest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Latest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Latest_Birth_Month#) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261618 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: How do I get number of birthdays?
Thanks, Jim. I'll give it a try. Rick -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Friday, November 24, 2006 4:22 PM To: CF-Talk Subject: Re: How do I get number of birthdays? I think this will work...check the logic, though. WHERE ((Month(Client_Birth_Date) = #Form.Earliest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Earliest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Earliest_Birth_Month#) cfif Form.Earliest_Birth_Month GT Form.Latest_Birth_Month !---This takes care of cases where the months go over a new year--- OR cfelse AND /cfif ((Month(Client_Birth_Date) = #Form.Latest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Latest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Latest_Birth_Month#) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261623 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: How do I get number of birthdays?
Jim, I started trying to understand the logic of the code and gave up, but it seems to work as written! Thanks! Rick -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Friday, November 24, 2006 4:22 PM To: CF-Talk Subject: Re: How do I get number of birthdays? I think this will work...check the logic, though. WHERE ((Month(Client_Birth_Date) = #Form.Earliest_Birth_Month# AND Day(Client_Birth_Date) = #Form.Earliest_Birth_Day#) ORMonth(Client_Birth_Date) #Form.Earliest_Birth_Month#) cfif Form.Earliest_Birth_Month GT Form.Latest_Birth_Month !---This takes care of cases where the months go over a new year--- OR cfelse AND /cfif ((Month(Client_Birth_Date) = #Form.Latest_Birth_Month# ANDDay(Client_Birth_Date) = #Form.Latest_Birth_Day#) OR Month(Client_Birth_Date) #Form.Latest_Birth_Month#) ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:261637 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
