Conditional formatting maybe??

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

Guest

I have a question to see if this will work with conditional formatting since
as to date, I can't get it to work.

I have a staff roster which shows all staff and list home phones and cell
phones. Now I had this all figured out but the bosses have decided now they
want to throw something else into. When we enter employee data into our
database there is an option to check to confirm whether or not they want
their number listed on the staff roster. These rosters are put out in our
houses, Admin have a full roster that disregards those boxes and shows all
numbers. Anyway to get down to the new one that is needed, I need one that
will show at least one number per person but only for certain people, (which
is two). I have a roster with conditional formatting that will show all home
number regardless. It will then look at the cell phone number and only show
it if there isn't a home number available. This gets me at least one number
for everyone. They now want it to do that, but for the ED, not show their
number, but for out QAM show both numbers (even though they don't want any of
theirs shown on the regular roster)

Here is what I tried. On the field "phonenumber", I put conditional
formatting to set text to white if [Name]="Doe, John" (this is just an
example name). Shouldn't this look for that name and white out the phone
number?? On cell phone I put one to show or put text to black if
[Name]="Smith, Jane". Again I thought this would show that number regardless
of if they marked show or not show. Both don't work so any suggestions would
be great.
 
Not sure about all your examples.

Make sure your report's data source (the query) includes the checkbox field
you mentioned.

For the phone number, in conditional formatting, change the first drop-down
to "expression is" then enter something like...

[SomeCheckBoxFieldName] = true

and set your condition

And then in the second condition....

[SomeCheckBoxFieldName] = true

and set your other condition.


If anyone exports this to word or excel, I'm pretty sure thye could
highlight the field and change the color.





A better way would be to remove the two phone fields from your report.
Instead, put an unbound text field.

In the field put....

=IIf([CheckboxField]=true,[HomePhone,"")

This will show the home phone if the checkbox is true, and show nothing if
the checkbox is not true.

--
Rick B



Shanin said:
I have a question to see if this will work with conditional formatting since
as to date, I can't get it to work.

I have a staff roster which shows all staff and list home phones and cell
phones. Now I had this all figured out but the bosses have decided now they
want to throw something else into. When we enter employee data into our
database there is an option to check to confirm whether or not they want
their number listed on the staff roster. These rosters are put out in our
houses, Admin have a full roster that disregards those boxes and shows all
numbers. Anyway to get down to the new one that is needed, I need one that
will show at least one number per person but only for certain people, (which
is two). I have a roster with conditional formatting that will show all home
number regardless. It will then look at the cell phone number and only show
it if there isn't a home number available. This gets me at least one number
for everyone. They now want it to do that, but for the ED, not show their
Click to expand...
number, but for out QAM show both numbers (even though they don't want any of
theirs shown on the regular roster)

Here is what I tried. On the field "phonenumber", I put conditional
formatting to set text to white if [Name]="Doe, John" (this is just an
example name). Shouldn't this look for that name and white out the phone
number?? On cell phone I put one to show or put text to black if
[Name]="Smith, Jane". Again I thought this would show that number regardless
of if they marked show or not show. Both don't work so any suggestions would
be great.
Click to expand...
 
My explanation wasn't the clearest in the world, sorry about that. My
original roster, that we distribute everywhere is based off a query with the
checkboxes. It works fine.
I have used the conditional formatting if the check box is not marked (which
means do not show), it will change the text color to white to match the
background so they don't show up.

The one I'm having trouble on is one they want that will show at least one
phone number for everyone regardless of the marks but for one job title, show
all, and for another job title show none. Since there is only one person per
these job titles this is how I set it up, since conditional formatting should
take the first true statement and stop.

This is the conditional formatting I used on the PhoneNumber field
1. Expression is [Name]="Smith, Jane" set formatting to black text
2. Expression is [Name]="Doe, John" set formatting to white text

On the other/cell number field this is the formatting I used
1. Expression is [Name]="Smith, Jane" set formatting to black text
2. Expression is [PhoneNumber] Is Null set formatting to black text
3. Expression is [ShowCell/OtherNumber]=No set formatting to white text

All of the expressions work except for the ones with the name field. Both
those people in those two job titles don't want their numbers listed on our
normal roster, but one has to have theirs on the supervisor roster regardless
of what they marked, and one does not. Shouldn't it be able to look for that
name in the Name field??


Rick B said:
Not sure about all your examples.

Make sure your report's data source (the query) includes the checkbox field
you mentioned.

For the phone number, in conditional formatting, change the first drop-down
to "expression is" then enter something like...

[SomeCheckBoxFieldName] = true

and set your condition

And then in the second condition....

[SomeCheckBoxFieldName] = true

and set your other condition.


If anyone exports this to word or excel, I'm pretty sure thye could
highlight the field and change the color.





A better way would be to remove the two phone fields from your report.
Instead, put an unbound text field.

In the field put....

=IIf([CheckboxField]=true,[HomePhone,"")

This will show the home phone if the checkbox is true, and show nothing if
the checkbox is not true.

--
Rick B



Shanin said:
I have a question to see if this will work with conditional formatting since
as to date, I can't get it to work.

I have a staff roster which shows all staff and list home phones and cell
phones. Now I had this all figured out but the bosses have decided now they
want to throw something else into. When we enter employee data into our
database there is an option to check to confirm whether or not they want
their number listed on the staff roster. These rosters are put out in our
houses, Admin have a full roster that disregards those boxes and shows all
numbers. Anyway to get down to the new one that is needed, I need one that
will show at least one number per person but only for certain people, (which
is two). I have a roster with conditional formatting that will show all home
number regardless. It will then look at the cell phone number and only show
it if there isn't a home number available. This gets me at least one number
for everyone. They now want it to do that, but for the ED, not show their
Click to expand...
number, but for out QAM show both numbers (even though they don't want any of
theirs shown on the regular roster)

Here is what I tried. On the field "phonenumber", I put conditional
formatting to set text to white if [Name]="Doe, John" (this is just an
example name). Shouldn't this look for that name and white out the phone
number?? On cell phone I put one to show or put text to black if
[Name]="Smith, Jane". Again I thought this would show that number regardless
of if they marked show or not show. Both don't work so any suggestions would
be great.
Click to expand...
Click to expand...
 
Back
Top