INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

need help with an IIF statement

need help with an IIF statement

(OP)
HI

I found the IIF statement that I cannot understand and would like if someone could explain it to me. I think it's incorrect but I'm fairly new to VB6 and SQl.
strSQL = strSQL & "UPDATE Soumission SET IndSoumis = " & IIf(bEstSoumis = True, "1, DateSoumis = '" & Now & "' "
Thanks in advance for any clarity you can provide to explain this IIF to me.

RE: need help with an IIF statement

I don't think you posted entire IIF statement.

IIF is:
IIF(condition, If True do this, If False do this)

You do have the 'condition' part: bEstSoumis = True
But looks like yout True part starts with double quote: "1
And your False part: DateSoumis = '" & Now & "' has ) missing

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: need help with an IIF statement

(OP)
sorry here is the complete statement: IndSoumis = " & IIf(bEstSoumis = True, "1, DateSoumis = '" & Now & "' ", "0")

RE: need help with an IIF statement

strSQL = strSQL & "UPDATE Soumission SET IndSoumis = " & IIf(bEstSoumis = True, "1, DateSoumis = '" & Now & "' ", "0")

That still does not look like a complete statement (to me)
Can you run the code? Does it compile?
If so, could you show what you get if you do:

Debug.Print strSQL

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: need help with an IIF statement

Maybe it is part of a Query criteria because you have single quotes around the Now word.
This tells the query that Now is a string. Normally you would use # if DateSoumis was a column in Date format (or just delete the ' or #.
IndSoumis = " & IIf(bEstSoumis = True, "1, DateSoumis = #" & Now & "# ", "0")
It is necessary because Now is a separate function outside the IIF loop
What it would do would be to Make nDateSoumis = today's date time if bEstSoumis is true otherwise it would make it "0" such as recording the something else was added.

Using ' instead # of date stores the date-time as a text string in whatever the computer format instead of date/time format which can give wrong results later if used on a computer in another country. Using # you must use the non standard USA date format mm/dd/yyyy.

RE: need help with an IIF statement

>Using # you must use the non standard USA date format mm/dd/yyyy

You might want to check the documentation on that, Ted.

RE: need help with an IIF statement

Actually it is the universal ("invariant locale") format, and thus hardly "non standard" in any way, shape, or form.

If you cram other syntax in there Jet will do its best to try to guess what was meant, but it will always do so in the invariant locale context. This means you may get away with a lot of possible formatting:

#1/6/2001# = January 6th, 2001
#13/1/2001# = January 13th, 2001 because 13 is not a valid month, though incorrect
#2001-1-6# = January 6th, 2001, works though incorrect
#02/29/01# = February 1st, 2029, works though incorrect
#2001-13-1# fails

... and so on with other goofy things like use of month names.

Just do it right and avoid hard to debug errors. The parsing occurs somewhere in one of the OLE DLLs as far as I can tell.

RE: need help with an IIF statement

What I think of as a "standard" is what is generally accepted by the largest proportion of population in the world.
USA still uses the quaint Miles per hour for speed and even quotes stock exchange numbers in one eighths while the rest of the world has or is going decimal!
So happens Americans invented this software so they can call the shots.

When I don't use # in a query for a Jet database it doesn't always find it because it could be a string it treats it as a string if you have '3/4/2015' in your query string, particularly if the date field is formatted as a STRING (By someone else)
In our part of the DD/MM/YYYY world it doesn't always work because it is maybe comparing it as a string, x. That why I always include it.

Maybe this does not apply to computers in USA?

Format or CVDate can convert lots of variations to the local date format of your computer but this is different to what I was talking about.

I wonder when it will be decitime!

RE: need help with an IIF statement

No point in railing against things as they are.

And you always have the option of moving to non-Microsoft technologies that took perhaps more neutral paths. For example XML and JSON timestamps normally conform to ISO 8601 when used properly. However you don't get a choice about things like the decimal point character or the names for "true" and "false" values, etc. which again conform to the invariant locale.

So you might be more comfortable writing Java or Python or something else and using SQLite, MySQL, etc.

RE: need help with an IIF statement

>what is generally accepted by the largest proportion of population in the world

The Invariant Locale is a lot more than an a date format, and is very carefully defined, and is a known standard locale in Windows, one which is not associated with any actual country/region (there are some other important things about it, such as the fact that, unlike other locales, it cannot be modified by the user). Microsoft documentation states repeatedly that it is not intended for, nor should it be used for display (and thus the actual string form is casts to is basically irrelevant)

RE: need help with an IIF statement

I was under the impression that if a database field is formatted Date/Time, the actual data stored is a number. It depends on what country your computer is set to as to how it is displayed.

The problem with # I had I suggest stems from the fact that some databases I have encountered have the date column formatted as text so you could get a different result in different countries using the same "data"

You don't have to use a # when you include Now in a citeria string but I use it when using specific times like 10:30 am.
It seems odd in the original question of this thread that the Now is surrounded by single quotes which make me suspect it is treating Now as a if it were a string in whatever it was supposed to do.

Reading up on "invariant locale" I have yet to see such a collection of indecipherable circular mumbo-jumbo.
One I came across was Where is Invariant Locale? Answer:"Its ten miles south of communicado, and five miles east of Cognito." That sums it up well.
But the one that sums it all up in ultimate Microsoft-ese is "An invariant locale is a locale that is an invariant!"
Anybody got a link to a 'real explanation' for the record that I could explain to my great granddaughter?

RE: need help with an IIF statement

Don't get me wrong - I'm not railing - just disappointed that people by now haven't got the same standards for everything around the world.
The worst part with visitors driving on holidays in USA (or Europe) is when I get home, I continue to drive on the WRONG side of the road for a week or so!

The Brits rode their horses on the left side of the road so they would have their (right) sword hand free to defend themselves. The Yankees and French only adopted the opposite so they could sneak up on the redcoats left side (typical).

The curious thing about driving on the right is they still give way to the right even though their right hand view can be blocked by a passenger (wife) who refuses to look straight ahead with their head back when you come to an intersection.

RE: need help with an IIF statement

>I was under the impression that if a database field is formatted Date/Time, the actual data stored is a number. It depends on what country your computer is set to as to how it is displayed.

Correct

>Anybody got a link to a 'real explanation' for the record

https://msdn.microsoft.com/en-us/library/windows/d...

Not sure it'd work for your great granddaughter, though.

It is simply a locale that is NOT actually linked to any specific real, existing region. And (in a somewhat simplistic description) is used as a neutral, fixed, intermediate format to allow correct string comparisons between regions whose string formats and/or text sort ordering rules differ.

RE: need help with an IIF statement

But to get back to the problem at hand, as I stated before the basic IIF syntax is:

IIF(condition, If True do this, If False do this)

So your (incomplete to me, or 'too much stuff') statement is:

strSQL = strSQL & "UPDATE Soumission SET IndSoumis = " & IIf(bEstSoumis = True, "1, DateSoumis = '" & Now & "' ", "0")


So your condition is checking if bEstSoumis = True
If True, you place "1
If False, you do DateSoumis = '" & Now & "' "
and I don't know what the "0" is for...
So you either have an extra "1 or "0" in your statement.

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: need help with an IIF statement

Andy,

No. In IIf(bEstSoumis = True, "1, DateSoumis = '" & Now & "' ", "0")

If True, you place "1, DateSoumis = '" & Now & "' "
If False, you place "0"

RE: need help with an IIF statement

guitarzan, you may be right, but it is hard to see it in the data base.

scarter1456 would have to have a table named Soumission with field:

IndSoumis
0
0
1, DateSoumis = '3/27/2015 19:37:16 AM'
0
1, DateSoumis = '3/30/2015 12:57:16 PM'
0


Looks weird to me... ponder

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: need help with an IIF statement

They are simply building a SQL UPDATE statement! There are two fields, not one... IndSoumis and DateSoumis

If bEstSoumis is true, two fields get updated (IndSoumis and DateSoumis). The update statement would be:
UPDATE Soumission SET IndSoumis = 1, DateSoumis = '3/27/2015 19:37:16 AM' 

If bEstSoumis is false, only IndSoumis gets updated and DateSoumis is left alone. The update statement would be:
UPDATE Soumission SET IndSoumis = 0 

There is nothing syntactically or logically wrong with the statement!!! Now, whether the code works would depend on the data types of the fields and whether the code is doing what the OP needs it to do... things I cannot guess at.

RE: need help with an IIF statement

Nope.

He'd have to have a table with two fields: IndSoumis and DateSoumis.

RE: need help with an IIF statement

You both agree, OP has to have two fields: IndSoumis and DateSoumis smile

But if guitarzan's statement is true, the syntax would have to be:

strSQL = strSQL & "UPDATE Soumission SET "

strSQL = strSQL & IIF(bEstSoumis = True, "IndSoumis = 1, DateSoumis = '" & Now & "'", "IndSoumis = 0")


We'd better wait for scarter1456 to respond...

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: need help with an IIF statement

Andy's code:
strSQL = strSQL & "UPDATE Soumission SET "
strSQL = strSQL & IIF(bEstSoumis = True, "IndSoumis = 1, DateSoumis = '" & Now & "'", "IndSoumis = 0")
 

OP's code
strSQL = strSQL & "UPDATE Soumission SET IndSoumis = " & IIf(bEstSoumis = True, "1, DateSoumis = '" & Now & "' ", "0") 
 

This code produces exactly the same output. I'm not sure what you are not understanding.

RE: need help with an IIF statement

>the syntax would have to be:


No, it wouldn't. As guitarzan says, syntactically it is fine as it is.

RE: need help with an IIF statement

My apology, they are the same statements.
I guess I was so stuck on my way of seeing it that I could not see it the other way.
purpleface

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: need help with an IIF statement

I find thay putting Now in a query string without the quotes and & works anyway. ( Datesoumis = Now )
When I use an external variable I put ( Datesoumis = #" & Mydate & "# ) in which case Mydate is a specific date that could change.
To be sure I also check beforehand that Mydate is really a valid date or set it to Null if not otherwise you get an error.

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!

Resources

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