Conditional formatting affects the display

[Scott]

I have one field as below to show the contents of the fields; Model and
SupModel, depending on the availability of these fields and works correctly.

=IIf(IsNull([Model]),"(" & [SupModel] &
")",IIf(IsNull([SupModel]),[Model],[Model] & " (" & [SupModel] & ")"))

Once I add the following in conditional formatting of the field. When both
fields are available, the SupModel was not displayed at all but the field
glows to the second row. Can someone advise the possible causes and fixes
to it.

Expression Is [Field5] Is Not Null

Thanks,

Scott

 

[Allen Browne]

I don't see what's wrong with the expression, Scott, but try this:
=[Model] & "(" + [SupModel] + ")"

 

[Scott]

Allen,

Your expression is simpler and working as well. Thanks!

However, it still has my problem once the field is used the conditional
formatting. Obviously, the conditional formatting has bug to affect the
result. It cannot display the last row of the field (If the field of the
record has three rows, only the first two rows display. If the field of the
record has two rows, only the first row displays.) Is there any way to get
rid of it?

Thanks,

Scott

I don't see what's wrong with the expression, Scott, but try this:
=[Model] & "(" + [SupModel] + ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have one field as below to show the contents of the fields; Model and
SupModel, depending on the availability of these fields and works
correctly.

=IIf(IsNull([Model]),"(" & [SupModel] &
")",IIf(IsNull([SupModel]),[Model],[Model] & " (" & [SupModel] & ")"))

Once I add the following in conditional formatting of the field. When
both fields are available, the SupModel was not displayed at all but the
field glows to the second row. Can someone advise the possible causes
and fixes to it.

Expression Is [Field5] Is Not Null

Thanks,

Scott

 

[Allen Browne]

Okay the issue might be one of timing, i.e. when Access calcualtes the
expression verses when it calcualtes the conditional formatting.

Could use use the whole expression in the CF intead of the name of the
calculated control? The CF expression could be:
IsNull([Model]) And IsNull([SupModel])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Your expression is simpler and working as well. Thanks!

However, it still has my problem once the field is used the conditional
formatting. Obviously, the conditional formatting has bug to affect the
result. It cannot display the last row of the field (If the field of the
record has three rows, only the first two rows display. If the field of
the record has two rows, only the first row displays.) Is there any way
to get rid of it?

Thanks,

Scott

I don't see what's wrong with the expression, Scott, but try this:
=[Model] & "(" + [SupModel] + ")"

I have one field as below to show the contents of the fields; Model and
SupModel, depending on the availability of these fields and works
correctly.

=IIf(IsNull([Model]),"(" & [SupModel] &
")",IIf(IsNull([SupModel]),[Model],[Model] & " (" & [SupModel] & ")"))

Once I add the following in conditional formatting of the field. When
both fields are available, the SupModel was not displayed at all but the
field glows to the second row. Can someone advise the possible causes
and fixes to it.

Expression Is [Field5] Is Not Null

Thanks,

Scott

 

[Scott]

Allen,

I have located the source of error. My original format must be in bold (was
normal) if the CF is red background and bold white font on condition. Very
strange!

Regarding concatenation using + and &, I tried the following and it works.

=([FieldA]+" ") & ([FieldB]+" ")

But not working with three fields.

=(([FieldA]+" ") & ([FieldB]+" ") & [FieldC])

Do you have any idea where is wrong?

Thanks,

Scott

Okay the issue might be one of timing, i.e. when Access calcualtes the
expression verses when it calcualtes the conditional formatting.

Could use use the whole expression in the CF intead of the name of the
calculated control? The CF expression could be:
IsNull([Model]) And IsNull([SupModel])


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Your expression is simpler and working as well. Thanks!

However, it still has my problem once the field is used the conditional
formatting. Obviously, the conditional formatting has bug to affect the
result. It cannot display the last row of the field (If the field of the
record has three rows, only the first two rows display. If the field of
the record has two rows, only the first row displays.) Is there any way
to get rid of it?

Thanks,

Scott

I don't see what's wrong with the expression, Scott, but try this:
=[Model] & "(" + [SupModel] + ")"

I have one field as below to show the contents of the fields; Model and
SupModel, depending on the availability of these fields and works
correctly.

=IIf(IsNull([Model]),"(" & [SupModel] &
")",IIf(IsNull([SupModel]),[Model],[Model] & " (" & [SupModel] & ")"))

Once I add the following in conditional formatting of the field. When
both fields are available, the SupModel was not displayed at all but
the field glows to the second row. Can someone advise the possible
causes and fixes to it.

Expression Is [Field5] Is Not Null

Thanks,

Scott

 

[Allen Browne]

Could be several things, such as the presence of zero-length strings or
spaces instead of Nulls.

Testing with IsNull() might help to pin it down.

The basic difference is that:
"A" & Null produces "A"
"A" + Null produces Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have located the source of error. My original format must be in bold
(was normal) if the CF is red background and bold white font on condition.
Very strange!

Regarding concatenation using + and &, I tried the following and it works.

=([FieldA]+" ") & ([FieldB]+" ")

But not working with three fields.

=(([FieldA]+" ") & ([FieldB]+" ") & [FieldC])

Do you have any idea where is wrong?

Thanks,

Scott

Okay the issue might be one of timing, i.e. when Access calcualtes the
expression verses when it calcualtes the conditional formatting.

Could use use the whole expression in the CF intead of the name of the
calculated control? The CF expression could be:
IsNull([Model]) And IsNull([SupModel])

Allen,

Your expression is simpler and working as well. Thanks!

However, it still has my problem once the field is used the conditional
formatting. Obviously, the conditional formatting has bug to affect the
result. It cannot display the last row of the field (If the field of
the record has three rows, only the first two rows display. If the
field of the record has two rows, only the first row displays.) Is
there any way to get rid of it?

Thanks,

Scott

I don't see what's wrong with the expression, Scott, but try this:
=[Model] & "(" + [SupModel] + ")"

I have one field as below to show the contents of the fields; Model and
SupModel, depending on the availability of these fields and works
correctly.

=IIf(IsNull([Model]),"(" & [SupModel] &
")",IIf(IsNull([SupModel]),[Model],[Model] & " (" & [SupModel] & ")"))

Once I add the following in conditional formatting of the field. When
both fields are available, the SupModel was not displayed at all but
the field glows to the second row. Can someone advise the possible
causes and fixes to it.

Expression Is [Field5] Is Not Null

 

[Scott]

Allen,

Thanks for your advice. Is it possible to concatenate a text field and a
memo field?

Scott

Could be several things, such as the presence of zero-length strings or
spaces instead of Nulls.

Testing with IsNull() might help to pin it down.

The basic difference is that:
"A" & Null produces "A"
"A" + Null produces Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I have located the source of error. My original format must be in bold
(was normal) if the CF is red background and bold white font on
condition. Very strange!

Regarding concatenation using + and &, I tried the following and it
works.

=([FieldA]+" ") & ([FieldB]+" ")

But not working with three fields.

=(([FieldA]+" ") & ([FieldB]+" ") & [FieldC])

Do you have any idea where is wrong?

Thanks,

Scott

Okay the issue might be one of timing, i.e. when Access calcualtes the
expression verses when it calcualtes the conditional formatting.

Could use use the whole expression in the CF intead of the name of the
calculated control? The CF expression could be:
IsNull([Model]) And IsNull([SupModel])


Allen,

Your expression is simpler and working as well. Thanks!

However, it still has my problem once the field is used the conditional
formatting. Obviously, the conditional formatting has bug to affect
the result. It cannot display the last row of the field (If the field
of the record has three rows, only the first two rows display. If the
field of the record has two rows, only the first row displays.) Is
there any way to get rid of it?

Thanks,

Scott

I don't see what's wrong with the expression, Scott, but try this:
=[Model] & "(" + [SupModel] + ")"

I have one field as below to show the contents of the fields; Model
and SupModel, depending on the availability of these fields and works
correctly.

=IIf(IsNull([Model]),"(" & [SupModel] &
")",IIf(IsNull([SupModel]),[Model],[Model] & " (" & [SupModel] &
")"))

Once I add the following in conditional formatting of the field.
When both fields are available, the SupModel was not displayed at all
but the field glows to the second row. Can someone advise the
possible causes and fixes to it.

Expression Is [Field5] Is Not Null

 

[Allen Browne]

Sure. Give it a try.

Most scenarios are fine. There are some cautions when doing this in a UNION
query. Or in a recordset:
http://allenbrowne.com/bug-16.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thanks for your advice. Is it possible to concatenate a text field and a
memo field?

Scott

Could be several things, such as the presence of zero-length strings or
spaces instead of Nulls.

Testing with IsNull() might help to pin it down.

The basic difference is that:
"A" & Null produces "A"
"A" + Null produces Null

Allen,

I have located the source of error. My original format must be in bold
(was normal) if the CF is red background and bold white font on
condition. Very strange!

Regarding concatenation using + and &, I tried the following and it
works.

=([FieldA]+" ") & ([FieldB]+" ")

But not working with three fields.

=(([FieldA]+" ") & ([FieldB]+" ") & [FieldC])

Do you have any idea where is wrong?

Thanks,

Scott


Okay the issue might be one of timing, i.e. when Access calcualtes the
expression verses when it calcualtes the conditional formatting.

Could use use the whole expression in the CF intead of the name of the
calculated control? The CF expression could be:
IsNull([Model]) And IsNull([SupModel])


Allen,

Your expression is simpler and working as well. Thanks!

However, it still has my problem once the field is used the
conditional formatting. Obviously, the conditional formatting has bug
to affect the result. It cannot display the last row of the field (If
the field of the record has three rows, only the first two rows
display. If the field of the record has two rows, only the first row
displays.) Is there any way to get rid of it?

Thanks,

Scott

I don't see what's wrong with the expression, Scott, but try this:
=[Model] & "(" + [SupModel] + ")"

I have one field as below to show the contents of the fields; Model
and SupModel, depending on the availability of these fields and works
correctly.

=IIf(IsNull([Model]),"(" & [SupModel] &
")",IIf(IsNull([SupModel]),[Model],[Model] & " (" & [SupModel] &
")"))

Once I add the following in conditional formatting of the field.
When both fields are available, the SupModel was not displayed at
all but the field glows to the second row. Can someone advise the
possible causes and fixes to it.

Expression Is [Field5] Is Not Null