SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Location
    Luton, UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can U SQL? Cos I need help

    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
    'As a computer, I find your faith in technology amusing'

  2. #2
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)
    ~Spydah
    welcome, to my web
    /\88/\

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

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

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Location
    Luton, UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by drewcoster; Jan 18, 2002 at 14:40.
    'As a computer, I find your faith in technology amusing'

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2000
    Location
    Australia
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.
    Last edited by shane; Jan 21, 2002 at 04:00.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Location
    Luton, UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by drewcoster; Jan 20, 2002 at 17:53.
    'As a computer, I find your faith in technology amusing'

  8. #8
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    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)

  9. #9
    SitePoint Enthusiast
    Join Date
    Jun 2001
    Location
    Luton, UK
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    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
    'As a computer, I find your faith in technology amusing'

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •