Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010
    Posts
    172

    Datepart in SQL Where Clause won't show last week of 2012

    This sql expression was recommended to me as a way to pull invoices that where paid last week. I view this report every week.

    Today my report is blank.

    I beleive it is because of the new year and so it is looking for 2013 dates.....which I have nothing paid in 2013 yet.

    Is there a change I can make to the expression so that it will pull last week, regardless of year?
    Thanks so much!!!!

    ((Year([PDATE])*53+DatePart("ww",[PDATE]))=Year(Date())*53+DatePart("ww",Date())-1));

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,424
    so what does
    Code:
    ((Year([PDATE])*53+DatePart("ww",[PDATE]))=Year(Date())*53+DatePart("ww",Date())-1));
    evaluate to?

    my guess is its the year *53 that is screwing things up...
    I think you would be better off defining your date bracket based on specific cutoff ppints, or supply these values as parameters. if it were me Id use date parameters so you can make the query as flexible as required. when the bright young twonk in accountancy wants hjust yesterdays invoices or last months or last quarter its no skin of your nose just tell th eprat to put in the dates in your supplied form (that validates the dates are reasonable and use those dates in the query
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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