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

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)
|/---------------------------------------------------
*----------------------------------------------------

>-----Original Message-----
>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
>
>
>.
>

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.

--
Cheers,
Jon
Microsoft MVP - FP

"Thanasis (sch)" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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
>
>

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::#)

"Jim Buyens" <(E-Mail Removed)> wrote in message
news:2046e01c458a9$1289baf0$(E-Mail Removed)...
> 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)
> |/---------------------------------------------------
> *----------------------------------------------------
>
> >-----Original Message-----
> >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
> >
> >
> >.
> >

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

"Jon Spivey" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Cheers,
> Jon
> Microsoft MVP - FP
>
> "Thanasis (sch)" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
> > 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
> >
> >
>
>

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)
|/---------------------------------------------------
*----------------------------------------------------



"Thanasis \(sch\)" <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
> 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::#)
>
> "Jim Buyens" <(E-Mail Removed)> wrote in message
> news:2046e01c458a9$1289baf0$(E-Mail Removed)...
> > 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)
> > |/---------------------------------------------------
> > *----------------------------------------------------
> >
> > >-----Original Message-----
> > >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
> > >
> > >
> > >.
> > >