where

[Guest]

Hi i have this code in my query.
WHERE MatchYear = <CFIF isdefined (FORM.Years)>#FORM.Years#<CFELSE>current year</CFIF>

this wont work, what i am tring to do is if the form is defined then the where value will be the form value, if not defined then i would like to have the year now not sure what the current year code is

can anyone help

[The_ScareCrow]

<CFIF isdefined ("FORM.Years")>#FORM.Years#<CFELSE>#Year(Now())#</CFIF>

Ken

[Guest]

Be careful. If FORM.Years is a text box, it would always be defined. If there is nothing in the text box, its value would be "". If FORM.Years is a radio button or checkbox, it would only be defined if it is selected. You may want to do something like the code below.

[boughtonp]

Except a user can easily enter something that's not a year into a text box... so something like this should be a minimum:
IsDefined("Form.Years") AND Len(Form.Years) AND IsNumeric(Form.Years)

And it might be wise to also do a check to ensure it's actually a valid year - something along the lines of this should work...
REFind("^(19|20)?[0-9][0-9]$",Form.Years)

[DJ_Jamba]

You are all correct!
Not to mention all of this should be done outside of the query:

<CFPARAM NAME = "theYear" DEFAULT="#Year(Now())#">
<CFIF IsDefined("Form.Years") AND Len(Form.Years) AND IsNumeric(Trim(Form.Years)) AND REFind("^(19|20)?[0-9][0-9]$",Trim(Form.Years))>
<CFSET theYear = "#Trim(Form.Years)#">
</CFIF>
<CFQUERY NAME="getSomething" DATASOURCE="#myDS#">
SELECT something FROM somewhere WHERE MatchYear = #theYear#
</CFQUERY>

EDIT: and just for good measure:
WHERE MatchYear = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="#theYeAR#">