Query criteria to check if record date field is not within last month 1

[08211987]

Hi,
Query syntax gets me everytime. I inherited a database that will run on a schedule the 2nd day of the month and pulls records with this criteria for a date field: Between Date()-33 And Date(). Subsequently it will pull all records from previous month as well as a few from 2 months ago and this month. The previously developer didn't mind because he replaced the table each month and ignored the extra records. Now I want to build a history and don't want duplicate records.

I would like to change that criteria to use today's date to see if the date field was within last month. I'm struggling with how to code that criteria, as an example I tried this: Not =Month("Date")-1 which is really off I know. Ideas?
Thanks!

 

[08211987]

Sorry I guess I wasn't as clear as I needed to be. What I ultimately want to do is to pull only the records where the record date field falls within last month and I will run it the the 2nd day of the next month. In my previous example I was planning on deleting the records I didn't want but if I code the correct syntax in the original query where the records are being pulled I wouldn't have to delete. Make sense?

 

[MajP]

This function will give you the first day of the previous month.

Public Function GetFirstOfLastMonth(TodaysDate As Date) As Date
  Dim dt As Date
  dt = DateSerial(Year(TodaysDate), Month(TodaysDate), -1)
  dt = DateSerial(Year(dt), Month(dt), 1)
  GetFirstOfLastMonth = dt
End Function

Beween GetFirstOfLastMonth(Date()) and Date()

 

[PHV]

What about this ?

SELECT ...
FROM ...
WHERE Month([yourDateField])=Month(Date())-1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps:
WHERE Format([yourDateField],"yyyymm")=Format(DateAdd("m",-1,Date()),"yyyymm")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[08211987]

Perfect PH! Thanks!