Where to put an IIF in a query

[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

 

[Allen Browne]

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

 

[Cindy]

Once again, you are my hero. It worked perfectly.

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)

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

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

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

.