Between 21st last month to 21st this month

[Adam]

Hi All,

I've got a table which has YTD information. Around the 21st of each
month a auditor will need to pull 1 months worth on info from it.

In the query I've like to to search between the 21st last month up
until the 21st this month. This is rolling and will be selected each
month around the 21st.

Does anyone know a criteria formula that could help me do this?

Adam

 

[Adam]

Its ok I've found it, see below criteria:

Between CDate("21/" & Format(Now(),"mm")-1 & "/" & Format(Now(),"yy"))
And CDate("21/" & Format(Now(),"mm") & "/" & Format(Now(),"yy"))

 

[Alex White MCDBA MCSE]

Are you sure that works for Jan as well as other months. Because I think you
need to check if the current month is Jan if so deduct a year and the month
= 12 etc.

 

[Douglas J. Steele]

As Alex points out, that will fail in January, because you'll end up with
CDate("21/0/2006")

Fortunately, the DateSerial function can handle this for you:

Between DateSerial(Year(Date()), Month(Date()) - 1, 21)
And DateSerial(Year(Date()), Month(Date()), 21)

It will correctly translate DateSerial(2006, 0, 21) to 21 Dec, 2005.

 

[Adam]

Yes I knew it wouldn't work in January but I'm not experienced enough
to correct it

Thank you for the formula Doug, thats really cool