Hello All.

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



Cheers

Always found it a pain getting these working. try using the # symbol around your date variables - or you may need to look into doing a DateTime conversion. If you're using Access an easy way to check the syntax is to build it as a query in Access then copy the SQL view data.


JobsRS.Source = "SELECT j.ID AS JobID, JobFrom, JobTo, JobDate, JobTime, JobType, JobStatus, JobNotes, JobArea, CustomerID, c.Company, Date_Added, areaname, JobPrice,JobInvoiceNo,JobDriver FROM tbl_Jobs j, customers c, areas a WHERE c.ID = j.CustomerID AND JobArea LIKE '"+ JobsRS__var1.replace(/'/g, "''" + "' AND a.areaname = j.JobArea AND <b>(((j.JobDate)=#"+ JobsRS__var4.replace(/'/g, "''" + "#)) </b> ORDER BY JobDate, JobTime";

that's using an Access database and this is the same kinda thing on a SQl database

Recordset1.Source = "SELECT * FROM dbo.SebInvoices WHERE InvoiceDate BETWEEN CAST('"+ Recordset1__MMColParam.replace(/'/g, "''" + "' AS DATETIME) AND CAST('"+ Recordset1__var2.replace(/'/g, "''" + "' AS DATETIME)";

Hope that pushes you in the right direction

Cheers

Seb