Getting records based on week

Results 1 to 5 of 5

Thread: Getting records based on week

  1. #1
    Join Date
    Dec 1969
    Posts
    106

    Default Getting records based on week

    I&#039;m trying to get records from a SQL Server database based on the current week. Can someone tell me what I am doing wrong here?<BR><BR>SQL1 = "SELECT * FROM tblGoals WHERE " & DatePart("ww", DueDate) & " = " & DatePart("ww", date, 1, 1) & ""<BR><BR>Tha name of the column in the table is called DueDate. I&#039;m not getting an error message, I&#039;m just not getting any data back. <BR><BR>P.S. There is data in there for the current week.

  2. #2
    Join Date
    Dec 1969
    Posts
    96,118

    Default RE: Getting records based on week

    You are doing *BOTH* DatePart calculations in VBScript!<BR><BR>Assuming that you don&#039;t have a DueDate variable in VBS, that is getting you the week of 30 December 1899! (Presumably either 52 or 53.)<BR><BR>You really don&#039;t want to do *ANY* of this in VBScript!<BR><BR>SQL1 = "SELECT * FROM tblGoals WHERE DatePart(ww, DueDate) = DatePart(ww, getDate() )"<BR><BR>Look here:<BR>http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2mic.asp<BR><BR>

  3. #3
    Join Date
    Dec 1969
    Posts
    53

    Default So how would this be done in Access?

    Im trying to do something similar.

  4. #4
    Join Date
    Dec 1969
    Posts
    96,118

    Default The same, except...

    ...you use DATE() instead of GETDATE().<BR><BR>Oops...not true! With Access, you have to put &#039;ww&#039; instead of just ww.<BR><BR>Okay, let&#039;s write it out:<BR><BR>SQL1 = "SELECT * FROM tblGoals WHERE DatePart(&#039;ww&#039;, DueDate) = DatePart(&#039;ww&#039;, Date() )"


  5. #5
    Join Date
    Dec 1969
    Posts
    106

    Default RE: The same, except...

    Thanks Bill!

Posting Permissions

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