Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to get a correct time stamp

How to get a correct time stamp

How to get a correct time stamp

Good afternoon,

I kinda got stuck writing a report In Crystal report 2008. I have a database with several time stamps for several kategories (names). I'm only looking for Address and Problem Category (which I'm filtering). I'm trying to get a time stamp for the first problem and the time stamp for the last address before the first problem. For example.

Address 00:36:00
Address 00:38:00
Problem 00:40:00
Problem 00:42:00
Address 00:44:00

My results should show/contain time stamps 00:38:00 for the address and 00:40:00 for the problem.

My logic was to create a column {@MIN_Problem} and use Min formula for the problem- "if {Response_Edit.Field_Name} like "Problem" then Minimum ({Response_Edit.Date_Time})". And the formula works, but it's giving me only value for the Problem name. Address name is blank. I believe that is breaking my other logic I wanted to use.

Then I wanted to write a statement {@LessThanMINProblem}:
"if (({Response_Edit.Field_Name} like "Address") and ({Response_Edit.Date_Time}) < {@MIN_Problem}) then "YES"
else "NO""
This would give me "YES"/"NO" value for the Address, which I would plug into the Max formula:
"if {Response_Edit.Field_Name} like "Address" and {@LessThanMINProblem} like "Yes" then Maximum ({Response_Edit.Date_Time}, ({Response_Edit.Field_Name}))"

The results using these formula are correct for the Problem nature, but the address I'm getting 00:44:00, which is not correct. It should be 00:38:00

I hope this makes a sense. Any help in this case would be very, very appreciated.

Thank you!

RE: How to get a correct time stamp

You should change your address formula to:

If {response_edit.field_name} like “Address” and
{@LessThankMINProblem}=“Yes” then

Then insert a maximum on this formula or write a second formula with a maximum on this formula. Be sure to use your own quote marks as mine will error out.


RE: How to get a correct time stamp

Thank you lbass, I was hoping that you would answer. You've always helped me in the past.

The problem is, that my first formula- {@MIN_Problem} is giving me blank value for the address, so my second formula- {@LessThanMINProblem}- has "YES" value for every address. And my third formula is giving me then the maximum (please, see the attachment, the last column should have a value of 2:49:59).

RE: How to get a correct time stamp

First sort the datetime field in ascending order. Then create a formula:

datetimevar AddDT;
datetimevar ProbDT;
numbervar i;
numbervar k;

if {table.Field_Name}="Address" and
i=0 and
next ({table.Field_Name}) = "Problem" then
AddDT := {table.datetime};
if {table.Field_Name}="Problem" then
i := i + 1;
if i = 1 then
k := k + 1;
if k = 1 then
ProbDT := {table.datetime};

If the fieldname just contains the words problem and address, instead of only using those words, then continue to use "like" instead of "=" in the formula above.
Then create two display formulas for the report footer:

datetimevar AddDT;

datetimevar ProbDT;

If you are using a group, you would have to add reset formulas for each variable to the group header.


RE: How to get a correct time stamp

thank you very much! I see the logic behind it and it make sense and I think it might work!
I'm using a group based on an incident ID. Is it too much to ask how the reset formula would look like? Or do I do running total field?

RE: How to get a correct time stamp

Why would you need a running total—are you planning to count something?

For the resets, add a formula to the group header section:

Datetimevar AddDT := datetime(0,0,0,0,0,0);
Datetimevar ProbDT := datetime(0,0,0,0,0,0);
Numbervar i := 0;
Numbervar k := 0;

You can suppress the formula.


RE: How to get a correct time stamp

Thank you so much! I'm getting correct time stamp for the address. However, I'm now getting the latest time stamp (for either field_name- not necessarily just for the problem) for the ProbDT formula (please, see the attachment).

Ultimately, I need to calculate 1. fixed time (a fixed field from the database) to last address before first problem time stamp for every incident AND 2. last address before first problem time stamp to first problem time stamp for every incident.

I was able to calculate the 1st thing using Formula: Time(0,0,0) + (Time({@AddressDT})-Time({table.Time_PickUp})) and it worked perfectly! Thank you! I will have to work on the second piece.

RE: How to get a correct time stamp

I can’t tell how you implemented my suggestion. Maybe you could send the report itself (saved with data) with identifying info removed.

I did test this and it worked here.


RE: How to get a correct time stamp

PS. I would be careful about using only the time() function, as you could end up ignoring dates that would have an impact in your calculations. You can always DISPLAY only the time, while using datetimes in your calculations.


RE: How to get a correct time stamp

You are correct on the only time() function.
Thank you very much for your help (as always!). I actually made it work and it works perfectly. :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close