Date Issue

[pungigi]

Is there a way to run a report on 3-13 for January data without manual intervention???

Currently I have it so I can run (LastFullMonth) and it returns Feb data but we are changing to run 45 days behind so I need to be able to return Jan data.

 

[kskid]

Try this.

{Table.Datefield} in
[
DateSerial(Year(currentdate),Month(currentDate)-2,1) to_
DateSerial(Year(currentdate),Month(CurrentDate)-1,1)
]

Translated, if today = 3/13/2005

{Table.Datefield} in
[
DateSerial(Year(3/13/2005),Month(3/13/2005)-2,1) to_
DateSerial(Year(3/13/2005),Month(3/13/2005)-1,1)
]

{Table.Datefield} in
[
DateSerial(2005,3-2,1) to_
DateSerial(2005,3-1,1)
]

{Table.Datefield} in
[
Date(2005,1,1) to_
Date(2005,2,1)
]

Since to_ is excluding the end date, we finish up with the following in Show SQL Query

{Table.Datefield} => {ts '2005-01-01 00:00:00.00'} and
{Table.Datefield} < {ts '2005-01-01 00:00:00.00'}

Cheers,

-LW

 

[kskid]

Ooops. Have a Typo error

{Table.Datefield} => {ts '2005-01-01 00:00:00.00'} and
{Table.Datefield} < {ts '2005-02-01 00:00:00.00'}

 

[MrBillSC]

You could try looking two month back.
Creat a formula called SelectYr:

If DatePart("m",Today) >= 3 then
(DatePart("yyyy",Today) else
(DatePart("yyyy",Today) - 1)

Create a second formula called SelectMonth:

If DatePart("m",Today) >= 3 then
(DatePart("m",Today) - 2 else
(DatePart("m",Today) + 10)

In your select record formula:

DatePart("yyyy",MyTable.MyDate) = SelectYr and
DatePart("m",MyTable.MyDate) = SelectMonth

I think the above should work.
MrBill