calculate time difference if criteria true

[gsrai31]

I have three fields – “Location”, “FirstReg” and “LastReg”.
In fourth field I want to calculate “TimeDiff” in hh:mm:ss, if “Location” is “LHR” or “BHX”, leave it blank if “Location” is “MNC” or FirstReg or LastReg are empty.
In fifth field I want “1” if “TimeDiff” is less then 1:00, “0” if “TimeDiff” is greater then 1:00 hour, blank if “TimeDiff” is blank.
Similar to table attached in word.

Hope someone can help me with this. Thanks in advance. GS

 

[raskew]

Hello -

Try this:

SELECT
    tblTimeDiff.Location
  , tblTimeDiff.FirstReg
  , tblTimeDiff.LastReg
  , DateDiff("n",[FirstReg],[LastReg]) AS TimeDiffM
  , IIf([Location]="MNC","",Format(Int([TimeDiffM]/60),"00\:") & FORMAT([timediffm] Mod 60,"00")) AS TimeDiffSay
  , SWITCH([Location]="MNC",""
  , [TimeDiffM] Between 1 AND 59,1
  , [timeDiffM]>60,0) AS Success
FROM
   tblTimeDiff;

...(provided your example was accurate) substituting the name of
your table for "tblTimeDiff".

You'll end up with an additional field in your query, which shouldn't
be a problem if the intent is publish a report -- just don't include the
added field.

HTH - Bob

 

[gsrai31]

Bob
Many thanks for your help with this.
It worked.
Regards GS

 

[gsrai31]

Hello Bob,
My criteria is getting a bit complicated so I need some further help with this,

using same feilds as before I would like to calculate in Success column –

If Location is <> LHR or FirstReg is empty then Blank

If TimeDiffSay < 60mins AND time in FirstReg on Monday to Friday < 17:05, on Saturday < 15:45 then “1” else “0”

If time in FirstReg on Monday to Friday is >17:05 and on Saturday > 15:45 or FirstReg day is Sunday And LastReg is next day and Time on Monday to Friday < 17:05, on Sat < 15:45 then “1” else “0”

Please see the attachment for examle table and above criteria.
Thanks
Gs

Hello -

Try this:

SELECT
    tblTimeDiff.Location
  , tblTimeDiff.FirstReg
  , tblTimeDiff.LastReg
  , DateDiff("n",[FirstReg],[LastReg]) AS TimeDiffM
  , IIf([Location]="MNC","",Format(Int([TimeDiffM]/60),"00\:") & FORMAT([timediffm] Mod 60,"00")) AS TimeDiffSay
  , SWITCH([Location]="MNC",""
  , [TimeDiffM] Between 1 AND 59,1
  , [timeDiffM]>60,0) AS Success
FROM
   tblTimeDiff;

...(provided your example was accurate) substituting the name of
your table for "tblTimeDiff".

You'll end up with an additional field in your query, which shouldn't
be a problem if the intent is publish a report -- just don't include the
added field.

HTH - Bob