Comparing a date field to a date in a form.

[Les Juby]

"SELECT id, title FROM Businesses WHERE Date_Added >= " &
Request.Form("Since_Date")

This SQL string returns all records from the table Businesses and does
not seem to be able to recognise Request.Form("Since_Date") as a valid
date. The Since_Date value is entered into a form field as
"01-Jan-2005" and appears to be treated as a date; when viewing the
SQL statement(with a response.write) it shows the date as 1/1/2005
even if it is entered into the form as 2005/1/1

I've also tried CDate(Request.Form("Since_Date")) with no luck,
although this was the treatment that worked when the SQL statement was
running in an .idc file.

Date_Added is a regular DateTime field in an Access 2000 database

Any suggestions please ?????

.les.

 

[McKirahan]

"SELECT id, title FROM Businesses WHERE Date_Added >= " &
Request.Form("Since_Date")

This SQL string returns all records from the table Businesses and does
not seem to be able to recognise Request.Form("Since_Date") as a valid
date. The Since_Date value is entered into a form field as
"01-Jan-2005" and appears to be treated as a date; when viewing the
SQL statement(with a response.write) it shows the date as 1/1/2005
even if it is entered into the form as 2005/1/1

I've also tried CDate(Request.Form("Since_Date")) with no luck,
although this was the treatment that worked when the SQL statement was
running in an .idc file.

Date_Added is a regular DateTime field in an Access 2000 database

Any suggestions please ?????

.les.

Try:

SELECT id, title
FROM Businesses
WHERE Date_Added >= #" Request.Form("Since_Date") & "#"

 

[Rob Meade]

...

Date_Added is a regular DateTime field in an Access 2000 database

Any suggestions please ?????

Hi Les,

Try reformatting the date before using it..for example:

dtmSinceDate = Request.Form("Since_Date")

dtmSinceDate = Year(dtmSinceDate) & "-" & Month(dtmSinceDate) & "-" &
Day(dtmSinceDate)

SQL = "SELECT id, title FROM Businesses WHERE Date_Added >= " & dtmSinceDate


Regards

Rob

 

[Bob Barrows [MVP]]

"SELECT id, title FROM Businesses WHERE Date_Added >= " &
Request.Form("Since_Date")

This SQL string returns all records from the table Businesses and does
not seem to be able to recognise Request.Form("Since_Date") as a valid
date. The Since_Date value is entered into a form field as
"01-Jan-2005" and appears to be treated as a date; when viewing the
SQL statement(with a response.write) it shows the date as 1/1/2005
even if it is entered into the form as 2005/1/1

I've also tried CDate(Request.Form("Since_Date")) with no luck,
although this was the treatment that worked when the SQL statement was
running in an .idc file.

Date_Added is a regular DateTime field in an Access 2000 database

Any suggestions please ?????

.les.

Stop using dynamic sql. Use parameters.

sSQL="SELECT id, title FROM Businesses WHERE Date_Added >= ?"
arParms = array(cdate(Request.Form("Since_Date")))
set cmd=createobject("adodb.command")
set cmd.ActiveConnection = cn
set rs=cmd.execute(,arParms,1)

Bob Barrows