Hi everyone, I'm having some trouble with a query. I have a history table where one of the fields is a date stamp. My query takes a date from the user, and I want the query to return all records from the history table with a date stamp equal to or less than the last day of the last month.
Therefore, if the user enters February 15, 2011 when prompted for a date, I want the query to return all records dated January 31, 2011 or earlier.
I've tried two approaches. The first was simply using DateAdd("m",-1,[UserEnteredDate]) but then for February 15, 2011 I get records from January 15, 2011 and back, which is not good. My second approach was to separate the date into month and year parts, which is fine for filtering the year because it is sequential, but I couldn't think of a way to filter the month, since I can't just use the criteria <=Month([UserEnteredDate]) because then it won't go back to return all months of the previous year.
Any ideas? :/
Therefore, if the user enters February 15, 2011 when prompted for a date, I want the query to return all records dated January 31, 2011 or earlier.
I've tried two approaches. The first was simply using DateAdd("m",-1,[UserEnteredDate]) but then for February 15, 2011 I get records from January 15, 2011 and back, which is not good. My second approach was to separate the date into month and year parts, which is fine for filtering the year because it is sequential, but I couldn't think of a way to filter the month, since I can't just use the criteria <=Month([UserEnteredDate]) because then it won't go back to return all months of the previous year.
Any ideas? :/
Comment