[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Crystal Reports Last Hour Selection (Data Stored as String)

I have an access 97 DB I inherited that has a time field as a string in military time '13:30' would be an accurate data sample. I need to make a highlight selection formula to highlight just the last hour. There is also a date column that is a DateTime but all the times are 12:00:00.
I try and do something like this.

IF DateAdd('h',-1,CurrentTime) >= {Newtrack.Time} then
   RGB(200,200,75)

However i get an error saying that CurrentTime (A Date is required here) If i make it CurrentDateTime I get an error on  Newtrack.Time (A date-time is required here)

I then tried to convert the string to a time using Ctime

If istime({Newtrack.Time}) then
CTime({Newtrack.Time})
and
iF DateAdd('h',-1,CurrentTime) >= {Newtrack.Time} then
   RGB(200,200,75)

That gives me the same error (Date is required here) on CurrentTime
0
knausscpa
Asked:
knausscpa
  • 4
  • 2
  • 2
  • +1
1 Solution
 
ZebulonPiCommented:
If your date data is in string format, and is consistent, you could always just use

TimeVar testtime := CTime((tonumber((Left (Newtrack.Time,2)))),00,00);

if Hour(testtime) > Hour (CurrentTime) then "later time" else "earlier time";


Tested that, works fine with a made up string following your time format.
0
 
UnifiedISCommented:
IF (CurrentTime - 3600) >= CTime({Newtrack.Time}) THEN
RGB(200,200,75)
0
 
knausscpaAuthor Commented:
That formula works if i put it in the report but it doesn't take date into account. I need 2 things,

#1 I need it need the formula for highlighting hence the RGB.
#2 I need the formula to take date into consideration from Newtrack.Date so that I can highlight all fields that have had activity in the last hour or 3 hours etc.
0
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
ZebulonPiCommented:
What format is Newtrack.Date in? I take it you're looking to look back into yesterday to see if something was done 3 hours ago then (i.e. 11pm, compared to 1am)?
0
 
knausscpaAuthor Commented:
NewTrack.Date is an actual Date Time format only problem is that only date is recorded so all times are 12:00:00.
0
 
UnifiedISCommented:
CTime and CurrentTime only work with time, CurrentDateTime and NewTrack.Date are datetimes.  You need to add some date to your time value or add some time value to your date.  
Sounds like you need to add a date to your time value before making the compare.  Where you have 13:30, what date should go with it?
 
0
 
knausscpaAuthor Commented:
@Unified
Date would get pulled from Newtrack.Date which is a date time field with no time data. Here is a little data directly from the database. Just ignore the 16 columns those are separate fields.

J               Date       Time      
16            1/25/200808:57      16
16            4/2/2008      14:16      16
16            4/2/2008      14:29      16
16            4/2/2008      14:29      16
16            4/2/2008      14:29      16
16            4/2/2008      14:29      16
16            4/2/2008      14:29      16
16            4/4/2008      07:30      16
16            4/4/2008      07:30      16
16            4/4/2008      07:30      16
16            4/4/2008      07:30      16
0
 
mlmccCommented:
Try this

DateAdd("h",-1,CurrentDateTime) <= DateTime(Date({NewTrack.Date},Time({Newtrack.Time}))

mlmcc
0
 
knausscpaAuthor Commented:
IF DateAdd("h",-1,CurrentDateTime) <= DateTime(Date({NewTrack.Date}),(Time({Newtrack.Time})))  then
   RGB(0,255,255)

Worked, just had to adjust () a little here and there.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now