Multiple Time criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that records several times. I need to report on time
intervals for several points. I am using a select query to gather the data.
Several of the time intervals are variable. Example

Interval 1
Time2 - Time 1

interval 2
Time 6 - (time5 or time4) whichever is earliest

The criteria for interval 1 I have already, the expression for interval 2
has me a bit stymied. Any help is greatly appreciated.
 
'between Time 6 and iif(time5< time4,time5,time4)' if you are using literal
dates u must enclose them with #.
 
That works, until time5 or time4 are null, then it does not evaluate
properly. Suggestions on how to handle this? Here is the criteria that I am
using, if all values are present, which is not always the case. It works
perfectly.

Time2: (IIf([ufhtime]<[tbltransferhospital]![iibiiiabolus1time] And
[ufhtime]<[fibrinolytictime],[ufhtime],IIf([fibrinolytictime]<[ufhtime] And
[fibrinolytictime]<[tbltransferhospital]![iibiiiabolus1time],[fibrinolytictime],[tbltransferhospital]![iibiiiabolus1time])))-[arrivaltime]

Thank you very much
 
use nz([mydate],#default date#)
--
HTH
Martin J


Slightly Befuddled said:
That works, until time5 or time4 are null, then it does not evaluate
properly. Suggestions on how to handle this? Here is the criteria that I am
using, if all values are present, which is not always the case. It works
perfectly.

Time2: (IIf([ufhtime]<[tbltransferhospital]![iibiiiabolus1time] And
[ufhtime]<[fibrinolytictime],[ufhtime],IIf([fibrinolytictime]<[ufhtime] And
[fibrinolytictime]<[tbltransferhospital]![iibiiiabolus1time],[fibrinolytictime],[tbltransferhospital]![iibiiiabolus1time])))-[arrivaltime]

Thank you very much

Martin J said:
'between Time 6 and iif(time5< time4,time5,time4)' if you are using literal
dates u must enclose them with #.
Click to expand...
Click to expand...
 
That works miracles for me, thank you very much!

Martin J said:
use nz([mydate],#default date#)
--
HTH
Martin J


Slightly Befuddled said:
That works, until time5 or time4 are null, then it does not evaluate
properly. Suggestions on how to handle this? Here is the criteria that I am
using, if all values are present, which is not always the case. It works
perfectly.

Time2: (IIf([ufhtime]<[tbltransferhospital]![iibiiiabolus1time] And
[ufhtime]<[fibrinolytictime],[ufhtime],IIf([fibrinolytictime]<[ufhtime] And
[fibrinolytictime]<[tbltransferhospital]![iibiiiabolus1time],[fibrinolytictime],[tbltransferhospital]![iibiiiabolus1time])))-[arrivaltime]

Thank you very much

Martin J said:
'between Time 6 and iif(time5< time4,time5,time4)' if you are using literal
dates u must enclose them with #.
--
HTH
Martin J


:

I have a database that records several times. I need to report on time
intervals for several points. I am using a select query to gather the data.
Several of the time intervals are variable. Example

Interval 1
Time2 - Time 1

interval 2
Time 6 - (time5 or time4) whichever is earliest

The criteria for interval 1 I have already, the expression for interval 2
has me a bit stymied. Any help is greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top