Solved

Crystal 2008 - date formula error

Posted on 2011-09-12
13
317 Views
Last Modified: 2012-06-27
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};



0
Comment
Question by:MidWestGirl
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 

Author Comment

by:MidWestGirl
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:MidWestGirl
Comment Utility
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
 

Author Comment

by:MidWestGirl
Comment Utility
also no supression on the field
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Can you post your report?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:MidWestGirl
Comment Utility
would I save off a copy of the report with the data included? it would only have 1 record.
0
 

Author Comment

by:MidWestGirl
Comment Utility
I have attached my report. I removed everything from it except the date field in question
DebugDate.rpt
0
 

Author Comment

by:MidWestGirl
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 19

Accepted Solution

by:
GJParker earned 500 total points
Comment Utility
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
 

Author Comment

by:MidWestGirl
Comment Utility
Thanks Sage. This was the problem.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What is wrong with my solution which covers both cases?

mlmcc
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now