If vs if

[tedsmith]

I previously thought IFF was a preferred way than IF ELSE ENDIF (because it only takes one line instead of 5)
Testing by multiple calling the same sub with either method revealed IIF to be 3 times slower than IF ELSE END IF.
This highlights a possible speed problem using IIF in SQL statements to convert different row values to something else (you can't directly use IF)
Is there another way or would using an embedded Function be any better?

 

[tedsmith]

Thanks for all your efforts but - I cant see how I would implement this.
When I print <DateDiff("h",date, date + timeserial(left(MyTime,2),right(MyTime,2),0))> it gives 23 for a Mytime of "23:30" and 25 for a Mytime of "25:30"
Isn't it the same as saying Left(MyTime,2)?

If I ran an INTO query with this as WHERE, it would simply be ignored as all times are less that 26:00 and would fail to convert all times between "23:59" and "26:00" which was the original problem.

The IIF statement I indicated above was not a criteria, it was in effect the value of a function I updated the new table with.

I didn't have any WHERE statements and I fail to see how one could be used because WHERE is meant to exclude unwanted things that are discarded.

You must only subtract 2400 from any MyTime after 23:59 then store it but store the original Mytime otherwise.

But as was originally pointed out, IIF is not necessarily slower when used in a SQL statement anyway.

Alternately is it possible to somehow have a field updated differently in the one SQL statement, something in general like -
INSERT INTO NewTable (Mytime) IN 'C:\MyFolder\MyDatabase.mdb', SELECT Mytime(Where Mytime < than Midnight ), Mytime = MyTime-24 (Where Mytime > midnight) FROM OrigTable
In the above, the second WHERE would end up in the next column and throw all the other fields out by one.
I have never seen one yet - thats why I thought you had to do it in 2 runs.

 

[strongm]

It was an example, Ted, not a solution. It demonstrates how one can easily deal with "illegal times without error", how we can easily "convert 25:00 to Date/Time" without an error (and without IIF) and how we can pick up the all data in a single pass.

Of course you don't have to use a WHERE clause - but it was you that brought up that idea in the first place.

And so you might end up with something like:

[tt]INSERT INTO NewTable (MyTime)
SELECT Date()+TimeSerial(Left$([MyTime],2),Right$([MyTime],2),0)
FROM OrigTable[/tt]

Of course, whether this is any faster than IIF I don't know

 

[tedsmith]

Thanks, I now understand what you were getting at. Your inclusion of WHERE in your second last example was what had me confused.