Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


If vs if

If vs if

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?

RE: If vs if

Firstly, given that VB6 is compiled using an optimising compiler, the number of lines taken up by source code is hardly ever an accurate reflection of the efficiency or size of the of the compiled code.

Secondly Iif is indeed inefficient compared to If, as it always evaluates both the true and false clauses.

Thirdly, using any VBA functions in a SQL statement (which Jet-SQL allows) is always relatively inefficient (and makes the code non-portable to other more standard SQL dialects)

RE: If vs if

The IIf function can't shortcut by its very nature: it is a function accepting ByVal arguments. So of course it evaluates both "branches."

I wouldn't leap to a conclusion that using functions from the Jet Expression Service is "relatively inefficient" because sometimes the alternative performs far worse. So I'd reject such a blanket statement, though I can understand the point.

Portability could be a stronger argument. Not much stronger since few ever face it and when it does arise there are usually easy alternatives. But it does mean more work to convert to other non-standard SQL dialects like T-SQL.

Quote (Wikipedia)

Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. SQL, the acronym for Structured Query Language, is a standardized computer language that was originally developed by IBM for querying, altering and defining relational databases, using declarative statements. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.

RE: If vs if

>Jet Expression Service

Fair point. I was avoiding making the distinction between VBA and JES (mainly because the JES version of IIF does NOT evaluate both branches), and my comment was related to Ted's timings in VB6, and hence VBA, which does. ANd it is certainly the case that JES functions may well run quite efficiently. Your own VBA funtions, however, used in a JET SQL statement may not.

RE: If vs if

Just goes to show things don't have universal cookbook answers. Even things that look alike don't always work alike.

Personally though I'd love to find a way to expose functions written in VB6 to be callable within Jet expressions. While just adding more burden to future porting, most of my uses would involve Jet as a local datastore and not a general purpose database anyway so porting wouldn't arise.

I assume MS Access does this through private entrypoints in the Jet engine, setting up some sort of callback.

RE: If vs if

I wonder why they called it Visual BASIC?
So how would you use (portable) SQL to convert a single row to something else depending on it's value?
(equivalent of IIF(Value=1,Red,Green))

RE: If vs if

The usual advice is to use the CASE ... WHEN expression if IIF is not available - but this does not exist in Jet SQL.

But please read this thread again: IIF is slower than IF in VB6 (for reasons outlined above), but not in Jet SQL, because a) there is no IF to compare it against and b) it is implemented differently and so not subject to the same slowdown as the VB version.

RE: If vs if

"why they called it Visual BASIC?" (tedsmith)
I just found out, BASIC stands for: "Beginner's All-purpose Symbolic Instruction Code"
I didn't know that... smile

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: If vs if

Visual Basic is not really BASIC. Sure, it is ultimately derives from BASIC, but is a very different beast (and that's why it isn't uppercase)

RE: If vs if

VB just stayed syntactically compatible with early Microsoft Basics, making it easy to port forward from version to version until VB.Net broke the mold radically.

RE: If vs if


Why do you need to do this?

Has it to do with how the records are displayed?

RE: If vs if

One reason I currently have is that in one strange users system they use a 26 hour day instead of DATE AND TIME,
Historically this was because they originally has a 300baud modem telephone hookup (before the days of the internet) and they were open for business until 2am each morning.
To save time and storage space, rather than send "20/2/15 11:55 PM" before midnight and "21/2/15 1:05 AM" (16 characters) ten minutes later they sent "2355" and "2405" (4 characters). Any time over 23:59 produces an error if you try to use it as regular time.

One reason I use IIF is to convert OldTime to normal date time format for a more modern database.
I test if the hour part > 24 and subtract 24 from it then format with ":" and the minutes to get AM/PM from the result, advancing the date only if >24. http://www.tek-tips.com/viewthread.cfm?qid=1744974

RE: If vs if

>for a more modern database.

Ok, so it's interfacing to another database.

(For display purposes in, for instance, a bound Gridcontrol, among other things, I could have offered a different, faster solution.)

Couldn't you just add and set a field to hold the 4-char time in the receiving database table, and then just run an UPDATE sql statement on the data there?
Then you would use the WHERE clause instead of the IIF() function.
That is usually just as fast, and sometimes even faster, especially with alot of data to process.

You could also send the data to a second table in a temporary local profil mdb, (to avoid bloating), to hold the data in between in order to further process with an UPDATE statement.
Your sql statements would also be more readable and portable.

RE: If vs if

Thanks CP60
I ended up making a new table to do all the final querying on anyway but I still had to determine whether the time was past midnight to be able to make the use of the original data table time column (that was Text format)
The format of the time column in the new table is in date format so I can easily query it.
If I used WHERE I would have to run it twice, once for under 24:00 and again for after midnight then combine them. I think this would be much slower than IIF.

RE: If vs if

>If I used WHERE I would have to run it twice, once for under 24:00 and again for after midnight then combine them


RE: If vs if

...and Dartmouth BASIC is the first version of BASIC.

RE: If vs if

Because if you try to convert 25:00 to Date/Time you get an error.
Without IIF you'd have to run at least two procedures which would make it even slower

Doing with a IIF I used in one pass
"CVDate(IIf([Mytimesecs]<86399,(Date() & ' ' & Format([MyTime],'h:nn:ss AM/PM'))," _
& "(Date()+1) & ' ' & Format((LTrim(Val(Left([MyTime],2)-24)) & Mid([MyTime],3)),"

This added 1 day to date and subtracted 24 hours from the time where it was more then 25:59. The database contained a column that was the seconds past midnight as well as a text column for the time that ended at 26:00, an illegal computer time

As it is only run when you want the result, the day is automatically always today or tomorrow only for times after midnight.

If I had used WHERE I would have had to run it once for times before midnight and again for those times after midnight with a similar calculation because you can't otherwise ignore the illegal times without error.

In MSAccess this could also have been done using a function in the Criteria but I have never been able to get functions to work in VB6 criteria (not that I have tried very hard!)

RE: If vs if


I think that was being proposed as a possibly faster option than to use the IIF was something like this


create table xx
(startdate datetime2
,enddate datetime2
,mytimex varchar(4)

insert into xx values(convert(date,getdate()),null,'2355')
insert into xx values(convert(date,getdate()),null,'2405')
select *
from xx
update xx
set enddate = case
              when convert(int,substring(mytimex,1,2)) <= 23
                   then convert(date, convert(varchar(10),startdate,121) + ' ' + stuff(mytimex,3,0,':') + ':00',121)
              else convert(date, convert(varchar(10),dateadd(d,1, startdate),121) + ' '
               + stuff(right('0000' + convert(varchar(4), convert(int, mytimex) - 2400),4),3,0,':') + ':00',121)
where enddate is null
select *
from xx 

so you do inserts into your db, do update of the fields as required (and with further validation as my assumption above is that the field is always populated, that it never goes past 2 days worth of minutes, and that the value is always a valid time if we subtract 2400 - if either of these isn't always true further validation would be required), and from that point on you only use the correct datetime field on your queries with no further conversion required

and as for "If I used WHERE I would have to run it twice, once for under 24:00 and again for after midnight then combine them"

as strongm was querying, the above case statement could be used on your queries (with changes as required to your needs) to do the select in one go (although not the faster way possibly)


Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: If vs if

WHERE DateDiff("h",date, date + timeserial(left(MyTime,2),right(MyTime,2),0))<=26

RE: If vs if

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.

RE: If vs if

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:

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

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

RE: If vs if

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close