Can U SQL? Cos I need help

[drewcoster]

Hi All
I am quite new to databasing and SQL queries and I am trying to write 2 queries but I'm not having much luck.

Firstly I want to search a table called products for all items receieved less than 7 days ago, then I need another query to search for items released after 7 days but no longer than 30 days ago.

For the first one I've tried - Select* from Products Where DateReceived > DateAdd(d, -7, Date ()) - which I undestand is search for products that are received in the last 7 days but when I try this query in Access 2000 it pops up a parameter box for 'd' and I have no idea what that means or what to do to get the database to recognise this query.

I'd really appreciate some help on this.

Thanks a lot
Drew

[Spydah]

I am not real familiar with Access and it's subset of SQL, but something like this should work:

Select * from products Where DateReceived >= (sysdate - 7)


Select * from products Where DateReceived > (sysdate - 30) and DateReceived < (sysdate - 7)

[shane]

I think you'll need double quotes around the 'd', as the DateAdd function, in Access, uses the VB syntax rather than SQL syntax (which has no quotes).

Try the following queries.

SELECT 	*
FROM	Products
WHERE	DATEDIFF("d", DateReceived, Date) <= 7

SELECT 	*
FROM	Products
WHERE	(DATEDIFF("d", DateReleased, Date) >= 7
		AND DATEDIFF(day, DateReleased, Date) <= 30)

[drewcoster]

Hi Shane
Thanks for replying to my question. I did try the code you inserted for me and a window asking for a date parameter popped-up so I put in todays date and it worked perfectly.

But When I put this query on the net will the date automatically be taken from todays date or do I have to put something like Spydah suggested and use a 'sysdate' type of code.

I really appreciate you help and insight into this.

Thanks
Drew

[mtffafl]

When you put it on the net it won't work that way...

You need to hardcode the date into your SQL query each time the connection is made...

i.e.
SQL = "SELECT * FROM Products WHERE DateReleased < #" & DateAdd(d, -7, Date ()) & "#"

And then query on the string (SQL) you have just created.

I can't remember the exact way to put the date in off the type of my head and it also differs depending on where you are in the world (I'm in Australia so my dates are probably backwards to yours).

If it doesn't work surrounded by the # signs then try replacing them with single quotes etc.

Matt

[shane]

drewcoster,

Apologoises, I don't use Access much so wasn't sure what the Jet SQL function is for getting the current date. Try the following modified queries.

SELECT 	*
FROM	Products
WHERE	DATEDIFF("d", DateReceived, Now) <= 7

SELECT 	*
FROM	Products
WHERE	(DATEDIFF("d", DateReleased, Now) >= 7
		AND DATEDIFF("d", DateReleased, Now) <= 30)

Originally posted by mtffafl
When you put it on the net it won't work that way...

You need to hardcode the date into your SQL query each time the connection is made...

i.e.
SQL = "SELECT * FROM Products WHERE DateReleased < #" & DateAdd(d, -7, Date ()) & "#"

Most flavours of SQL are perfectly capable of getting the current datetime, so you don't need to do as you say and build the sql in code, as you can embed the appropriate function call in the sql.

[drewcoster]

Shane

You are awesome - the 7 day code worked like a dream, but (I'm sorry to keep on!) the second query made a 'day' parameter box pop-up but that's okay for now.

I wondered if I could pick your brains on the next stage of this query?

I want to search the database using the code you showed me
SELECT *
FROM Products
WHERE DATEDIFF("d", DateReceived, Now) <= 7

But instead of * I want to choose SELECT name, title WHERE style = "dance" but how do I write that in when I already have a WHERE statement that checks the Recieved date of the song I am looking for.

Sorry to ask so many questions but I really do appreciate you expertise on this.

Thanks a million
Drew

[shane]

Just append an 'AND' clause to the statement. If you're getting the style from a web page form you'll need to build the sql string in code

'SQL
SELECT 	P.Name,
	P.Title
FROM	Products P
WHERE	DATEDIFF("d", P.DateReceived, Now) <= 7
AND	P.style = 'dance'

'In asp it'd look something like this
strStyle = Request.Form("style")
strStyle = Replace(strStyle, "'", "''")


strSQL = "SELECT 	P.Name, " & _
			"P.Title " & _
			"FROM	Products P " & _
			"WHERE	DATEDIFF("d", P.DateReceived, Now) <= 7 " & _
			"AND	P.style = '" & strStyle & "'"

'2nd query modified (I hadn't changed 'date' to "d")
SELECT 	*
FROM	Products
WHERE	(DATEDIFF("d", DateReleased, Now) >= 7
	AND DATEDIFF("d", DateReleased, Now) <= 30)

[drewcoster]

You are the man!!!!!!!!!

Shane that has helped me so much. Thank you for your knowledge and time I really do appreciate your help.

Drew

[shane]

Glad I could help drew.

You may want to have a look at www.w3schools.com as they've some good intros to sql/asp/ado.