Where to put an IIF in a query

  • Thread starter Thread starter Cindy
  • Start date Start date
C

Cindy

I need to put an IIF in a query to calculate the minutes from Start Time to
End Time. It's in military time and of course goes past midnight on some
occasions. Using DateDiff in the Query Field block I get a negative number
on those occasions.

I've read through all the threads here and found this that I think should
work but I don't know where to actually put it. I've tried it in the Field
block and in the criteria block but as soon as I hit enter it clears the
field out.

IIF End Time <= Start Time Then
Me.TimeInMinutes = DateDiff("n", Start Time, End Time) + 1440
Else
Me.TimeInMinutes = DateDiff("n", Start Time, End Time)
End IIF

I hope someone can point me in the right direction here.

Thanks!
Cindy
 
Type the expression into the Field row in query design:
=DateDiff("n", [Start Time], [End Time]) + IIf([End Time] < [Start Time],
1440, 0)
 
Once again, you are my hero. It worked perfectly.

Thanks!

Cindy

Allen Browne said:
Type the expression into the Field row in query design:
=DateDiff("n", [Start Time], [End Time]) + IIf([End Time] < [Start Time],
1440, 0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Cindy said:
I need to put an IIF in a query to calculate the minutes from Start Time
to
End Time. It's in military time and of course goes past midnight on some
occasions. Using DateDiff in the Query Field block I get a negative
number
on those occasions.

I've read through all the threads here and found this that I think should
work but I don't know where to actually put it. I've tried it in the
Field
block and in the criteria block but as soon as I hit enter it clears the
field out.

IIF End Time <= Start Time Then
Me.TimeInMinutes = DateDiff("n", Start Time, End Time) + 1440
Else
Me.TimeInMinutes = DateDiff("n", Start Time, End Time)
End IIF

I hope someone can point me in the right direction here.

Thanks!
Cindy
Click to expand...

.
Click to expand...
 
Back
Top