Query between two date entries 1

[florida41]

I am trying to create form where I can search between two dates of my Date field (submitDate).

The two values below (dateOne and dateTwo) are not database fields. I am using them just to pass values for my Beginning and Ending date.
My form:

<form action="action7.cfm" name="search7" method="post">

Name<input type="Text" name="name">
<br><br>
Beginning Date<input type="Text" name="dateOne">&nbsp;
End Date<input type="Text" name="dateTwo"> 
<input type="submit" name="submit">
</form>

My action page:

<cfquery name="theRecord" datasource="theDSN">
SELECT *
FROM TableOne
WHERE 0=0
<cfif len(trim(FORM.name))>
AND name LIKE '%#form.name#%'
</cfif>
AND submitDate Between #FORM.dateOne# And #FORM.dateTwo# 
</cfquery>

Please advise why this is not working:

 

[ecobb]

Are you getting an error, or is it just not returning results?



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[florida41]

Not returning results

 

[TruthInSatire]

are you sure there are records that fit that criteria? and are you sure you're formating the date the same way?

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[florida41]

Here is the date sql that I tested to use for just fetching a page to check the format:

<cfquery name="theRecord" datasource="theDSN">
SELECT *
FROM TableOne
where submitDate like '4/22/02'
</cfquery>

This pulls up the record:

Name test = TestOnlyName
Date = 2002-04-22 00:00:00



Now If I try and do the between statement

SELECT *
FROM TableOne
where dte_subm between '4/22/02' and '5/22/02'

it gives me the error:
ODBC Error Code = 22005 (Error in assignment)


[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


This is an Access 2000 database. Please advise?

 

[TruthInSatire]

Try this maybe

AND submitDate Between ###dateformat(FORM.dateOne, "yyyy-mm-dd")### And ###dateformat(FORM.dateTwo, "yyyy-mm-dd")###

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[florida41]

Thank you!! It now works.