search date filter help!

[code L]

i have go 4 txtbox in a form and a search button. first is a specific date search second and third is a ranged date search; from and to when. fourth is done by. what i want to know is how to do search for date.

<cfquery name="search" datasource="searchdb">
SELECT *
FROM searchTable
WHERE
date LIKE #form.txtdate# //specific date search
AND
<cfif #form.txtdatefrom# is not "">date >= #form.txtdatefrom#</cfif>
<cfif #form.txtdateto# is not "">date <= #form.txtdateto#</cfif>
</cfquery>


this is all done by a search.cfm page only. form action="search.cfm". no other page involve.


i am getting the error txtdatefrom is undefined in form.


help me thanks!

[Jt107]

If you set default for txtdatefrom like
<cfparam name="form.txtdatefrom" default=""> at the top of the processing page you will not get the error.
Also could it be a mismatch? your text field name and variable name is not the same? Just a guess.

[code L]

i am sure there is no mismatch. i will give it a try when my com is ready for me to try that out =) thanks.

[Gjslick]

Code L, can you post your HTML form code?

-Greg

[code L]

Gjslick, I am currently attached to an organisation by my school, this organisation is running on intranet so i can only scan you the code.. hope you dont mind. HAHAHA i just found out that i can save what i scan into txt file with adobe =D

everything is done in this page, item consist of
:a form for user to input search
:SQL for filter
:a form table for displaying result

why i want a form for displaying result is because user can select on the result name. therefore i need a input for user to select.

problem:
my sql is not working. it says that textboxname from form is undefined. why?

searchdb_dir.cfm

<!--this is search table --> 

<form name="searchdir" action="searchdb_dir.cfm" method="post"> 
<table width="800" height="50" align="center">
<tr> 
<td width="179" align="left" valign="top">
search Date:<br> 
<input type="text" readonly 
value="<cfoutput>#Dateformat(date_now,"d/m/yyyy")#</cfoutput>" name="txtDate_dir"><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDate_dir')">
</td> 
<td width="179" valign="top">
Date from:<br> 
<input type="text" readonly name="txtDateFrom_dir"><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDateFrom_dir')">
</td> 
<td width="179" valign="top">
Date to:<br> 
<input type="text" readonly name="txtDateTo_dir"><img src="pics/cal.gif"style="cursor:pointer" onclick="opncal('txtDateTo_dir')">
</td> 
<td width="171" valign="top"> 
project Name:<br> 
<input type="text" name="txtproj">
</td> 
<td width="68" align="center"> 
<input type="submit" name="searchBtn" value="search"> 
</td> 
</tr> 
</table> 
</form> 




<!--SQL for filter --> 

<cfquery name="DIRdata" datasource="DBcadcam"> 
SELECT projectName, count(*) as NumofRequest, sum(NumofDrawing) as totalqty
FROM DIR 
WHERE 
DateRequested Like ,#txtDate# 
<cfif #txtDateFrom_dir# is not "">AND DateRequested >= #createODBCdate(form.txtDateFrom_dir)#</cfif> 
<cfif #form.txtDateTo_dir# is not "">AND DateRequested <= #createODBCdate(form.txtDateTo_dir)#</cfif> 
GROUPBY projectName 
</cfquery> 




<!--display table --> 

<form name="formtable" action="" method=""> 
<tablewidth="10" align="center" bgcolor="#FFFFCC" frame="box"> 
<thead bgcolor="#66FFCC"> 
<tr> 
<td width="SO" ali~n="center">s/N</td> 
<td width="1??" allgn="center">project Name</td> 
<td width="188">Total number of Drawing</td> 
<td width="188">Number of Request</td> 
</tr> 
</thead> 
<cfoutput query="DIRdata"> 
<tr> 
<td align="center">#DIRdata.currentrow#</td> 
<td align="center"> 
<input type="hidden" name="ProiectName" value="#projectName#"> 
<input type="hidden" name="DIRT. value="dir"><a "href='dir_detail.cfm?DIR=dir&projectName=#projectName# name="selected" id="selected" target="_blank">#projectName#</a>
</td> 
<td>#totalqty#</td> 
<td>#NumofRequest#</td> 
</tr> 
</cfoutput> 
</table> 
</form>

[Gjslick]

Code L, is that code all in one file?

[Gjslick]

If that code is all in one file, then that would be your problem. The form has to be submitted before the values can be accessed in the form scope on the form's target (action) page.

However, if you do want all of this on one .cfm page, and your form submits to itself, then you just have to write some code to prevent the rest of the page from being processed until there is an actual form submission. Try this:

<!-- this is search table --> 
<form name="searchdir" action="searchdb_dir.cfm" method="post"> 
    <input type="hidden" name="searched" value="true">
    
    <table width="800" height="50" align="center">
        <tr> 
            <td width="179" align="left" valign="top">
                search Date:<br> 
                <input type="text" name="txtDate_dir" value="<cfoutput>#Dateformat(now(), 'd/m/yyyy')#</cfoutput>" readonly><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDate_dir')">
            </td> 
            <td width="179" valign="top">
                Date from:<br> 
                <input type="text" name="txtDateFrom_dir" readonly><img src="pics/cal.gif" style="cursor:pointer" onclick="opncal('txtDateFrom_dir')">
            </td> 
            <td width="179" valign="top">
                Date to:<br> 
                <input type="text" name="txtDateTo_dir" readonly><img src="pics/cal.gif"style="cursor:pointer" onclick="opncal('txtDateTo_dir')">
            </td> 
            <td width="171" valign="top"> 
                project Name:<br> 
                <input type="text" name="txtproj">
            </td> 
            <td width="68" align="center"> 
                <input type="submit" name="searchBtn" value="search"> 
            </td> 
        </tr> 
    </table> 
</form> 


<cfif structKeyExists( form, 'searched' )>    <!--- The form has been submitted, show results --->

    <!--SQL for filter --> 
    <cfquery name="DIRdata" datasource="DBcadcam"> 
        SELECT 
            projectName, 
            count(*) as NumofRequest, 
            sum(NumofDrawing) as totalqty
        FROM 
            DIR 
        WHERE 
            DateRequested Like '%#txtDate#%'
            <cfif txtDateFrom_dir neq "">AND DateRequested >= #createODBCdate(form.txtDateFrom_dir)#</cfif> 
            <cfif form.txtDateTo_dir neq "">AND DateRequested <= #createODBCdate(form.txtDateTo_dir)#</cfif> 
        GROUP BY 
            projectName 
    </cfquery> 
    
    
    
    
    <!--display table --> 
    <form name="formtable" action="" method=""> 
        <table width="10" align="center" bgcolor="#FFFFCC" frame="box"> 
            <thead bgcolor="#66FFCC"> 
                <tr> 
                    <td width="SO" ali~n="center">s/N</td> 
                    <td width="1??" allgn="center">project Name</td> 
                    <td width="188">Total number of Drawing</td> 
                    <td width="188">Number of Request</td> 
                </tr> 
            </thead> 
    
            <cfoutput query="DIRdata"> 
                <tr> 
                    <td align="center">#DIRdata.currentrow#</td> 
                    <td align="center"> 
                        <input type="hidden" name="ProiectName" value="#projectName#"> 
                        <input type="hidden" name="DIRT" value="dir"><a "href=dir_detail.cfm?DIR=dir&projectName=#projectName#" name="selected" id="selected" target="_blank">#projectName#</a>
                    </td> 
                    <td>#totalqty#</td> 
                    <td>#NumofRequest#</td> 
                </tr> 
            </cfoutput> 
        </table> 
    </form>
    
</cfif>

I just created a simple hidden form field so it can be checked if the form has been submitted or not before continuing to your SQL query and results table. Try that and let me know how it goes.

-Greg

[code L]

Gjslick, yes these code is in a page. thanks for the quick solution! sadly i can only try this out on Monday! all my coding is not with me and i do no have a server which i could test out my coldfusion coding. I will tell you if it works =)

[code L]

that works! thanks Gjslick =)

[Gjslick]

np dude, glad you got it working =)

[takumi309]

tried. works great. Thanks