Date Range filtering with FP2003 and DIW

[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.

I want to create a search form with 2 text boxes with names FROMDATE and
TODATE.
I save the form as asp page SEARCH.ASP.
When the user clicks on submit button i want to call another asp page
RESULTS.ASP which filters the records from dates ranging from (FROMDATE ) to
(TODATE)?
I'd like to point out that inside a table there is a date field say(DATEX)

How this can be done in FP2003 and DIW?

Wishes
Thanasis

 

[MD Websunlimited]

Hi,

You'd use a custom SQL query of the form

SELECT orders.[Date Ordered]
FROM orders
WHERE (((orders.[Date Ordered]) Between #6/1/2004# And #6/30/2004#));


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
J-Bots Plus 2002 87 components for FrontPage
http://www.websunlimited.com/order/Product/JBP2002/jbp_help_dir.htm
FrontPage Add-ins Since '97 2003 / 2002 / 2000 Compatible

 

[Andrew Murray]

For your search form:

Set up a results region with one field: TODATE (and repeat this procedure for
FROMDATE) set nothing in the criteria section and when you get to the layout of
the results, choose the dropdown box (this will then populate the dropdown box
with the data from your database at view-time. Repeat with FROM DATE. Have
these inside a <form> and </form> area. Add a button, label it "Search" Point
the action to your asp page where your results will be displayed, method = "GET".

Set up the search results area. the Criteria wiill be (something like) FromDate
"Less than" ToDate AND "ToDate" Greater than "FromDate". (if choosing from the
results wizard).

So the results should return everything between the two dates. (including equal
to the From and To dates)

Basically you will end up with two dropdown boxes and a search button in a form,
the form action will point to your results ASP page the drop down boxes will be
populated by the data already in the database.

So, in the results area of the results.asp (or whatever you call it) the sql
query will be something like

(View the code and see what the results wizard generated as far as the query is
concerned.

Select * from TABLE [TableName] Where FromDate GREATER THAN or EQUAL TO
%FromDate% AND ToDate LESS THAN or EQUAL TO %ToDate%

If it's not quite what you need, go back through the wizard and change the
criteria to suit the query, or write a custom query if you know SQL well enough
to do so.

Sorry just re-read your query, I've said use drop down boxes - there is a simpler
way, the results wizard will create the search form for you if you specify it,
but it will place it on the same page as your results, you can copy and paste the
search for to another page, then point it at the results page.

The drop down box way forces the user to choose the two dates from existing data,
which will naturally return a result, instead of them guessing, and not getting
any results because one or other of the dates do not exist in the database.

That might be something to consider.

Hopefully this makes sense!

Anyway, it is all possible to do with the Database Results Wizard (or Interface
Wizard, whatever it's called in FP2003.)

 

[Thanasis \(sch\)]

Hi,
thanks for your response.

Hi,
Between and And values aren't constant.They should be the values of the

textboxes of the form.
How this can be done?
Is something like that correct:

SELECT orders.[Date Ordered]
FROM orders
WHERE (((orders.[Date Ordered]) Between #request.form(fromdate)# And #request.form(todate)#);

Wishes
thanasis
You'd use a custom SQL query of the form

SELECT orders.[Date Ordered]
FROM orders
WHERE (((orders.[Date Ordered]) Between #6/1/2004# And #6/30/2004#));


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
J-Bots Plus 2002 87 components for FrontPage
http://www.websunlimited.com/order/Product/JBP2002/jbp_help_dir.htm
FrontPage Add-ins Since '97 2003 / 2002 / 2000 Compatible

Hello to everyone,

i am going to built a database application with DataBase Interface Wizard
and FrontPage 2003.
My DataBase will be Access 2000.

I want to create a search form with 2 text boxes with names FROMDATE and
TODATE.
I save the form as asp page SEARCH.ASP.
When the user clicks on submit button i want to call another asp page
RESULTS.ASP which filters the records from dates ranging from (FROMDATE ) to
(TODATE)?
I'd like to point out that inside a table there is a date field say(DATEX)

How this can be done in FP2003 and DIW?

Wishes
Thanasis

 

[Thanasis \(sch\)]

Hi,
thanks for your response.
Between and And values aren't constant.They should be the values of the
textboxes of the form.
How this can be done?
Is something like that correct:

SELECT orders.[Date Ordered]
FROM orders
WHERE (((orders.[Date Ordered]) Between #request.form(fromdate)# And

#request.form(todate)#);

Wishes
thanasis
============================================================================
======

Hi,

You'd use a custom SQL query of the form

SELECT orders.[Date Ordered]
FROM orders
WHERE (((orders.[Date Ordered]) Between #6/1/2004# And #6/30/2004#));


--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
J-Bots Plus 2002 87 components for FrontPage
http://www.websunlimited.com/order/Product/JBP2002/jbp_help_dir.htm
FrontPage Add-ins Since '97 2003 / 2002 / 2000 Compatible

Hello to everyone,

i am going to built a database application with DataBase Interface Wizard
and FrontPage 2003.
My DataBase will be Access 2000.

I want to create a search form with 2 text boxes with names FROMDATE and
TODATE.
I save the form as asp page SEARCH.ASP.
When the user clicks on submit button i want to call another asp page
RESULTS.ASP which filters the records from dates ranging from (FROMDATE ) to
(TODATE)?
I'd like to point out that inside a table there is a date field say(DATEX)

How this can be done in FP2003 and DIW?

Wishes
Thanasis