Report to pull data from last 10 days

[neronikita]

Hi, I hope I can explain this in a way that makes sense...

I'm trying to create a report that will list all work completed on our equipment over the last 10 days. This will include trucks and trailers. So far, I started trying to create a formula that would show the date range it is displaying in the report, and I can't even get that formula to work.

The formula I found that should do it (for the month, I changed it to the day and it'snot working) is Today & ": 10 days ago = " & DateAdd ("d", -10, CurrentDate).

Does anyone have any idea on how to do this formula and then any suggestions on the rest of the report? I'm thinking I will first list the unit numbers and from there have it that you can drill down on the unit number to see what work was actually completed during those 10 days.

Any guidance anyone can offer would be appreciated... thanks in advance!

Di

 

[MCuthill]

neronikita,

Please provide more information on your database structure & reporting fields if the below is not sufficient to resolve your inquiry. I am making the assumption a work order has a Date Completed field, or something similar.

Record Selection Criteria:

{Table.DateCompleted} >= DateAdd("d",-10,CurrentDate)

This states that the "Date Completed" must be on, or after the date 10 days prior to the CurrentDate.

If this doesn't work, you may need to work with wrapping your Date Field & "CurrentDate" in the Date() function.

Cheers! Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[neronikita]

Hi Mike, thanks for your response. I tried to do that, but it's telling me I need a datetime field. Here are my two tables I am using and the fields/types:

DailyVehicleTracking
PK DailyVehicleTrackingID (int)
VehicleNO (Varchar20)
TrackingDate(date)
Mileage (int)
TimeStarted (Time(7))
TImeStopped (Time(7))
AddlComments (varchar(Max)

VehicleMaintenanceOperations
PK VehicleMaintenanceOperationID (int)
FK DailyVehicleTrackingID (Int)
WorkItemCompleted (Varchar(100))
ComplaintSymptoms (Varcharc(512))
Code (varchar(512))
Comments (Varchar(512))

So the formula I tried, based on yours, was {DailyVehicleTracking.TrackingDate} >= DateAdd("d",-10,today) and it told me that I need a datetime, so I guess I need to either convert my date to a datetime, or convert currentdate or today to a date without a time... am I correct? This may be what you were referring to having to wrap in the date function....


So far, what I want to display in the report is the vehicle number, the date, the work completed. I'm assuming as I get that far they will ask for more, but I'm starting small with basic info...

Thanks!

Di

 

[MCuthill]

neronikita,

If it is wanting a DateTime, I beleive the following will help:

[blue]DateTime[/blue]([blue]Year[/blue]({DailyVehicleTracking.TrackingDate}),[blue]Month[/blue]({DailyVehicleTracking.TrackingDate}),[blue]Day[/blue]({DailyVehicleTracking.TrackingDate}),0,0,0) >= [blue]DateAdd[/blue]("d",-10,[blue]CurrentDate[/blue])

*Note: The structure for [blue]DateTime[/blue]() is [blue]DateTime[/blue](YYYY,MM,DD,HH,MM,SS)

Hope this helps neronikita!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[neronikita]

Thanks Mike. That isn't working, it is highlighting {dailyvehicletracking.trackingdate} and saying that a date is required here, which doesn't make sense to me because it is a date...

Thanks,

Di

 

[MCuthill]

Thanks for the update neronikita.

My assumption would be non-standard data in your dataset, but that can be somewhat difficult to trace down. In looking quickly through the available functions in Crystal, perhaps a function like "DateTimeValue()" may work instead, but would need some testing.

The other question I would ask is, do you have the ability to modify the database, or access to an individual who can? You could look to get that field changed to a DateTime data-type to see if that makes any difference.

Cheers! Hope this helps neronikita!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

Please run your mouse over the field and note the datatype for the field {DailyVehicleTracking.TrackingDate}. If it is in fact already a date, then the solution:

{DailyVehicleTracking.TrackingDate} >= DateAdd("d",-10,CurrentDate)

...should work. Or try:

{DailyVehicleTracking.TrackingDate} >= currentdate-10

-LB

 

[MCuthill]

Hey lbass,

T'was my original suggestion (28 Dec 11 15:57) that sparked the "DateTime" issue (when suggesting the >=DateAdd() solution). I am thinking non-standard data, or null data in this field to be the cause (or not a Date data-type field, as you suggested).

Aside:
I wasn't aware that [blue]DateAdd[/blue]("d",-10,[blue]CurrentDate[/blue]) == [blue]CurrentDate[/blue]-10. --Thanks!

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[neronikita]

lbass, you are correct. My database says "date" for type, but when I put my cursor over it in Crystal, it says string. Why does it do that?

So I am guessing i will have to do something to convert it to a date, correct?

Thanks!

Di

 

[MCuthill]

neronikita,

The DateValue() function mentioned above will accept string inputs (and display as a date). If your string is of standard setup (YYYY-MM-DD for example), you could use string funtions to extract each value (the Mid(), Left() & Right() functions).

The Crystal Reports Help can assist in how to use these various functions.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

MCuthill is correct. If you need more assistance, please show exactly how the string is formatted, e.g., dd/MM/yyyy or whatever it is.

-LB

 

[neronikita]

That worked beautifully. Thank you both for your help!!!!

Have a great day!

Di