FP2003 Using DRW to pull a Date Range from an Access database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an eForm that successfully sends data an Access database
created through FP. I am able to pull -ALL- results into an asp page, which
it shows as a table. I also created a version using more options to set the
criteria so that any records that had an entry in this newly-added column
that shows the date would be used to filter; however, what I am trying to do
is create a page that would have beginning and ending date fields to select a
date range, driven off of the column that has the date in it.

I realize that I would have to have 2 pages, one that has the beginning and
ending date fields, and that I would somehow have to "send" the filtering
statement to the results page and another that has the results. I have
followed the article from spiderwebwoman on the subject, but couldn't get it
to work in my implementation.

My question is, how would I go about creating an user interface that allows
them to select a date range of data to view and shows them all columns for
the date selected? I'm not really sure where to begin here and could really
use some advise. Also, while learning asp code is on my list of things to do
in the forseeable future, I am not a programmer. I do recognize the
importance of understanding asp and html code in the field. I just haven't
been able to devote the time to this pursuit yet. So, please be specific and
advise wherever possible when something can be done entirely through FP. (Be
gentle) Any advice the group can provide is greatly appreciated. Thanks in
advance for your help.
 
Hi Bill,

The SQL to pull that type of information is:

SELECT * FROM table WHERE date BETWEEN #01/01/2006# and #01/17/2006#

To allow for the date information to come from a form field you'll need to utilize the file name in double colons.

SELECT * FROM table WHERE date BETWEEN #::startdate::# and #::enddate::#
 
Mike,
Thanks for the response. As noted in my query, I am not a programmer, so I
would have absolutely no idea where to insert the code submitted. I guess
this means there is no way to enter this through DRW, or are you saying this
would be the info to submit in a custom SQL query?
 
Andrew,
Thanks for clarifying.

Mike,
Thanks for the custom code. I tried it and received an error indicating
that the server was unable to process a database request. I will provide
some additional information, in hopes that someone can help clarify the
specific line of custom code.

My database is called DailyStats1. My table is called Results. Given the
code provided by Mike, I came up with:

SELECT * FROM ::Results:: WHERE date BETWEEN #::startdate::# and #::enddate::#

I also tried:

SELECT * FROM Results WHERE date BETWEEN #::startdate::# and #::enddate::#

Neither worked. I am trying to drive off of a column in the Results table
that has the date. This column is labeled "Date_of_Stats". I am having them
manually enter the date in a specific format instead of using the date/time
stamp saved field.

Any guidance the group can provide is appreciated. Thanks in advance.
 
Hi Bill:

Try this:

SELECT * FROM Results WHERE Date_of_Stats BETWEEN #::startdate::# and
#::enddate::#
 
Ok. Tried Kathleen's suggestion and got same server error. Just for
reference, I am adding this as a custom query through the DRW. Any other
thoughts or suggestions as to what I'm doing wrong?
--
Bill M


Kathleen Anderson said:
Hi Bill:

Try this:

SELECT * FROM Results WHERE Date_of_Stats BETWEEN #::startdate::# and
#::enddate::#


--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
Click to expand...
 
Bill:
I just created a database and ran the DBRW against it. I defined
Date_of_Stats as a General Date in Access. The DBRW created the following
SQL, which works:

SELECT * FROM results WHERE (Date_of_Stats > '::startdate::' AND
Date_of_Stats < '::enddate::')

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
 
Back
Top