SQL date query. Please help me!

[Si]

Hi there.

I have a page on a website I am building where is want to look for all
records added in the last 60 days.

The date is added in short UK format, that is 25/12/2003 in an access
database. (set as a date field)

I am using <% session.lcid=2057 %> in my header to force UK date format.

My problem is, everything seems to be correct, but the query just returns
all records in the table.

The SQL query is

if request("lookup") = "latest" then
recent = FormatDateTime(Now()-60,vbShortDate)
end if
if request.querystring("lookup") = "latest" then
myquery="SELECT * FROM Property WHERE datein > " & recent & " ORDER BY
datein DESC"
else
myquery="SELECT * FROM Property WHERE department LIKE '%" &
request.querystring("lookup") & "%' AND pricefaieuro > " & MinPrice & " AND
pricefaieuro < " & MaxPrice & " ORDER BY reference DESC"
end if

The SQL output via response.write seems to be correct (see following)

SELECT * FROM Property WHERE datein > 07/10/2003 ORDER BY datein DESC

(I have also tried WHERE NOT datein < " & recent &" ........)

Does anyone know what the proplem may be. I have been looking at this for
hours now and last night and am no closer to fixing it.

Thanks in advance

Simon

 

[CJM]

Have you run this SQL in Access itself? Did you get the same results?

 

[Bob Barrows]

Hi there.

I have a page on a website I am building where is want to look for all
records added in the last 60 days.

The date is added in short UK format, that is 25/12/2003 in an access
database. (set as a date field)

I am using <% session.lcid=2057 %> in my header to force UK date
format.

Start here:
http://www.aspfaq.com/show.asp?id=2040

Where the article says to use "YYYYMMDD" format, it's talking about SQL
Server. This format will not be recognized by Jet. For Access, it is best to
use "YYYY-MM-DD". And don't forget to delimit the date literal with hash
marks (#).

HTH,
Bob Barrows

 

[Roland Hall]

:
: if request.querystring("lookup") = "latest" then
: myquery="SELECT * FROM Property WHERE datein > " & recent & " ORDER BY
: datein DESC"

if request.querystring("lookup") = "latest" then
myquery="SELECT * FROM Property WHERE datein > '" & recent & "' ORDER BY
datein DESC"

Instead of returning this:

SELECT * FROM Property WHERE datein > 07/10/2003 ORDER BY datein DESC

....it should return this:

SELECT * FROM Property WHERE datein > '07/10/2003' ORDER BY datein DESC

You need the quotes around the date.

HTH...

--
Roland

This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose.
-Technet Knowledge Base-
http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=TECH&ln=EN-US&FR=0
-Technet Script Center-
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/scriptcenter/default.asp
-MSDN Library-
http://msdn.microsoft.com/library/default.asp

 

[TomB]

If it was me I'd create a query in Access that returns the data from the
last 60 days, and call that query rather than dynamically building a SQL
Statement.