How to get dates before the last day of last month?

[Coolboy55]

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? :/

[Rabbit]

DateAdd("d", -1, CDate(Month(#2/15/2011#) & "/1/" & Year(#2/15/2011#)))

[yarbrough40]

Easier yet

"MyDate" being your user inputted date

 DateAdd("d", -1, Format(MyDate, "M/1/YYYY"))

ROCK!

[ADezii]

In the Criteria Row of your Date Field, Copy-N-Paste the following Statement (will prompt User for Date):

<=(DateSerial(Year([Enter Date]),Month([Enter Date]),1)-1)