I have an evaluations table that has an opendate, and closedate field, both of which are dates, per evaluation.
I need to give the user a dropdown menu of evaluations so they can select one and view its results. Whether the user can see evals that are currently open (active), depends on their role. Here's my select statement. It appears to work as needed, but I don't know if there's a better way to do it. It basically says, if this user can't see evaluation while they're open, give me a query of evals where today is on or after the close date. Or else if this user CAN see evals while they're in progress, give me a query of evals where today is on or after the open date. SELECT tblperiods.periodid, tblperiods.periodname, tblperiods.periodsort, tblperiods.periodisactive, tblevaluations.evalid, tblevaluations.periodid, tblevaluations.evalname, tblevaluations.evalopendate, tblevaluations.evalclosedate, tblevaluations.evalshowgenderinput, tblevaluations.evalshowagegroupinput, tblevaluations.evalnumtildisplay FROM tblperiods, tblevaluations WHERE tblperiods.periodid = tblevaluations.periodid <!--- If user is allowed to see only closed evals in dropdown ---> <cfif viewevalwhileactive EQ "No"> AND Now() >= tblevaluations.evalclosedate <!--- User is allowed to see active evals in dropdown ---> <cfelse> AND Now() >= tblevaluations.evalopendate </cfif> ORDER BY tblperiods.periodname, tblevaluations.evalname thanks, Will ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 beta â Build next generation applications today. Free beta download on Labs http://www.adobe.com/cfusion/entitlement/index.cfm?e=labs_adobecf8_beta Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282144 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
