Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: Query not working properly

    So, I have a query that suppose to give me all the records that has Notice Date between today's date and 3 months from now. I think I'm not using the date() function or dateadd() function properly or something but results are all records that are today's date and beyond. All future records are showing. I've also tried BETWEEN Date() And (Date()+90) or something like it too but that didn't work either. Here's my query.

    Code:
    SELECT ForecastNotice.PropertyID, [Rent & Option].MonthlyRent, [Rent & Option].MonGrossRent, [Rent & Option].LeaseTerm, [Rent & Option].LeaseTermExp, [Rent & Option].LeaseTermNoticeDate, [Rent & Option].OptionToExtend, [Rent & Option].[1stExtNoticeDate], [Rent & Option].[2ndExtNoticeDate], [Rent & Option].[3rdExtNoticeDate], [Rent & Option].[4thExtNoticeDate], [Rent & Option].[5thExtNoticeDate], [Rent & Option].[6thExtNoticeDate], ForecastNotice.NoticeDate
    FROM ForecastNotice INNER JOIN [Rent & Option] ON ForecastNotice.PropertyID = [Rent & Option].PropertyID
    WHERE (((ForecastNotice.NoticeDate) Between Date() And (DateAdd("m",+3,Date()))));
    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tobabygu View Post
    ...suppose to give me ... Notice Date between today's date and 3 months from now.

    ... results ... today's date and beyond. All future records are showing.
    sounds like you got exactly what you wanted

    were you perhaps trying to get dates between 3 months ago and today? historic dates?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    I was trying to get dates between today and 3 months from today but instead I'm getting today, 3 months from today, year from today, 5 years from today etc...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's the datatype of the NoticeDate column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    103
    Text. Is that the issue? It should be set to date?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i believe that's the issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you have stored dates in a text / string / char column then make certain you aren't using the same dataa type for numeric columns (assuming you may want to use those columns for any form of mathmatics eg add, sum, subtract and so on OR use those columns in a where clause.

    why?
    ferinstance storing the numbers 1....13 as string when sorted appear as
    1,10,11,12,13,2,3,4,5,6,7,8,9
    as opposed to
    1,2,3,4,5,6,7,8,9,10,11,12,13
    if you use them in a where clause you are doing a string comparison not a numeric comparison.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2011
    Posts
    103
    Sweetness. Thank y'all. It's working now.

Posting Permissions

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