Date ranges in database results with a defined variable

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I'm trying to create a more efficient way of changing the
date range criterion I use in database results
criterion. I frequently have to change the database
results properties across a series of pages to alter the
dates of articles displayed on those pages. The custom
query I currently use is:

SELECT * FROM Articles WHERE ("Article Date" Between
#7/1/2003# AND Date( )) ORDER BY "Article Date" DESC

I want to replace the hard coded date #7/1/2003# with a
variable that I can set in the source of the page, or
better yet, on a single include page that would be pulled
into all the pages using this same database query. I
have done this successfully with other types of queries,
but I can't figure out the right syntax when it comes to
the date property.

Here is how I am defining the variable in HTML view:

<%
Dim MyDate
Date = "9/1/2003"
%>

And here are several variations of the syntax I tried to
use for my defined variable. I placed and tested each of
these within the SQL query in the HTML view. All result
in errors when I try to view my page in a browser.

&quot;::MyDate::&quot
""::MyDate::""
'%::MyDate::%'
#::MyDate::#

I believe that I have the syntax wrong where I am trying
to insert my defined variable into the SQL query in HTML
view. Can anyone provide help? Thanks.
 
One possibility, certainly, is to set up an Access query
named CurArticles, ala:

SELECT * FROM Articles WHERE "Article Date" >= >#7/1/2003#

Then, use the CurArticles query rather than the Articles
table as the DRW's datasource.


Otherwise, if you're running the DRW page from a form,
create a file named begdate.inc that contains this code:

<input name="begdate" type="hidden" value="7/1/2003">

Then, ssi-include this page inside the form, and
configure the DRW to use begdate as a query field.


Otherwise, create a page named begdate.htm that contains
this code:

<html>
<body>
<script>begdate = "7/1/2003"</script>
</body>
</html>

Use an Include Page component to include this <body>
section in the page that links to your DRW page, and then
change the hyperlink to:

<a href="drwpage.asp?begdate=<script>document.write
(begdate);</script>">

and again use begdate as a query field in the DRW itself.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*
 
Thank you for all of the detailed suggestions.
Unfortunately, due the large number of pages I have that
use slightly different queries, it would be cumbersome to
create and modify a large number of queries in Access as
you outlined in your first suggestion. Also, I can't use
the second two suggestions exactly as written because I
get a lot of traffic to the pages in question from search
engines and places where links to the pages are already
on other people's sites, so I can't limit access to my
pages from a form or other links that I can fully
control.

I tried to modify the approach in your third option so
that I wouldn't have to use a different URL for the
page. I defined the variable with a <script>begdate
= "7/1/2003"</script> tag in a page that is then included
in the DRW page, and I used begdate as a query field in
the DRW itself. Unfortunately, I get errors in the DRW
region when I view the page.

Placing begdate in the DRW query results in the
message "Too few parameters. Expected 1." Using ::
begdate:: results in the message "One or more form fields
were empty."

I think I am still missing something some key characters
either where I define the variable in the <script> tag,
or when I place begdate in the DRW. It's probably some
basic syntax issue that I just don't understand. Any
suggestions?
 
Try

<%
begdate = "7/1/2003"
%>

Then in your query use

"SELECT * FROM Articles WHERE Article_Date >= #" & begdate & "# "

Avoid using spaces, etc. in field names.
--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================
 
paul said:
Thank you for all of the detailed suggestions.
Unfortunately, due the large number of pages I have that
use slightly different queries, it would be cumbersome to
create and modify a large number of queries in Access as
you outlined in your first suggestion.
Click to expand...

All the pages that use the same Access table can use the same Access
query.

You would, however, need to modify each page to access the query
instead of the table.

The DRW can query an Access query, you know.

Jim Buyens
Microsoft FrontPage MVP
(e-mail address removed)
http://www.interlacken.com
Author of:
*------------------------------------------------------*
|\----------------------------------------------------/|
|| Microsoft Office FrontPage 2003 Inside Out ||
|| Microsoft FrontPage Version 2002 Inside Out ||
|| Web Database Development Step by Step .NET Edition ||
|| Troubleshooting Microsoft FrontPage 2002 ||
|| Faster Smarter Beginning Programming ||
|| (All from Microsoft Press) ||
|/----------------------------------------------------\|
*------------------------------------------------------*
 
Thomas, thank you for your reply. Unfortunately, I'm
still stuck. I can see that the variable setting format
you suggested is working for me. I confirming it with a
response.write command on the page. However, the query
portion still causes errors.

Due to a known and unfixed FrontPage DRW bug, I cannot
place #" & begdate & "# " directly into the FP DRW field
because it generates errors and will not allow me to save
the change.

So in HTML view, I have tried to paste this into the gray
s-sql= section. Since this always needs variations on
such characters as the quotation mark, I tried it the
following ways:

#&quot;& begdate &&quot;#
#&quot; & begdate & &quot;#

When I save and preview the page using either of the
conditions above, I get a Database Results Error that
says "Syntax error in date in query expression." and
where the query is displayed in the error message it is
cut off where the begdate condition is - it only reads #'
and ends.

Any further suggestions that I can try to make this query
work?

Also, does anyone know if FP2003 is more compatible with
writing custom queries?
 
Paul,

Have you consider learning to code ASP manually, as what you are attempting
do would work with the example code I provided?

--

==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)

FrontPage Resources, Forums, WebCircle,
MS KB Quick Links, etc.
==============================================
 
Thomas:

Thank you for your prompt reply. I hadn't considered
trying to code ASP manually, but considering the
limitations I'm bumping into it sounds like a good idea.
It may be that I'm trying to use FrontPage to do
everything when I should just use it for certain things
while directly writing ASP code where that is more
efficient. Thanks for your feedback.

Paul
 
Back
Top