Date Range filtering trouble

[Thanasis \(sch\)]

Hello to everyone,

i am going to built a database application with DataBase Interface Wizard
and FrontPage 2003.My DataBase will be Access 2000.
Inside my database (mdb file) there is a table whose name is NEWS.A field of
the table is called PUBLISH_DATE(type DATE).

I want the user of my application to provide 2 dates, in order to filter the
records according to values of the PUBLISH_DATE field.
i have followed the steps below:
1) i have created a search form with 2 text boxes with names DATE1 and
DATE2.I save the form as asp page SEARCH.ASP.The method parameter is POST
and the action is RESULTS.ASP.

2)I have created a RESULTS.ASP page which actually filters the records
according to the user input (from DATE1 to DATE2).
I click Insert-->DataBase-->Results-->I choose my database and i click on
Next-->then i click on Custom Query (Edit) and i type
SELECT * FROM NEWS WHERE (PUBLISH_DATE>= date1 AND PUBLISH_DATE<= date2).
I click next..next and the wizard is closed.
Also i tried something like that:
SELECT * FROM NEWS WHERE (PUBLISH_DATE>= request.form("date1" AND
PUBLISH_DATE<= request.form("date2").

However i got an error message from the Wizard.I didn't get the filtering i
expected.

How this can be done with SQL or DIW?
Wishes
Thanasis

 

[Jim Buyens]

Howdy.

Your SQL statement needs to look like:

SELECT * FROM NEWS
WHERE (PUBLISH_DATE >= #::date1::#)
AND (PUBLISH_DATE <= #::date2::#)

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------

 

[Jon Spivey]

Hi,
The query you want is just

SELECT *
FROM Table
WHERE Publish_Date BETWEEN #:ate1::# AND #:ate2::#

Having said that there's a lot of problems that can arise from date querys -
for example to a UK user 1/6/2004 would be 1 June to a US user ir would be 6
Jan. If you have an international user base I'd suggest using ISO format for
dates, eg today is 2004-06-23 and presenting drop downs for day month and
year which you could combine into a date.

 

[Thanasis \(sch\)]

Dear Jim thanks for your response,
i have pasted the SQL statement you provided
SELECT * FROM NEWS
WHERE (PUBLISH_DATE >= #::date1::#)
AND (PUBLISH_DATE <= #::date2::#)
in Custom Query window and i clicked on verify query.Then i got an error
message.
However when i ommited the # character my query work
Why does this happen?
And another question.
When in a text box of my search form i type whatever but a date (i.e a
string), in results page i got an error message from DRW.
Cheers
thanasis


SELECT * FROM NEWS
WHERE (PUBLISH_DATE >= #::date1::#)
AND (PUBLISH_DATE <= #::date2::#)

 

[Thanasis \(sch\)]

Dear Jon thanks for your response,
i have pasted the SQL statement you provided
SELECT * FROM NEWS
WHERE (PUBLISH_DATE BETWEEN #:ate1::# AND #:ate2::#)
in Custom Query window and i clicked on verify query.Then i got an error
message.
However when i ommited the # character my query works.
Why does this happen?
And another question.
When in a text box of my search form i type whatever but a date (i.e a
string), in results page i got an error message from DRW.
Cheers
thanasis

 

[Jim Buyens]

I already answered this for you when you asked the same question by
e-mail.

Please don't make duplicate posts, or ask the same question via
different channels. By doing so, you "reward" the people who are
helping you by giving them double work.

Date literals in Access need # characters surrounding them, as in
#23-jun-2004#. The fact that you got an error from the # characters
leads me to believe that the PUBLISH_DATE field isn't really a date,
or the database really isn't Access. But if it's working the way you
want without the #'s, why worry about it?

As to the invalid date syntax, yes, if you ask the database to perform
a date comparison, and then give it an invalid date, the query is
going to blow up. To prevent this, you would need to add some
JavaScript to your forms page, edit check the date fields for proper
syntax, and then block the submit if the syntax wasn't correct.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------