cfif question - probably easy but I don't get it

[tharring]

What's wrong with this cf code? I need to search a db table only if there is a contactid. (Other records are inventory and not pertinent to my search. So, I want to search for two different fields, date and date2, but only if there is a contactid. There must be some way to do this.

<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif (rsRes10.contactid) GTE 1>
<cfquery name="rsRes10" datasource="Petersen">
SELECT *
FROM res10
WHERE date="8/25" OR date2="8/25"
group by date, classname
<cfelse>
null
</cfif>
</cfquery>

Thanks for your help,

Tedra

[Dan_Bracuk]

There appear to be several things wrong.

First, your cfif refers to a query result, and if it returns true, you run that very query.  That doesn't make sense.  Unless there is some code you are not showing, your page probably crashes on this line with a message that says contactid is not defined in rsres10.

Next, in your query you are using questionable values for your dates.  Plus you have double quotes.  That often guarantees a syntax error.

Also in your query, you have a group by clause without selecting any aggregates.

[tharring]

You're right. A lot of mistakes in my <cfif>. I did get a "contactid undefined" so I put in a <cfparam

name="contactid" default="1"> above my other code. And it does return true, so I am saying if contactid is equal to or greater than 1... which it is... why doesn't it just run the select sql statement? That's what I want it to do. I was just qualifiying an additional search item, I thought. It may look like I am using questionable values for my dates, but the fields  are only called "date" and "date2". They're actually varchar fields. I didn't want to use the whole 8/25/10 date... just the month and day. Is there anything wrong with that? I will try it again without the double quotes, and I won't include the group by. Is there anything else you can tell me about how I can search for THREE different values? That's what I'm really trying to do... search for date 1, date 2 and any contactid. Thanks so much for your help.

Tedra

[Debbie2]

Aside from the issues Dan has already pointed out, to get what you're looking for, drop the CFIF statement and just write your WHERE statement like this: WHERE contactid >= 1 AND (date="8/25" OR date2="8/25")

[Dan_Bracuk]

Storing dates as text instead of dates is fundamentally a bad idea. 

When you say the query is not running, what is happening instead?  I generally solve if/else problems like this:

<cfif something>

yes

<cfelse>

no

then output or dump all relevent variables

</cfif>

[Adam Cameron.]

Everything that the others said, plus your CFML is incorrectly nested.  Your starting <cfif> is outside your <cfquery>, but you close it inside the <cfquery>.

--

Adam

[tharring]

Debbie, your answer of HOW TO do it saved me. That's all I needed to know. You should get a raise!!! My dates check out just fine now and I can do everything I need to do. THANKS SO MUCH! I'm still learning <cfif> and appreciate the points others have made as well.  Thank you.

Tedra