Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal 2008 - date formula error

Posted on 2011-09-12
13
Medium Priority
?
327 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
ID: 36524121
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
ID: 36524170
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 101

Expert Comment

by:mlmcc
ID: 36524260
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:MidWestGirl
ID: 36524282
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
ID: 36524289
also no supression on the field
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36524326
Can you post your report?
0
 

Author Comment

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

Author Comment

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

Author Comment

by:MidWestGirl
ID: 36525604
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 101

Expert Comment

by:mlmcc
ID: 36526210
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 2000 total points
ID: 36528988
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
ID: 36529188
Thanks Sage. This was the problem.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36529251
What is wrong with my solution which covers both cases?

mlmcc
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

824 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