Hello All. <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>

Hopefully someone can help me because I'm stumped. Can't find any info on the net about this presumably simple query...


OK here goes -

I have a web based database (Access/IIS/ODBC) which is used to store invoice details. Every record has a date field which has a default value of Now() so that when the record is created, the field is populated with the correct date.

I'm trying to build report pages which lists the contents of the database.

I have made a page which lists all the records - <img src=../images/dmxzone/forum/icon_smile_cool.gif border=0 align=middle>
I have made a simple form with Month and Year values which posts to a page which can pull the specific records out of the database by matching the Month and Year from the database - all good.

Now I need help! I'm trying to build a reports page which can list all records between 2 dates - I've built a page which requests two values - 1 for 'start date' and one for 'end date'. this page then posts these 2 values to a reports page which attempts to select all records from the database where the date is within the 'start' and 'end' values. It sounds easy enough - but i've been on this for a few weeks now and need some help.

The closest i've come with my query so far is :

SELECT *
FROM tbl_web_budget
WHERE invoice_date &gt; ED_StartDate

Where the variable 'ED_StartDate' is defined as :

DateValue(Request.Form("report_start_date")


Obviously I'm doing something W-R-O-N-G but am undoubtedly going to be faced with this problem again at some point with different web apps.

Can someone here please sort me out or give me a push in the right direction?

Many thanks.

<img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

Cheers

check this out:
www.1keydata.com/sql/sqlbetween.html

you could do something similar and for your page to display your dates you could use to pull down menus with the available dates, one for initial date and other with end date and bring

The sql would look something like this:

SELECT *
FROM tblDates
WHERE Date BETWEEN 'param1' AND 'param2'

something like that. I don't have my DW at habd to get the rest. so hopefully someone else can add to that.
Cheers,

J.

Thanks for the link - it gave me the push in the right direction i needed. <img src=../images/dmxzone/forum/icon_smile_cool.gif border=0 align=middle>

I'd been trying various similar things to this, but my main stumbling block was data type mismatches when working with dates. Using the VBScript DateValue function in the query does the trick.

For anyone thats interested, the method for creating a recordset (VBScript ASP/IIS/Access) where records fall between 2 submitted dates is similar to this:


Value01 = Request.Form("report_start_date"
Value02 = Request.Form("report_end_date"

SELECT * FROM table_name WHERE date_field BETWEEN DateValue(Value01) AND DateValue(Value02)



Thanks again for the link... <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>