If/Then in Access 2007

[Thomas Hirschmann]

I can't figure out the "IF" code for the follwong list I'm trying to compile:

The "Table" field I'm trying to query is "Office111". If that field is not empty, a "+" should be added to the output document. If the "Office111" field is empty, then nothing should be added. I tried this but I'm getting "The expression you entered contains invalid syntax".

CHNummer: IIf([Office111]<>"" "+") & "" & [Office1] & " " & [Office11] & " " & [Office111]

Any help would be most appreciate. Thanks.
Tom


EggHeadCafe - Software Developer Portal of Choice
Full-Text Search with SQL Server 2000
http://www.eggheadcafe.com/tutorial...52-e6f5b6320397/fulltext-search-with-sql.aspx

 

[vanderghast]

IIf([Office111]<>"" , "+" , "" )
& [Office1] & " " & [Office11] & " " & [Office111]


Note that when you say "empty" , I assume you mean just that, a string with
no character in it. IT IS NOT the same as being NULL (unkown data, not
available, not appropriate, ... ). If you have to include also this case,
try:

iif( 0 <> len(office111 & "") , "+", "" )
& ...



Vanderghast, Access MVP



in message
news:[email protected]...

 

[Dale Fye]

Michel

Any particular reason why you used the syntax:

0 <> len(office111 & "")

instead of:

len(office111 & "") <> 0

I've always put the expression to be evaluated on the left of the comparison
operator(s) , and was just wondering whether there is a reason for doing it
in reverse?

----
Dale

IIf([Office111]<>"" , "+" , "" )
& [Office1] & " " & [Office11] & " " & [Office111]


Note that when you say "empty" , I assume you mean just that, a string with
no character in it. IT IS NOT the same as being NULL (unkown data, not
available, not appropriate, ... ). If you have to include also this case,
try:

iif( 0 <> len(office111 & "") , "+", "" )
& ...



Vanderghast, Access MVP



in message

I can't figure out the "IF" code for the follwong list I'm trying to
compile:

The "Table" field I'm trying to query is "Office111". If that field is not
empty, a "+" should be added to the output document. If the "Office111"
field is empty, then nothing should be added. I tried this but I'm getting
"The expression you entered contains invalid syntax".

CHNummer: IIf([Office111]<>"" "+") & "" & [Office1] & " " & [Office11] & "
" & [Office111]

Any help would be most appreciate. Thanks.
Tom


EggHeadCafe - Software Developer Portal of Choice
Full-Text Search with SQL Server 2000
http://www.eggheadcafe.com/tutorial...52-e6f5b6320397/fulltext-search-with-sql.aspx

.

 

[Marshall Barton]

I can't figure out the "IF" code for the follwong list I'm trying to compile:

The "Table" field I'm trying to query is "Office111". If that field is not empty,
a "+" should be added to the output document. If the "Office111" field is
empty, then nothing should be added.

CHNummer: IIf([Office111]<>"" "+") & "" & [Office1] & " " & [Office11] & " " & [Office111]

What does Office1 and Office11 have to do with it?

If they are irrelevant, try using:
CHNummer: IIf(Office111 Is Null, Null, "+")

If the Office111 field in the table has its AllowZeroLength
property set to Yes, then you might(?) need to use:
CHNummer: IIf(Nz(Office111,"") <> "", Null, "+")

 

[vanderghast]

Simple twist I learnt from C/C++, starting with the constant you want to
check rather than the variable you want to check, but any 'side' is ok. In
C/C++, = only means assignment, it is == which is used for comparison, so:

0 = variable

is catch by the compiler to be an error (you cannot assign a constant)
while what you want is probably a test: 0 == variable. Doing

variable = 0

won't catch the error, and will place a zero in your variable. Bug hard to
catch. So the 'twist' is to educate your mind to start with the constant.
That possible bug does not exist in C#, since

if( variable = 0 )

is an error (can't cast an integer, 0, to a Boolean). The bug does not
exists in VBA since = means and an assignment, and a comparison test and the
compiler catches which one is which from the context.


Vanderghast, Access MVP

Michel

Any particular reason why you used the syntax:

0 <> len(office111 & "")

instead of:

len(office111 & "") <> 0

I've always put the expression to be evaluated on the left of the
comparison
operator(s) , and was just wondering whether there is a reason for doing
it
in reverse?

----
Dale

IIf([Office111]<>"" , "+" , "" )
& [Office1] & " " & [Office11] & " " & [Office111]


Note that when you say "empty" , I assume you mean just that, a string
with
no character in it. IT IS NOT the same as being NULL (unkown data, not
available, not appropriate, ... ). If you have to include also this case,
try:

iif( 0 <> len(office111 & "") , "+", "" )
& ...



Vanderghast, Access MVP



in message

I can't figure out the "IF" code for the follwong list I'm trying to
compile:

The "Table" field I'm trying to query is "Office111". If that field is
not
empty, a "+" should be added to the output document. If the "Office111"
field is empty, then nothing should be added. I tried this but I'm
getting
"The expression you entered contains invalid syntax".

CHNummer: IIf([Office111]<>"" "+") & "" & [Office1] & " " & [Office11]
& "
" & [Office111]

Any help would be most appreciate. Thanks.
Tom


EggHeadCafe - Software Developer Portal of Choice
Full-Text Search with SQL Server 2000
http://www.eggheadcafe.com/tutorial...52-e6f5b6320397/fulltext-search-with-sql.aspx

.

 

[Dale Fye]

Thanks for the explanation.

Dale

Simple twist I learnt from C/C++, starting with the constant you want to
check rather than the variable you want to check, but any 'side' is ok. In
C/C++, = only means assignment, it is == which is used for comparison, so:

0 = variable

is catch by the compiler to be an error (you cannot assign a constant)
while what you want is probably a test: 0 == variable. Doing

variable = 0

won't catch the error, and will place a zero in your variable. Bug hard to
catch. So the 'twist' is to educate your mind to start with the constant.
That possible bug does not exist in C#, since

if( variable = 0 )

is an error (can't cast an integer, 0, to a Boolean). The bug does not
exists in VBA since = means and an assignment, and a comparison test and
the compiler catches which one is which from the context.


Vanderghast, Access MVP

Michel

Any particular reason why you used the syntax:

0 <> len(office111 & "")

instead of:

len(office111 & "") <> 0

I've always put the expression to be evaluated on the left of the
comparison
operator(s) , and was just wondering whether there is a reason for doing
it
in reverse?

----
Dale

IIf([Office111]<>"" , "+" , "" )
& [Office1] & " " & [Office11] & " " & [Office111]


Note that when you say "empty" , I assume you mean just that, a string
with
no character in it. IT IS NOT the same as being NULL (unkown data, not
available, not appropriate, ... ). If you have to include also this
case,
try:

iif( 0 <> len(office111 & "") , "+", "" )
& ...



Vanderghast, Access MVP



in message
I can't figure out the "IF" code for the follwong list I'm trying to
compile:

The "Table" field I'm trying to query is "Office111". If that field is
not
empty, a "+" should be added to the output document. If the
"Office111"
field is empty, then nothing should be added. I tried this but I'm
getting
"The expression you entered contains invalid syntax".

CHNummer: IIf([Office111]<>"" "+") & "" & [Office1] & " " & [Office11]
& "
" & [Office111]

Any help would be most appreciate. Thanks.
Tom


EggHeadCafe - Software Developer Portal of Choice
Full-Text Search with SQL Server 2000
http://www.eggheadcafe.com/tutorial...52-e6f5b6320397/fulltext-search-with-sql.aspx

.

 

[Thomas Hirschmann]

TO ALL... THANK YOU VERY MUCH FOR YOUR HELP. This finally worked:

IIf(0<>Len([office111] & ""),"+","") & "" & [Office1] & " " & [Office11] & " " & [Office111]

Where a "+" would be added if the field "Office111" is not empty.

BUT? I still got a Syntax error. Got really suspicious and checked my settings in "Regional and Language Options" in "Control Panel".

Turns out that under "Customize this format?" the setting of the "Digit grouping symbol" was set to " ' " instead of ",". Changed it back? syntax error was gone!

Again many thanks for your help
Tom




Thomas Hirschmann wrote:

If/Then in Access 2007
27-Oct-09

I can't figure out the "IF" code for the follwong list I'm trying to compile:

The "Table" field I'm trying to query is "Office111". If that field is not empty, a "+" should be added to the output document. If the "Office111" field is empty, then nothing should be added. I tried this but I'm getting "The expression you entered contains invalid syntax".

CHNummer: IIf([Office111]<>"" "+") & "" & [Office1] & " " & [Office11] & " " & [Office111]

Any help would be most appreciate. Thanks.
Tom

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Create Progress bar in windows application
http://www.eggheadcafe.com/tutorial...5-c8b7ff417a5e/create-progress-bar-in-wi.aspx