Crystal 2008 - date formula error

I am using crystal 2008. My data is coming from an Acces table. If a date is null then I want to set the date to the current date and time. My formula originally used the If IsNull function. This did not work so there must be someother value. I redid the formula and get an error that a string is required here.  The field is a datetime field. When the error message displays this is the line that gets highlighted If isdatetime({Projects.Actual Finish}) . Can anyone tell me what is wrong with this formula a or a better way to know if a field is a valid date.? Thanks.


WhilePrintingRecords;

Global Datetimevar ActualFinishDate;


 If isdatetime({Projects.Actual Finish}) then
   
    ActualfinishDate := {Projects.Actual Finish}
else
    ActualFinishDate := currentdatetime


This was my original formula. It did not change a blank date to the current date time
which is why went to the isdate function.

WhilePrintingRecords;

Global Datetimevar ActualFinishDate;

If isnull({Projects.Actual Finish}) then
    ActualFinishDate := currentdatetime
else
   
    ActualfinishDate := {Projects.Actual Finish};



MidWestGirlAsked:
Who is Participating?
 
GJParkerCommented:
You won't have any null values beacuse you have convert database NULL values to default selected so you should be checking for an empty string.

If cstr({Projects.Actual Finish}) = '' then
    currentdatetime
else
    {Projects.Actual Finish}


or uncheck the convert null values options and use

If IsNull({Projects.Actual Finish}) then
    currentdatetime
else
    {Projects.Actual Finish}
0
 
peter57rCommented:
I don't see any reason why your original formula should not work.

Have just dropped it into a report here and it's fine..
0
 
MidWestGirlAuthor Commented:
I put my original formula on the report. The record I am testing with has no value in the
actual finish date field. The formula returns blanks.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
The IsDateTime function takes a number or  string as the argument.  Since you field is a dateTime it gets an error.

Do you have any suppression on the field?

I don't see anything as to why it would not show a value.

Do you have the Exception for NULL or Use Default for NULL in the formula option?

Are you converting NULLs to a default value?

mlmcc
0
 
MidWestGirlAuthor Commented:
I am using Exceptions for Nulls. I changed my formula to not use a global variable.
It still returns blanks.  

WhilePrintingRecords;

If isnull({Projects.Actual Finish}) then
    currentdatetime
else
    {Projects.Actual Finish}
0
 
MidWestGirlAuthor Commented:
also no supression on the field
0
 
peter57rCommented:
Can you post your report?
0
 
MidWestGirlAuthor Commented:
would I save off a copy of the report with the data included? it would only have 1 record.
0
 
MidWestGirlAuthor Commented:
I have attached my report. I removed everything from it except the date field in question
DebugDate.rpt
0
 
MidWestGirlAuthor Commented:
I created a formula that would return the string "NotNull" if the date field was
not null. It turned out to be a true statment. I need to figure out how to check if
a field is a valid datetime. It looks lile blanks but I can't check for that becaurse
I get an error.
0
 
mlmccCommented:
Try this formula

WhilePrintingRecords;

If isnull({Projects.Actual Finish}) then
    currentdatetime
Else if CStr({Projects.Actual Finish}) = "" then
    currentdatetime
else
    {Projects.Actual Finish}

mlmcc
0
 
MidWestGirlAuthor Commented:
Thanks Sage. This was the problem.
0
 
mlmccCommented:
What is wrong with my solution which covers both cases?

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.