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
