I'm trying to select only the records in which their date falls from the current date to the end of the month three months from now. Currently there are two dates in the table matching the query:
Judy: 5/17/09 asdf: 8/9/09 DateVar current = Date(CurrentDateTime); //takes the time off DateVar ThreeMonthsAway = Date(year(CurrentDateTime), month(CurrentDateTime)+4, 1) - 1; // month+4, then -1 days to get last day three months away > current AND < ThreeMonthsAway The problem is, it returns no results. If I take off the second part of it, I get both results but I only want the dates within three months. What am I doing wrong?
323 4 4 silver badges 18 18 bronze badges asked May 14, 2009 at 14:34 31.6k 64 64 gold badges 184 184 silver badges 271 271 bronze badgesSo your code must read (unfortunately I don't have CR here to test it):
DateVar current := Date(CurrentDateTime); //takes the time off DateVar ThreeMonthsAway := Date(year(CurrentDateTime), month(CurrentDateTime)+4, 1) - 1; // month+4, then -1 days to get last day three months away > current AND < ThreeMonthsAway Perhaps because you are adding 4 months - which is into the future - instead of subtracting them?
You question asks for 'end of month three months ago', but the sample dates are (as of 2009-05-14) into the future, so it could be that I'm misinterpreting your question, or your question is mis-written.
Have you printed out the value of ThreeMonthsAway to see what it evaluates to? Are you sure the 3-argument form of Date() takes the arguments in the year-month-day order (it is plausible, but I've also encountered systems where the order is month-day-year)?
Some of the points made here have been addressed by fixing the question (or the comments have been rendered irrelevant because the question was fixed since the comments were made).