Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal 2008 - date formula error

Posted on 2011-09-12
13
Medium Priority
?
326 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
[X]
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
  • 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

721 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