var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Date Formula
I am wanting to run a query with a strange criteria. The query returns a list of invoices. If today is 16th June, I want the query to return all invoices dated 30th April and before.
When we get to July, I want the query to return all invoices dated 31st May and before.
How would I do this in the criteria of a query?
start with today's date -- DATE()
then find the day of the month and subtract 1 -- DAY(DATE())-1
subtract that number of days from today's date -- DATEADD("D",-(DAY(DATE())-1),DATE())
subtract one more month -- DATEADD("M",-1,DATEADD("D",-(DAY(DATE())-1),DATE()))
you now have a date for the first day of the previous month
example: since we're in june, this gives you the 1st day of may
then just write your query to accept all dates that are earlier than that
note that this formula works perfectly well going backwards over year boundaries, too
WHERE datecolumn < DATEADD("M",-1,DATEADD("D",-(DAY(DATE())-1),DATE()))
Thank you for this.
How would I use this in the criterea field in my query design view?
Ignore me, it works a dream. Thank you.
And thank you for the explanation as well. That really helped.
just type the "less than" operator and copy/paste the expression
Originally Posted by Yorkshire Lad