Evaluate if a date exists

[fryguy5049]

CR 10 & MySQL 4
I have a DB with new data everyday except there is no data on saturdays. There are no nulls in my date field, just not a record for any date that falls on a saturday.

how can I write a formula that pulls data from the 9th day of every month and if the 9th falls on a saturday then the report should pull data from the previous day(8th).
Currently I have:

If {table1.cycle}=9 and Day ({table1.rep_date})=9 and {table1.days}=" 30-DAY"then
{table1.total_acct}

else If {table1.cycle}=9 and Day ({table1.rep_date})=8 and {table1.days}=" 30-DAY"then
{table1.total_acct}
else 0

Then I have a summary field on this formula. So the problem is next month when the 9th doesn't fall on a saturday the formula adds the data from both the 8th & 9th.

 

[synapsevampire]

This is all very odd in it's design.

To limit rows to either the 9th day or the previous day if it's a Saturday, use Report->Edit Selection Formula->Record

(
// If the 9th is not a Saturday pull the data
{table1.cycle}=9 and Day({table1.rep_date})=9
AND DAYOFWEEK({table1.rep_date}) <> 7
and {table1.days}=" 30-DAY"
and
// If the 8th is a Friday, then the 9th is a Saturday
// so pull the data
{table1.cycle}=9 and Day({table1.rep_date})=8 and
Dayofweek({table1.rep_date}) = 6
and {table1.days}=" 30-DAY"
)

You may have meant that you want to create a formula, not pull data, so adapt the above if need be to match your formula requirements.

-k

 

[lbass]

Try something like:

If {table1.cycle}=9 and
(if Day ({table1.rep_date}) = 9 and
dayofweek({table1.rep_date}) = 7 then
day({table1.rep_date}) = 8 else
if Day ({table1.rep_date}) = 9 and
dayofweek({table1.rep_date}) <> 7 then
day({table1.rep_date}) = 9) and
{table1.days} = " 30-DAY" then
{table1.total_acct} else 0

-LB