Bad Date String Format... with null and date controls in place... 1

[jcrawford08]

Okay, I'm stumped... I have a report that calculates days outstanding based on a date that's entered into a string field (wish it were a date field!); I have controls in my record select as follows:

isdate ({Comment.Field}) and
not isnull({Comment.Field}) and
{@Outstanding time} >= 140.00

The outstanding time formula is:
datediff("d",datevalue(cstr({Comment.Field})),currentdate)

The report is breaking and returing a bad date format string for my outstanding time formula; given that I am controlling in the record select that the comment field MUST be a date and that it CANNOT be null, what other items do I need to control for to ensure only 'good' records are calculated in my report?

As always, many thanks!!


Not in word only, but in deed also... 1Jn3:18

 

[HowardHammerman]

Can you provide an example of the comment field that the report is "breaking" on? I suspect that the isdate() function is not doing what you think it is. Here is a test: Create a simple formula with only isdate({comment.field}). Place it in the detail (or other section) next to the the comment.field. Check the results. Is it really true when you think it should be? Is it false when you think it should be false? That should give you a clue. I suspect embedded blanks.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[Madawc]

There is no point putting a null statement AFTER some other test, since the formula will stop when it finds a null. Rewrite as

not isnull({Comment.Field}) and
isdate ({Comment.Field}) and
{@Outstanding time} >= 140.00

You could also try commenting this out in the command and writing it as a formula field put next to the data, to see what you do have.

For the main command, if it's definitely a date then you can simplify, e.g.

datediff("d", {Comment.Field}, currentdate)

This will give you the difference in days.




Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[jcrawford08]

Madawc,

Thank you!! I had no clue that placing the not isnull () AFTER the others would 'nullify' it

I fixed the order of the clauses and badda bing, we have a working template.

Many thanks!

Not in word only, but in deed also... 1Jn3:18

 

[jcrawford08]

For a complete fix, i added an isdate clause to the outstanding time formula, so that:

if isdate({comment.field}) then datediff("d",{comment.field},currentdate) else -1

this allows it to be a value that I can then report on and do some additional reporting to identify the culprit records... and allows for consistently clean data in the current report.

Thanks for all your help!

Not in word only, but in deed also... 1Jn3:18