need help with an IIF statement

[scarter1456]

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.

 

[Andrzejek]

I don't think you posted entire IIF statement.

IIF is:[tt]
IIF(condition, If True do this, If False do this)
[/tt]
You do have the 'condition' part: bEstSoumis = True
But looks like yout True part starts with double quote: [tt]"1[/tt]
And your False part: [tt]DateSoumis = '" & Now & "' [/tt] has [tt])[/tt] 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.

 

[scarter1456]

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

 

[Andrzejek]

[tt]strSQL = strSQL & "UPDATE Soumission SET IndSoumis = " & IIf(bEstSoumis = True, "1, DateSoumis = '" & Now & "' ", "0")
[/tt]
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:
[tt]
Debug.Print strSQL
[/tt]

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.

 

[tedsmith]

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.

 

[strongm]

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

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

 

[dilettante]

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.

 

[tedsmith]

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!

 

[dilettante]

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.

 

[strongm]

>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)

 

[tedsmith]

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?

 

[tedsmith]

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.

 

[strongm]

>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/desktop/dd373795(v=vs.85).aspx

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.

 

[Andrzejek]

But to get back to the problem at hand, as I stated before the basic IIF syntax is:
[tt]
IIF([red]condition[/red],[green] If True do this[/green],[blue] If False do this[/blue])
[/tt]
So your (incomplete to me, or 'too much stuff') statement is:
[tt]
strSQL = strSQL & "UPDATE Soumission SET IndSoumis = " & IIf([red]bEstSoumis = True[/red],[green] "1[/green], [blue]DateSoumis = '" & Now & "' "[/blue], "0")
[/tt]

So your condition is checking if bEstSoumis = True
If True, you place [tt]"1[/tt]
If False, you do [tt]DateSoumis = '" & Now & "' "[/tt]
and I don't know what the [tt]"0"[/tt] is for...
So you either have an extra [tt]"1[/tt] or [tt]"0"[/tt] 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.

 

[guitarzan]

Andy,

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

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

 

[Andrzejek]

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

scarter1456 would have to have a table named [tt]Soumission[/tt] with field:
[tt]
IndSoumis
0
0
1, DateSoumis = '3/27/2015 19:37:16 AM'
0
1, DateSoumis = '3/30/2015 12:57:16 PM'
0
[/tt]

Looks weird to me...

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.

 

[guitarzan]

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:
[pre]UPDATE Soumission SET IndSoumis = 1, DateSoumis = '3/27/2015 19:37:16 AM'[/pre]

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

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.

 

[strongm]

Nope.

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

 

[Andrzejek]

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

But if guitarzan's statement is true, the syntax would have to be:
[tt]
strSQL = strSQL & "UPDATE Soumission SET "

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

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.

 

[guitarzan]

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

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

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