Substitute a date 1

[spiced]

I am using Crystal Reports 7.0 and was wondering if there is a way to substitute a date for a range of dates that the report is pulling from a field in the database. I can't add a field to the database with the two dates I want to use. There are two days (the 14th and 28th of each month) that I want to specify for two different range of dates 1.) M/1/YY to M/14/YY = M/14/YY 2.) M/15/YY to M/last day of month/YY = M/28/YY.

spiced

 

[lbass]

Try:

If day({table.date}) in 1 to 14 then Date(year({table.date}),month({table.date}), 14) else
Date(year({table.date}),month({table.date}), 28)

You can then use field format to format the date however you like.

-LB

 

[spiced]

Thanks that worked great.

spiced

 

[spiced]

I tried the formula in another report and I keep getting an error "Dates must be between year 1 and year 9999". The dates are all in the same format as the other report, any ideas?

 

[synapsevampire]

You may have a null date field, try:

if not(isnull({table.date})) then
(
If day({table.date}) in 1 to 14 then
Date(year({table.date}),month({table.date}), 14)
else
Date(year({table.date}),month({table.date}), 28)
)
else
{table.date}

-k

 

[spiced]

I got the same result.

 

[lbass]

Try placing the date field in the details section and sort by it so you can see what's going on with the years.

You could also try this variation on SV's formula:

if not(isnull({table.date})) or
{table.date} <> date(0,0,0) then
(
If day({table.date}) in 1 to 14 then
Date(year({table.date}),month({table.date}), 14)
else
Date(year({table.date}),month({table.date}), 28)
)
else
{table.date}

-LB

 

[synapsevampire]

Bizarre, perhaps the date is stored in a different format or as zeroes.

Try:

(
If day({table.date}) in 1 to 14
and
year({table.date}) in 1 to 9999 then
Date(year({table.date}),month({table.date}), 14)
else
If day({table.date}) in 15 to 31
and
year({table.date}) in 1 to 9999 then
Date(year({table.date}),month({table.date}), 28)
else
{table.date}
)

-k