asked on

90 days before current date

I am trying to write a report that needs to have a date range of 90 days before the current date all the way through the last day of the year. Getting stumped on how I would do 90 days before the current date?

Edward Wood

you can use a date part(days, todays date) >90 and the same for the end of the year

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Tsioumpris

between #" & DateAdd("d",-90,Date()) & "# AND #" & Cdate("12/31/" & Year(Date()) & "#"

Open in new window

ASKER CERTIFIED SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HOTWATT

ASKER

Thanks everyone for the help. Paul you explained it perfectly! One more question. If I want the last date to be lets say next year instead of being 12/31/17 I would want it 12/31/18. How do I go about doing that?

John Tsioumpris

between #" & DateAdd("d",-90,Date()) & "# AND #" & Cdate("12/31/" & Year(Date())+1) & "#"

Open in new window

Paul Cook-Giles

If I want the last date to be lets say next year instead of being 12/31/17 I would want it 12/31/18. How do I go about doing that?

You'd still use the DateSerial function, but instead of using the current year's integer, you'd add 1 to it:

DateSerial((DatePart("yyyy", date())) + 1, 12,31)

Open in new window

:)
Paul

Gustav Brock

The simple criteria would be:

Where [YourDateField] Between DateAdd("d", -90, Date()) And DateSerial(Year(Date()) + 1, 12, 31)

or, for three months:

Where [YourDateField] Between DateAdd("m", -3, Date()) And DateSerial(Year(Date()) + 1, 12, 31)

/gustav

First, you should never use strings for dates.

Gustav Brock

John, you are overcomplicating it by casting to strings (which further will fail in an international environment) mixing up strings and date values, and concatenating back in the SQL. Indeed for the year where you cast Year(Date()) to a string, concatenate with another string, convert to a date value which then is casted to a string, and finally concatenated to a string expression for the date value.

between #" & DateAdd("d",-90,Date()) & "# AND #" & Cdate("12/31/" & Year(Date())+1) & "#"

Should be:

between DateAdd('d',-90,Date()) AND DateSerial(Year(Date()) + 1, 12, 31)

/gustav

John Tsioumpris

In case of International Formats you can always use Format(StringDate,"mm/dd/yyyy") and bypass the issue...but i will keep the DateSerial solution for future situations

Gustav Brock

Well, not always. It will fail for FindFirst with ADO.

The only completely safe format is the ISO sequence: yyyy-mm-dd

/gustav

John Tsioumpris

Interesting Gustav...i will keep this in mind