First of Month? 2

[parrotheadman]

When I ran my reports for September MTD using the following formula:

if (({vLOIS1.Dispdate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd("d",-1,Currentdate)) then 1 else 0

I suddenly lost a number of loans that I had yesterday. Can anyone figure out why?

 

[synapsevampire]

Where was this code used? If it's the record selection criteria, instead of:

"if (({vLOIS1.Dispdate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd("d",-1,Currentdate)) then 1 else 0"

(you had a paren issue anyway)

Try:

{vLOIS1.Dispdate}>= date(year(CurrentDate),month(CurrentDate),1)
and
{vLOIS1.Dispdate} <= Currentdate-1

This assumes that you want the beginning of the month to yesterday.

This construct should pass the SQL, and correctly defines the period.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[parrotheadman]

My problem seems to be with on the first of the month only. If I am in the middle of the month, I can pull MTD information easily, but when I get to day 1 and I need to pull everything from the previous month, I have the problems I initially listed. I tried your coding, but I actually received 0 records in return.

This is your coding:
if isnull ({vLOIS1.DispDate}) then 0 else
if (({vLOIS1.Dispdate}>= date(year(CurrentDate),month(CurrentDate),1) and {vLOIS1.Dispdate} <= Currentdate-1)
and {vLOIS1.Dispcode} =1 and {vLOIS1.LoanType}<>31 and {vLOIS1.LienIndicator}=1) then 1 else 0

This is my original one:
if isnull ({vLOIS1.DispDate}) then 0 else
if (({vLOIS1.Dispdate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate))
and {vLOIS1.Dispcode} =1 and {vLOIS1.LoanType}<>31 and {vLOIS1.LienIndicator}=1) then 1 else 0

Any ideas?

 

[Naith]

Parrot,

Every time you execute this report on the first of the month, your formula is literally saying:

if ({vLOIS1.Dispdate} in '1-OCT-2002' to '30-SEP-2002')

which isn't correct.

You need to catch the exception for the first of each month in a seperate clause. Try the following on for size:

If IsNull ({vLOIS1.DispDate}) 
Then 0 
Else
If Day(CurrentDate) = 1
Then
If (({vLOIS1.Dispdate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd("m",-1,Currentdate))
    and {vLOIS1.Dispcode} = 1 and {vLOIS1.LoanType} <> 31 and {vLOIS1.LienIndicator} = 1) 
    Then 1 
    Else 0
Else
If Day(CurrentDate) <> 1
then
If (({vLOIS1.Dispdate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate))
    and {vLOIS1.Dispcode} = 1 and {vLOIS1.LoanType} <> 31 and {vLOIS1.LienIndicator} = 1) 
    Then 1 
    Else 0

Good luck,

Naith

 

[parrotheadman]

O.K. Naith, I see where you are going with this. Now, what do you suggest is the best way to test this as if today were 10/1/2002? If I just change my system date, would that do it?

 

[synapsevampire]

Ahhh, right...

if isnull ({vLOIS1.DispDate}) then
0
else
if day(currentdate) = 1 then
(if (({vLOIS1.Dispdate} in dateadd('m',-1,Date(Year(CurrentDate),Month(CurrentDate),1)) to Dateadd(&quot;m&quot;,-1,Currentdate))
and {vLOIS1.Dispcode} =1 and {vLOIS1.LoanType}<>31 and {vLOIS1.LienIndicator}=1) then
1
else
0)
else
if (({vLOIS1.Dispdate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate))
and {vLOIS1.Dispcode} =1 and {vLOIS1.LoanType}<>31 and {vLOIS1.LienIndicator}=1) then
1
else
0

Looks about right...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

You could do that. I don't know what system you're using, but it seems to me that the most painless way to test the formula would be to change the Day(CurrentDate) = 1 check to Day(CurrentDate) = 2.

If the numbers then act like you'd want them to act for the 1st of each month, then switch the check back to 1 after testing.

All the best,

Naith