Select receipts from last month

[Baldeagle]

I'm struggling with DateAdd and DatePart and SerialDate!! In a query I want to select all records of gifts received in the previous calender month. My field is 'Date Paid'. Can someone please string together what I need? Would there be a problem if this is run in January? Does the new year create any problems?

[pbaldy]

Try these, using Date() instead of dte:

'First of previous month
'-------------------------------------
DateSerial(Year(dte), Month(dte) - 1, 1)

'Last of previous month
'-------------------------------------
DateSerial(Year(dte), Month(dte), 0)

[Baldeagle]

Thanks for the steer. I appreciate that. I may have a follow-up question but if so I will post it later.

[pbaldy]

Happy to help.

[Baldeagle]

If I use >=DateSerial(Year(Date()),Month(Date())-1,1) in a query to select all entries for the last calendar month how will this work at the beginning of January? Will the Year(Date()) be interpreted as the current year and as a result will not select any records from December? If so, how do I overcome this problem?

[jzwp11]

I think you are after something like this:

dateserial(year(dateadd("m",-1,date())), month(dateadd("m",-1,date())),1)

[Baldeagle]

Thanks for the guidance. That continues to do what I want it to do this month so I expect it will solve my problem in January. Thanks again.

[pbaldy]

The formulas were fine as they were:

?DateSerial(Year(#1/15/13#), Month(#1/15/13#) - 1, 1)
12/1/2012

[Baldeagle]

Apart from not understanding what "#1/15/13#" represents in your suggestion I'm not clear how the year element of the DateSerial formula can change to year-1 if it is being used in January. Maybe my understanding of the elements in the formula is flawed? Does it not look at the 3 elements of the current date and select the current year (unless there is a '-1' in which case it would select the previous year?

[jzwp11]

The #1/15/13# is just a fixed date in place of the date() function (date values have to be delimited with # signs in Access); Paul was illustrating that a date in January will yield the first of the December with the previous year. I too was surprised that having the-1 for only in the month term impacted the year as well. I will have to read up more on the DateSerial() function.

Thanks Paul for the new knowledge; it will come in handy in the future.

[pbaldy]

No problem. DateSerial is good that way; note the way you can use a Day argument of 0 to get the last day of the previous month, irregardless of how many days are in that month.

[Baldeagle]

Thanks to both of you. Sometimes logic is a bad thing if you don't fully understand the whole concept!!

[pbaldy]

No problemo!