Calculate based on separate date field and time field 1

[mcquam]

I need to calculate the number of minutes early or late that a service was delivered compared with the date due and time due. It seems like the best way would be to create two DateTime fields and use DateDiff but I can't figure out how to join the fields (other posts seem to be doing it the other way round). I have the following fields:

{TMGS_FILE_COVER_BACKUP.Date Issued}{TMGS_FILE_COVER_BACKUP.Date Due}
{TMGS_FILE_COVER_BACKUP.Time Issued}{TMGS_FILE_COVER_BACKUP.Time Due}

Can anyone suggest the best approach?

I'm using CR11 and an access database.

 

[Madawc]

One method is to separate the elements using a set of formula fields Datepart, e.g. DatePart ("d", {date1}) for day. Then re-unite as

DateTime (YYYY, MM, DD, HH, MM, SS)

An alternative is to use DateDiff separately for date and for time, adding or subtracting a day depending on the differences.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Assuming that these fields are dates and times respectively, create these formulas:

//{@datetimedue}:
datetime({TMGS_FILE_COVER_BACKUP.Date Due},{TMGS_FILE_COVER_BACKUP.Time Due})

//{@datetimeissued}:
datetime({TMGS_FILE_COVER_BACKUP.Date Issued},{TMGS_FILE_COVER_BACKUP.Time Issued})

Then use a datediff formula like this:

datediff("n",{@datetimeissued},{@datetimedue})

This would give a positive result if issued before due. If you want the reverse, switch the order of the formulas in the datediff formula.

-LB

 

[mcquam]

Thanks lbass, I've entered the formulas as you suggest but I can't get past the first date field as I get an error "a date is required here" for the date due and date issued fields.

As far as I can see, the Access date due field is set as date/time and I have formatted it as a date field in the report. It is possible that there is incompatible data in the Access table though.

 

[Madawc]

What's the field type? Sometimes a field will be classed as 'string' even though what it contains is a date. Try

datetime(Cdate({TMGS_FILE_COVER_BACKUP.Date Due}),Cdate({TMGS_FILE_COVER_BACKUP.Time Due}))

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[mcquam]

Thanks Madawc. I'm getting past the date field and getting the same result ("a time is required here") for the time due field now!

 

[lbass]

If the date due field is ALREADY a datetime, then what does the time field add? Place your fields on the report and then run the mouse over them and observe the data types that appear in the tooltips, and then report back. Note that formatting of dates is for appearance only.

-LB

 

[mcquam]

They are both datetime fields but they contain either date or time respectively but not both. They are specified in the Access database as date/time fields.

 

[lbass]

Then change your formulas to this syntax:

datetime(date({TMGS_FILE_COVER_BACKUP.Date Due}),time({TMGS_FILE_COVER_BACKUP.Time Due}))

-LB

 

[mcquam]

That's worked now. Many thanks to both of you.