select *from date range

[techskool]

Im trying to pull all records added to my database in the last 14 days.

Im trying:

sql="SELECT * FROM stock WHERE DateLogged BETWEEN '"&DateAdd("d",-14,Date)&"' AND '"& Date &"' AND InStock = 'Yes' ORDER BY Artist, Title, Label"

ive response.write out the values of the 2 dates to check they are 2 weeks apart and in the same date format but its not working

any ideas guys

Dave

 

[robbie59]

I've run into this one before I seem to remember I had to put wildcard in front of and past the date range fields to make it work such as '"'%'+ &DateAdd("d",-14,Date)&'%'"' AND '"& Date &"' AND InStock = 'Yes' ORDER BY Artist

something like that if I remember

If knowledge were power I would be a AAA battery!

 

[TwoOdd]

Try adding ():

sql="SELECT * FROM stock WHERE ((DateLogged BETWEEN '" & DateAdd("d",-14,Date) & "' AND '" & Date & "') AND InStock = 'Yes') ORDER BY Artist, Title, Label"

If you still have problems, type response.write sql & &quot;<BR>&quot; after the line above and run the page. Copy the sql statement that's printed to the browser and run it through your database. This should help you trouble shoot the sql statement.


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[techskool]

Thanks for the suggestions guys

Ive tried both, but to no avail

Im writing out the sql on the page and all i get is

SELECT * FROM stock ORDER BY Artist, Title, Label

...so its totally ignoring the WHERE clause and the BETWEEN and date ASP.

stumped?

 

[TwoOdd]

Try copying this and pasting it into notepad (get rid of the html formating) and then copy it from notepad and paste it directly into your code. Then run the page again and see what prints to the browser.

sql = "SELECT * FROM stock WHERE ((DateLogged BETWEEN '" & DateAdd("d",-14,Date)
sql = sql & "' AND '" & Date & "') AND InStock = 'Yes') ORDER BY Artist, Title, Label"
Response.Write sql & &quot;<BR>&quot;

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[techskool]

Its still doing the same!!

oh well, thanx for trying

Dave

 

[TwoOdd]

Sorry I couldn't be more helpful. The code works just fine on my machine(Windows 2000 and IIS). Not sure if the webserver or operating system has anything to do with your problem.

Good Luck,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[Chopstik]

techskool,

Have you tried to write out your sql statement? Then copy and paste into your Query Analyzer to see if you can identify the problem. Also, what kind of db are you using?

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[techskool]

Ive written it out and its missing the date area of the query so im gonna try a query analyzer.

ps im using MS Access 2000 (Groan)

Thanx again

Dave

 

[Chopstik]

When you say it's missing the date area of the query, you mean that your sql query looks like this:

SELECT * FROM stock WHERE DateLogged BETWEEN '' AND '' AND InStock = 'Yes' ORDER BY Artist, Title, Label

If so, then it means that the ASP variables that you are using in your query are not returning results and you need to verify that you have values for those variables. After re-reading your original post, it seems like they are, but I cannot ascertain for certain per that post.

If they are returning values, then pasting it into Access and testing in Access directly should help to find the problem. Good luck.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[techskool]

Ive messaed about with the syntax a little, and it seems to be working, by that i mean when i write out what the sql value is it is (when run today):

SELECT * FROM stock WHERE DateLogged BETWEEN '25/09/2003' AND '09/10/2003' AND InStock = 'Yes' ORDER BY Artist, Title, Label

Can anyone spot why this might not be pulling dates between the specified ones. It seems to be just pulling all the records in the db.

Does SQL understand the BETWEEN statement when it is applied to dates, as its not like a simple integer range?

Thanx again for everyones suggestions

Dave

 

[Chopstik]

I don't normally work with Access, but doesn't it prefer dates formatted like:

#25/09/2003#

Also, did you try your code in Access itself to ensure that it works in your application?

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein