This one seems so easy having search the forums but I still have problems.

I have a DB containing leaflets and corresponding info e.g. date received from supplier

I want users to be able to search for leaflets by date received from supplier so I have an input form with fields DateStart and DateEnd. This is submitted to a results page displaying recordes in the range. However I keep getting the odd record that is outside the required range?!?

The SQL is as follows:

SELECT *
FROM Leaflets
WHERE DateReceived BETWEEN #START# AND #END#

START and END being the variables from the input form.

Any ideas why I'm getting the odd result out od the range?

Thanks

Stefan

UD4 | ASP | VBScript | ACCESS | IIS5

I assume that

a. You are using MS Access
b. Where you have #Start# you have this defined in the variables section below requesting the info from the url

When you say odd results, what do you get? What is missing?

Regards
Vince

Response.write("The best line of code you can ever use"

VBScript | ASP | HTML | SQL | Oracle | Hosting

Hi Vince

Yes I am using MS ACCESS and yes I am defining the variables.

When I say 'odd' results I mean that when searhing for, say, leaflets received in August 2002, the user puts in start date 01/08/2002 and end date 31/08/2002.

The results then include all records in August but also a couple of bogus ones eg 15/01/2002


UD4 | ASP | VBScript | ACCESS | IIS5

I think Odd was a good description of the problem!

Can you copy the sql code that is actually on the page?

Regards
Vince

Response.write("The best line of code you can ever use"

VBScript | ASP | HTML | SQL | Oracle | Hosting

Hi Vince

The SQL is as follows:

SELECT PubID, Title, CLCI, LeafletID, UpdateDue, DateReceived, AdditionalNotes
FROM Pubs
WHERE UpdateDue BETWEEN #DATESTART# AND #DATEEND#

DATESTART and DATEEND are variables that have been declared

so why am I getting results outside the range?

UD4 | ASP | VBScript | ACCESS | IIS5

This is doing my head in! It's not the SQL, thats fine. It's the date format.

I'm inputting DDMMYYY, this is how the date appearsd in access. I'm also requesting the date DDMMYY, but this is where the problem occurs. Having tested it MMDDYYY it's fine!

Any know how to resolve this? I've tried LCID but to no avail...

UD4 | ASP | VBScript | ACCESS | IIS5