Posts by arrgh

ROFL .. I wish! Too bad code always does what we tell it to do, instead of what we want it to do ;-)

1) where date discontinued is between the start and end date
2) If the start date is NEQ to the end date I need to find if there is another entry with a discontinued date
3) if not then output the entries that have a NULL endate.

The 1st one's clear ... but I don't follow the rest of it. Why display records that don't match the filtered dates?

Can you give an example of all 3 cases using your sample data?

I just ran a second query looking for dayreturned IS NULL and kept the dayleft where statement

Yep, that'll do it! Good job.

If you want everything in 1 query. Just do somethin like this.

WHERE ID = 193 
AND dayLeft < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
AND ( <!--- using greater than or equals to also include selectedDate at midnight exactly --->
    dayReturn >= <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date"> 
OR  dayReturn IS NULL 
    )

<cfif #dateAdd("d", -1, checkResident.dayleft)

That only checks whatever dates happen to be in the 1st record of the query. Remember you removed the WHERE clause. So there might be more than 1 record returned. For accurate results you'd have to cfloop through the query to check all of them.

The original sql didn't work because I didn't know the data contained times too. So it was the wrong the comparison. Just adjust it to account for the time.

Example: if today is 8/30/2011, #selectedDate# will be 8/10/2011 and #dayAfter# will be 8/11/2011.

<cfset todaysDate = createODBCDate(NOW())>
<cfset selectedDate = dateAdd("d", -20, todaysDate)> 
<cfset dayAfter     = dateAdd("d", 1, selectedDate)>

<cfquery name="checkResident" datasource="residents5">
SELECT *
FROM hospital
<!--- be sure to use cfqueryparam if resident id is a variable --->
WHERE ID = 193
AND   dayLeft    < <cfqueryparam value="#dayAfter#" cfsqltype="cf_sql_date">
AND   dayReturn > <cfqueryparam value="#selectedDate#" cfsqltype="cf_sql_date">
</cfquery>

When I do this in MSAccess sql view it works but doesn't work on cfm page.

(Yeah, that's special "Access" syntax. It may work with some drivers, or it may not.) But the correct way to pass dates in cfquery is with cfqueryparam.

WHERE Stuff.RegDate = <cfqueryparam value="5/1/2004" cfsqltype="cf_sql_timestamp">

Please help I have also tried shifting the date formats.

That only changes the user display. It doesn't make any difference to your query.

This has nothing to do with CF. It is a pure SQL question ;)

AND MoveOutDate = 'NULL'

NULL isn't a string. It is a special system value. You can't use equals "=" either. To find nulls you must use

WHERE  ColumnName IS NULL

return a reference to this component

I just meant return the object you created. That's basically what <cfreturn this> does. When used inside a component, THIS means "the component itself".

<cfcomponent>
<cffunction name="init" output="false" returntype="MyComponent">
  ....
    <!--- Return the component so the calling page can use it --->
     <cfreturn this>
  </cffunction>
</cfcomponent>

The reason you do that is so the calling page can use the component's functions

<!--- "myObj" now contains a MyComponent object --->
<cfset myObj = createObject("component", "MyComponent").init(dsn="My DSN")>

<!--- So you can call any of MyComponent's functions --->
<cfset result = myObj.someFunctionName()>

Because redirection occurs independent of the Yes/No values of that attribute

The AddToken attribute relates to client/session management. It has no affect on redirection. Only what's passed in the url. When addToken="yes", CF appends several client tokens (cfid, cftoken and possibly jsessionid) as URL parameters

So this

<cflocation url="http://www.yoursite.com/path/to/products.cfm?productid=4" addToken="yes">

becomes

http://www.yoursite.com/path/to/products.cfm?productid=4&cfid=xxxx&cftoken=xxxx&jsessionid=xxxxx"

If addToken="no", CF doesn't append the extra parameters to the URL

I didn't think the documentation was that cryptic. But then I'm more of a "teach a man to fish ..." kind of person ;)

... you need to read the documentation ;-)
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_c-d_25.html

This same post has appeared in other forums as troll bait. Do not feed the trolls!
http://en.wikipedia.org/wiki/Troll_%28Internet%29