file1:<br><form action="file2.cfm" method"post"><br><input type="text" name="date"> deadline date<br><input type="submit" value="submit"><br></form><br>---------<br>file2:<br><cfquery name="finddate" datasource="abcd"><br>select *<br>from tablename<br><b>where 'tablename.deadline' > '#date#'</b><br>order by deadline<br></cfquery><br>--------<br>problem is tablename.deadline is date format.<br>no matter what i input in file1 form, then search to file2, query result is to give me whole database ignoring "<b>where 'tablename.deadline' > '#date#'</b> ".<br>when i change > to <, no matter what i type in form, the result is nothing.<br>i change input date format, like yyyy-mm-dd, mm/dd/yyyy, mon-dd-yyyy. result is same, give me the whole database order by deadline ignoring "<b>where 'tablename.deadline' > '#date#' </b>".<br><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
date format 2
- Thread starter achai
- Start date
-
2
- #2
Try changing it to:<br><br><b>where tablename.deadline > '#date#'</b> (no single quotes)<br><br>Since this is a column, you should not use the quotes around it. Your query is treating it like a string value.<br><br>Let me know if this works...
- Thread starter
- #3
unfortunately. it shows erro, when i delete the <b>'</b> <b>'</b> covering data.deadline.<br>---------------<br>Error Diagnostic Information<br>ODBC Error Code = 22005 (Error in assignment)<br><br>[Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression.<br><br>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (16:1) to (16:62).<br><br>
hhhhhhmmmmmmm..........<br><br>Try this...<br><br>where tablename.deadline > #date# (take quotes off #date#)<br><br>and if this doesn't work:<br><br>where tablename.deadline > ###date### (access help shows dates surrounded by pound signs. Use the double pound signs to escape them in CF, plus the pound signs that surround the variable name...)<br><br>Let me know if one of these work, and if so, which one (or both, maybe?)..
<br><br>DM
<br><br>DM - Thread starter
- #5
it should... But it looks like you put the single quotes back around tablename.deadline. Those need to come off if you want it to compare it as a column and not a string value....<br><br>Here's a query I just put together from an access 97 database I have using the query builder...<br><br><FONT FACE=monospace>SELECT Client.*<br>FROM Client<br>WHERE (((Client.Date)>#4/1/2000#));</font><br><br>This query in CF should look like this:<br><br><FONT FACE=monospace>SELECT Client.*<br>FROM Client<br>WHERE (((Client.Date)>###4/1/2000###));</font>
- Thread starter
- #7
- Status