Table Analysis

[June7]

What do you mean by 'Query row'?



The expression belongs on the Field row of query builder. This is to construct a field with expression.

[Sheba]

Sorry, I meant to say "field row". Okay, I changed the syntax to:

Minutes: DateDiff("n",[Coronary].[firstTime],[Coronary].[lastTime])

it gives a table of values which (after a manual random check) appears to be correct until the value highlighted in red below. The record has firstTime (11:44pm) and lastTime (12:09am). What should I do to correct this?

Minutes

56

-1415

28

[June7]

This range is crossing midnight to the next day? Without the date component, it reads 12:09am as 12:09am of the same day as 11:44pm. So 12:09am is before 11:44pm and will get weird results.

If ranges will cross midnight and/or extend for multiple days, weeks, months, etc, need date component.

[Sheba]

thanks but what do you mean by Date component

[June7]

As in Date/Time type field.

There is date component and time component.

Need the MM/DD/YYYY parts.

[Sheba]

the formula I know is: DateDiff("n",[Coronary].[firstTime],[Coronary].[lastTime])
and the "n" indicates the output should be in minutes. Could you advise what the MM/DD/YYYY parts will be exactly and where it should be in this formula please? This is new for me. Thanks much

[June7]

The MM/DD/YYYY must be in the data. If you do not have that data saved then solving this will be difficult.

[Sheba]

hi June7, this is still not clear to me. In my table, date of records has format DD/MM/YYYYY. Am I supposed to put this into

the formula DateDiff("n",[Coronary].[firstTime],[Coronary].[lastTime]) ???

[June7]

The example data you show in post 17 has only time parts.

If data is saved as full date/time, then the expression should work.

[Sheba]

Now I'm super.

One thing has nothing to do with the other. when you asked about the format, I thought you were referring to the record in whole and not that particular field. for the field in question, the input mask I have is 00:00;0;_

[Sheba]

i.e. it should give an output of hh:mm

[June7]

I am also confused. Do you have date in one field and time in another? The expression will have to combine these fields.

Regardless of what you want to show, if you want DateDiff to work properly, need full date/time value. How is the calc supposed to know that 12:09 AM is the next day and not the same day as 11:44 PM? If the date is not provided, the calc will assume same day, resulting in a negative in this case.

[Sheba]

in my table I have as many as 50 columns. Three of these columns are

"tripDate"
data type - date/time
format - dd/mm/yyyy

"firstTime"
data type - date/time
input mask - hh:mm

"lastTime"
data type - date/time
input mask - hh:mm

[June7]

That does not help solve the issue of when firstTime and lastTime fall on different days. Need tripFirstDate and tripLastDate or maybe some code to determine if lastTime falls on following day. If firstTime is PM and lastTime is AM then lastTime must have been the next day, unless this range could extend for multiple days.