|
As an extremely critical question, what kind of values are your @time and IncTime fields? Are they Time values, or, more likely, DateTime values?
The simplest solution is to make sure you are using CTime on both sides of the comparison. If you are using DateTime values, this will pull out only the time value. However, that does mean that it will ignore the date value. This may be an issue for the shift that straddles midnight.
As another tip, make sure you are including one (and only one) of your endpoints. With your current formulas, you'll never capture any incidents that occur at exactly 3:00 pm.
Also, I'd recommend using the range data type, or using the BETWEEN operator, rather than using "less than" and "greater than" operators.
So, for your running total, try:
CTime(@time) IN CTime(#15:00#) TO_ CTime(#23:00#)
Look at the critical changes here. By using CTime on @time, we make sure we are only looking at the time piece of the value. "IN" and "TO" are part of the range operation. By putting the underscore after "TO" we are telling it to ignore 23:00 (i.e., consider the first time to be part of this shift, but the last time to be part of next shift). The military time is just personal preference on my part. I consider it to be less error-prone than trying to use "am" and "pm." But, then, I work with a bunch of military types.
To answer the other question you snuck in there, the running total doesn't know it's counting incidents. It just knows that it's counting rows. Every time a new row pops up in that time frame, it adds 1 to the current total.
In your second formula, I have a sneaking suspicion that Crystal might not be doing the implicit conversion in the way you expect. If it is converting IncTime to a string, instead of "07:00 AM" to a time, you are asking which one comes first alphabetically. Now, 9 times out of 10, it works out to be the same. But, occasionally, it's just not. Especially as you haven't explicitly told Crystal what format to use for the conversion. While you're trying to get a handle on things, it is best to always use explicit conversions. So, try something like:
CTime({Incident.IncTime}) BETWEEN CTime("07:00") AND CTime("15:00")
I went with the BETWEEN structure here just to demonstrate it. However, notice something. There is no way to tell it to ignore the endpoints. If you use BETWEEN, then any incident which occurs at exactly 15:00 will get counted on two different shifts.
As a tip to help you as a newbie, try breaking the problem down into smaller pieces, and attacking it that way. Create a formula that just returns CTime(Incident.IncTime), and display it on the report. Display your running total on the report. Try to narrow down which specific piece of the formula is returning an unexpected value. This will greatly help you to correct whatever mistake you might have made.
|
Hello and thanks for your recent response. I do have the CTime values counting, but I still have one issue. I have changed everything to military time (easier, you're right), however, I cannot get the formulas to count over the midnight time. Meaning, I have a shift that goes from 23:00 to 07:00, but for some reason, the formula CTime(@time) IN Ctime ("23:00") to_ Ctime(#07:00#) is not reading all the information.
I run test information and the items from 23:00 to 00:00 are not being counted in this calculation but all the others from 00:00 to 07:00 are being counted.
Have any more great advice for me?
|
IP Logged
