Comma in concatenated field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a test box that combines several fields using IIf statment
=IIf([cofa]=-1,"AAAAAA") & IIf([cofconf]=-1," , BBBBBB") &
IIf([cofcomp]=-1," , CCCCC")

My problem is with the comma if [cofa]=0 and [cofconf]=-1 or [cofcomp]=-1
then the ouput will be
,BBBBBB or ,CCCCC
How to manage the comma to appear only , if there is a value before it.

Thank you for your help
 
Bassel said:
I have a test box that combines several fields using IIf statment
=IIf([cofa]=-1,"AAAAAA") & IIf([cofconf]=-1," , BBBBBB") &
IIf([cofcomp]=-1," , CCCCC")

My problem is with the comma if [cofa]=0 and [cofconf]=-1 or [cofcomp]=-1
then the ouput will be
,BBBBBB or ,CCCCC
How to manage the comma to appear only , if there is a value before it.
Click to expand...


Here's one way:

=Mid(IIf(cofa,", AAAAAA") & IIf(cofconf,", BBBBBB") &
IIf(cofcomp,", CCCCC"), 3)
 
It looks to me your construction is wrong or you mistyped the expression:
Each IIf() usage requires 3 arguments and you have 2 in each IIf().

Check Access VB Help on the syntax of IIf().
 
Van said:
It looks to me your construction is wrong or you mistyped the expression:
Each IIf() usage requires 3 arguments and you have 2 in each IIf().

Check Access VB Help on the syntax of IIf().
Click to expand...


But, Van, this isn't VBA, it's a control source expression
and the expression service's IIf does allow the third arg to
be defaulted to Null.
 
Thanks, Marsh

Both Access Help and Access VB Help state that the 3 arguments are required
so I always use 3 arguments.

There are some inconsistencies here. I did a quick test using IIf as
ControlSource for a TextBox.

= IIf(1>2, "Some Text")

is OK but:

= IIf(1>2, , "Some Text")

gives syntax error and to fix it, I need to use:

= IIf(1>2, Null, "Some Text")

It seems that the expression serive allows us to omit arguments at the end
of the argument list but not arguments in the middle. In fact, I recently
found that using a UDF with middle optional arguments left blank (and values
for end arguments provided) is not acceptable to Access. It seems to be the
same problem with the IIf() here.

I haven't seen any documentation about why Expression Service interprets VBA
(inbult and user-defined) functions differently from VBA. Have you seen any
doc that details the differences?
 
Van said:
Both Access Help and Access VB Help state that the 3 arguments are required
so I always use 3 arguments.

There are some inconsistencies here. I did a quick test using IIf as
ControlSource for a TextBox.

= IIf(1>2, "Some Text")

is OK but:

= IIf(1>2, , "Some Text")

gives syntax error and to fix it, I need to use:

= IIf(1>2, Null, "Some Text")

It seems that the expression serive allows us to omit arguments at the end
of the argument list but not arguments in the middle. In fact, I recently
found that using a UDF with middle optional arguments left blank (and values
for end arguments provided) is not acceptable to Access. It seems to be the
same problem with the IIf() here.

I haven't seen any documentation about why Expression Service interprets VBA
(inbult and user-defined) functions differently from VBA. Have you seen any
doc that details the differences?
Click to expand...


Now that you mention it, Van, I don't remember seeing any
documents for it either, maybe the old A2 manuals explained
it?? I guess it's now part of the folklore that's been
passed around since forever. It's pretty obvious that the
expression service is definitely different than VBA
expressions/functions. Is Null, the @ feature in MsgBox,
the non-bitwise operation of AND and OR, the IN operator and
the IIf function are the primary variations I try to keep in
mind.
 
Back
Top