Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Crystal 2008 - date formula error

Posted on 2011-09-12
13
321 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 100

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 100

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 500 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 100

Expert Comment

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

mlmcc
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

840 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