Access Date Issue

[KJP0422]

In excel if I want to find the last day of the current month I can use the following formula

=EOMONTH(TODAY(),0)

I have an access database with a table that contains , Accounting Date, Quantity. I would like to use a formula to generate a rolling 12 month graph. With the current month being the 12th month.

Current Static Formula

( Mar Qty: Sum(IIf(Month([tbl_Stores]![Accounting Date])=3,[tbl_Stores]![Quantity],0))

What functions / formulas would I use to generate a dynamic rolling 12 month query

Thanks in advance

 

[apr pillai]

Find end-of-month date with this formula: =DateSerial(Year(Date()),Month(Date())+1,1)-1

 

[xenou]

Yes, that is how I do it too. Also (with a slight variation):
DateSerial(Year(Date()),Month(Date())+1,0)


For example,

SELECT 
    Table1.Field1, 
    DateSerial(Year([Field1])-1,Month([Field1]),1) AS BeginDate, 
    DateSerial(Year([Field1]),Month([Field1])+1,0) AS StartDate
FROM 
    Table1;

Returns the one year period from Feb1 2015 to Feb29 2016:
2016-02-08 | 2015-02-01 | 2016-02-29

 

[apr pillai]

Thank you xenou for correction.

If you hate to type this lengthy expression and would love to use your EOMONTH() Function instead then copy and paste the following VBA function into a Standard Module and save it.

Public Function EOMONTH() As Date
  EOMONTH = DateSerial(Year(Date), Month(Date) + 1, 0)
End Function

 

[KJP0422]

Thanks all, but I am still having problems with this formula. My goal is to sum the quantity by item for a rolling 12 month period. So there will be an IIF statement included as well. I entered this based on the replies:

EOM: DateSerial(Year([tbl_Stores]![Accounting Date]),Month([tbl_Stores]![Accounting Date])+1,1)-1 It is returning the correct EOM for the issue date, How would I get August minus 1 month

 

[xenou]

I think you need a BETWEEN. You are speaking of a range of dates here, not one date. It's not clear why you are getting august minus 1 month either, because you should be getting an EOM date, not a date minus 1 month.

Please provide sample data and expected output.