How do I insert a coloured symbol in an Access field please?

[New Ripper]

I use a caculated field to show a symbol if the contents of another field
"isnull".
I want a red disk if nil, and a green disk if it has value.
I can do it using black ring and white ring symbols but I have tried to use
Word to change the font colour then cut and paste into my expression. The
symbols revert to black and white.
Any ideas please?

 

[fredg]

I use a caculated field to show a symbol if the contents of another field
"isnull".
I want a red disk if nil, and a green disk if it has value.
I can do it using black ring and white ring symbols but I have tried to use
Word to change the font colour then cut and paste into my expression. The
symbols revert to black and white.
Any ideas please?

You can't within the field, and certainly not in a query.
In a report, what you can do is have 2 images, one a red ring image
and the other a green ring image positioned one over the other and
make one or the other visible as needed, using code in the Report
section's Format event.

If IsNull([SomeControl]) Then
Image1.Visible = True
Image2.Visible = False
Else
Image1.Visible = False
Image2.Visible = True
End If

 

[Marshall Barton]

I use a caculated field to show a symbol if the contents of another field
"isnull".
I want a red disk if nil, and a green disk if it has value.
I can do it using black ring and white ring symbols but I have tried to use
Word to change the font colour then cut and paste into my expression. The
symbols revert to black and white.

Use a text box in your form/report. Set the text box's Font
to one that has a circle character and set its control
source expression to:
=[other field] Is Null

Then use a custom format specification in the text box's
Format property:
;[Red]\X;[Green]\X

where X is the charater code for the font's circle character

If you don't like the shade of the red or green the above
uses, then forget the format property and use Conditional
Formatting where you can get any color. With the CF
approach, set the text box expression to:
="X"
and use the CF expression:
[other field] Is Null

 

[New Ripper]

Many thanks Fred.
Maybe I should have mentioned that the query is linked to a list box and the
calculated field is visible in that box. Currently I can display the black
blob or the white blob (or Y and N whatever takes your fancy!) but I can't
colour the font other than black. So I'm on a form, not in a report and I
can't see that I can superimpose the images as they need to move up and down
with the scrollbar - unless I've misunderstood you.
I've seen it done elsewhere - unless they were disguising a report as a form
and the amount of code for that seems a mamouth task.
Regards
Roger

I use a caculated field to show a symbol if the contents of another field
"isnull".
I want a red disk if nil, and a green disk if it has value.
I can do it using black ring and white ring symbols but I have tried to use
Word to change the font colour then cut and paste into my expression. The
symbols revert to black and white.
Any ideas please?

You can't within the field, and certainly not in a query.
In a report, what you can do is have 2 images, one a red ring image
and the other a green ring image positioned one over the other and
make one or the other visible as needed, using code in the Report
section's Format event.

If IsNull([SomeControl]) Then
Image1.Visible = True
Image2.Visible = False
Else
Image1.Visible = False
Image2.Visible = True
End If

 

[New Ripper]

Hi Marsh
I sort of got this to work although I had to set the bound field in the list
box to the one that the text box was looking at. However the text box didn't
refresh when I scrolled down the list box. I really need to poke a coloured
symbol into the expression field in the query (memories of early machine code
and peek & poke!!!). Here's my expression from the query :-
Flag: IIf(IsNull([date out]),"X","O")
Or am I asking the impossible?
Even if I get the text box to sync with the list box (showing 8 lines) it
means quite a bit to line up 8 mini text boxes - not good programming
technique, I fear.
Look forward to ore input!
Regards and thanks
Roger

I use a caculated field to show a symbol if the contents of another field
"isnull".
I want a red disk if nil, and a green disk if it has value.
I can do it using black ring and white ring symbols but I have tried to use
Word to change the font colour then cut and paste into my expression. The
symbols revert to black and white.

Use a text box in your form/report. Set the text box's Font
to one that has a circle character and set its control
source expression to:
=[other field] Is Null

Then use a custom format specification in the text box's
Format property:
;[Red]\X;[Green]\X

where X is the charater code for the font's circle character

If you don't like the shade of the red or green the above
uses, then forget the format property and use Conditional
Formatting where you can get any color. With the CF
approach, set the text box expression to:
="X"
and use the CF expression:
[other field] Is Null

 

[Marshall Barton]

I sort of got this to work although I had to set the bound field in the list
box to the one that the text box was looking at. However the text box didn't
refresh when I scrolled down the list box. I really need to poke a coloured
symbol into the expression field in the query (memories of early machine code
and peek & poke!!!). Here's my expression from the query :-
Flag: IIf(IsNull([date out]),"X","O")

I think you are doomed if you insist on using a list box.
There just isn't enough things you can do with it.

Don't tell anyone, nut I have never used a list box in a
real database. Instead I always use a continuous subform.
In your case it should be fairly simple to create a subform
that looks like a list box but you can the add your colored
dot text box to the subform without a scrolling problem.

That should be all you need if you are just viewing or
selecting a single item.

OTOH, there is some work involved if you want to use some
kind of multi-select capability.

 

[New Ripper]

I sort of got this to work although I had to set the bound field in the list
box to the one that the text box was looking at. However the text box didn't
refresh when I scrolled down the list box. I really need to poke a coloured
symbol into the expression field in the query (memories of early machine code
and peek & poke!!!). Here's my expression from the query :-
Flag: IIf(IsNull([date out]),"X","O")

I think you are doomed if you insist on using a list box.
There just isn't enough things you can do with it.

Don't tell anyone, nut I have never used a list box in a
real database. Instead I always use a continuous subform.
In your case it should be fairly simple to create a subform
that looks like a list box but you can the add your colored
dot text box to the subform without a scrolling problem.

That should be all you need if you are just viewing or
selecting a single item.

OTOH, there is some work involved if you want to use some
kind of multi-select capability.

Many thanks Marsh,
As the list box is view-only I'll do it your way. That solves my problem.
I guess the word "nut" was a typo for "but" otherwise you've probably summed
me up in one word! Just joking, I am very grateful to you.
Many thanks
Roger

 

[Marshall Barton]

That really was a typo. I didn't mean to call you a nut,
honestly I didn't, really ... ;-)

You're welcome and I hope you get the effect you want.
We're always here if you need more ideas ;-)
--
Marsh
MVP [MS Access]

I sort of got this to work although I had to set the bound field in the list
box to the one that the text box was looking at. However the text box didn't
refresh when I scrolled down the list box. I really need to poke a coloured
symbol into the expression field in the query (memories of early machine code
and peek & poke!!!). Here's my expression from the query :-
Flag: IIf(IsNull([date out]),"X","O")

I think you are doomed if you insist on using a list box.
There just isn't enough things you can do with it.

Don't tell anyone, nut I have never used a list box in a
real database. Instead I always use a continuous subform.
In your case it should be fairly simple to create a subform
that looks like a list box but you can the add your colored
dot text box to the subform without a scrolling problem.

That should be all you need if you are just viewing or
selecting a single item.

OTOH, there is some work involved if you want to use some
kind of multi-select capability.

Many thanks Marsh,
As the list box is view-only I'll do it your way. That solves my problem.
I guess the word "nut" was a typo for "but" otherwise you've probably summed
me up in one word! Just joking, I am very grateful to you.