Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Date Range formula Post Reply Post New Topic
Author Message
Kathleen
Newbie
Newbie
Avatar

Joined: 31 Dec 2007
Location: United States
Online Status: Offline
Posts: 17
Quote Kathleen Replybullet Topic: Date Range formula
     Posted: 14 Jan 2008 at 7:29am
If DatePart ("ww",{SAFETY.DateChk}) - DatePart ("ww", Previous({SAFETY.DateChk}))> 1 then "out of compliance"
 else "in compliance"
 
I received a shell of the above formula to use in calculating date range.  What I actually need is this:
 
The safety check can be done any day during the week as long is it is done one day each period of Sunday-Saturday.  The report will determine compliance with this schedule of inspections.
 
Meaning, the report should calculate whether an inspection is performed once during each period of Sunday - Saturday.  If it is, the report will read "in compliance", if there is a week that is missed, the report will read "out of compliance".
 
When using the formula previously provided, Crystal keeps asking for an actual date where I have {SAFETY.DateChk} - it is not reading the field.  I need the formula to read the field and then calculate each week from the SAFETY.DateChk date.
 
Can you please advise?
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 14 Jan 2008 at 11:26am
What is the data type of the DateChk field, then?  Is it stored as a string?

Try using CDate({SAFETY.DateChk}) and see what happens.
IP IP Logged
Kathleen
Newbie
Newbie
Avatar

Joined: 31 Dec 2007
Location: United States
Online Status: Offline
Posts: 17
Quote Kathleen Replybullet Posted: 14 Jan 2008 at 12:02pm
I just tried to put the CDate before the formula that I have and I'm still having problems.  I think our dates are in as strings, but I'm not sure.  Is there anything else that I can try?
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 15 Jan 2008 at 4:39am
Did you put the CDate inside the DatePart function?  I.e., the formula should look like:

If DatePart ("ww",CDate({SAFETY.DateChk})) - DatePart ("ww", CDate(Previous({SAFETY.DateChk}))) > 1
then "out of compliance"
else "in compliance"

You may also need to check your data, and make sure all of the data are actually dates in a format recognizable by Crystal.  The IsDate function will help you here.

Depending on your data, you may also need to check for NULL values.
IP IP Logged
Kathleen
Newbie
Newbie
Avatar

Joined: 31 Dec 2007
Location: United States
Online Status: Offline
Posts: 17
Quote Kathleen Replybullet Posted: 18 Jan 2008 at 6:28am
If DatePart ("ww",CDate({SAFETY.DateChk})) - DatePart ("ww", CDate(Previous({SAFETY.DateChk})))> 1 then "out of compliance"
 else "in compliance"
============================
When I use the above formula, I get an error that states "Bad date format string" so I guess my dates are in as strings.  What can I do now?
IP IP Logged
Kathleen
Newbie
Newbie
Avatar

Joined: 31 Dec 2007
Location: United States
Online Status: Offline
Posts: 17
Quote Kathleen Replybullet Posted: 18 Jan 2008 at 6:34am
If DatePart ("ww",CDate(ToNumber({SAFETY.DateChk}))) - DatePart ("ww", CDate(ToNumber(Previous({SAFETY.DateChk}))))> 1 then "out of compliance"
 else "in compliance"
===========================
 
I have also tried the above formula and it doesn't work either.  Any more advice?
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 18 Jan 2008 at 8:59am
Post some of the sample data here for your dates.  Clearly, they are in a non-standard format.  Also, check your data for any records that may have entries that aren't dates (e.g., "TBD" or "ASAP").  It looks like you may need to do some serious data manipulation to get useful results out.  (Especially if cleaning up the actual data isn't an option.)
IP IP Logged
Kathleen
Newbie
Newbie
Avatar

Joined: 31 Dec 2007
Location: United States
Online Status: Offline
Posts: 17
Quote Kathleen Replybullet Posted: 22 Jan 2008 at 2:11pm

Our dates are definately in as strings.  Unfortunately, due to company policy and the privacy of our information, I cannot post sample data.  I can tell you that I am now using the following formulas and am getting the following errors.

(ToNumber ({SAFETY.DateChk}))

and
 
If DatePart ("ww",CDate({@TO NUMBER})) - DatePart ("ww", CDate(Previous({@TO NUMBER})))> 1 then "out of compliance"
 else "in compliance"
 
and I am getting this error:
 
"Dates must be between year 1 and year 9999".
 
If I save the formulas and return to the report, everything is blank, it will not pull up anything at all.
 
As the dates are strings, don't I have to change them to numbers first as I did in the first formula?  Then shouldn't I be able to  place that formula into the second formula for calculation purposes?
 
I am trying to create a dummy account so perhaps I can send that as sample data.  Is there any advice you can give me while I get that information created?


Edited by Kathleen - 24 Jan 2008 at 2:10pm
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.