hiding values below/above a threshold

[Jeannie M]

What I have is:

FldA FldB FldC FldD FldE FldF
cust1 1 1 1 1 1 6
cust2 1 2 1 1 1 1
cust3 1 1 1 1 1 1
cust4 1 1 1 4 1 1
cust5 1 1 1 1 1 1
cust6 1 7 1 1 1 3

What I need is more of an exception report so the user does not have to look
to hard to extract the numbers that are important - any value larger than 1.
Is there a way to not display a value below (or above) a specified threshold
on a report or form?

what I need to produce is this:

FldA FldB FldC FldD FldE FldF
cust1 6
cust2 2
cust3
cust4 4
cust5
cust6 7 3

this is much easier to pick out the critical values

HELP

 

[Steve]

Base your report on a query. Use this expression for FldA:
RptFldA:IIF([FldA]>1,[FldA],Null)
Do the same for B to F.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Jeannie M]

Guess I forgot a real important part of the problem. I need the ability to
change the value in any field. Using the expression you noted makes the
field not updateable. Same thing happens when I use IIF(FldA>1, FldA, "") as
the Control Source. Sorry I forgot this. this may be the show stopper.
--
Jeannie

Base your report on a query. Use this expression for FldA:
RptFldA:IIF([FldA]>1,[FldA],Null)
Do the same for B to F.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

What I have is:

FldA FldB FldC FldD FldE FldF
cust1 1 1 1 1 1 6
cust2 1 2 1 1 1 1
cust3 1 1 1 1 1 1
cust4 1 1 1 4 1 1
cust5 1 1 1 1 1 1
cust6 1 7 1 1 1 3

What I need is more of an exception report so the user does not have to
look
to hard to extract the numbers that are important - any value larger than
1.
Is there a way to not display a value below (or above) a specified
threshold
on a report or form?

what I need to produce is this:

FldA FldB FldC FldD FldE FldF
cust1 6
cust2 2
cust3
cust4 4
cust5
cust6 7 3

this is much easier to pick out the critical values

HELP

 

[Jeannie M]

Also this is a subform on a form not a report.
--
Jeannie

Base your report on a query. Use this expression for FldA:
RptFldA:IIF([FldA]>1,[FldA],Null)
Do the same for B to F.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

What I have is:

FldA FldB FldC FldD FldE FldF
cust1 1 1 1 1 1 6
cust2 1 2 1 1 1 1
cust3 1 1 1 1 1 1
cust4 1 1 1 4 1 1
cust5 1 1 1 1 1 1
cust6 1 7 1 1 1 3

What I need is more of an exception report so the user does not have to
look
to hard to extract the numbers that are important - any value larger than
1.
Is there a way to not display a value below (or above) a specified
threshold
on a report or form?

what I need to produce is this:

FldA FldB FldC FldD FldE FldF
cust1 6
cust2 2
cust3
cust4 4
cust5
cust6 7 3

this is much easier to pick out the critical values

HELP

 

[John Spencer]

If you have Access 2000 or later, you could use conditional formatting and
--bold the font on the controls when they have a value you want to emphasize
and
--set the font color to a medium gray and not bolded when your values should
not be emphasized.

Or you could change the background color or ...
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Also this is a subform on a form not a report.
--
Jeannie

Base your report on a query. Use this expression for FldA:
RptFldA:IIF([FldA]>1,[FldA],Null)
Do the same for B to F.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

What I have is:

FldA FldB FldC FldD FldE FldF
cust1 1 1 1 1 1 6
cust2 1 2 1 1 1 1
cust3 1 1 1 1 1 1
cust4 1 1 1 4 1 1
cust5 1 1 1 1 1 1
cust6 1 7 1 1 1 3

What I need is more of an exception report so the user does not have to
look
to hard to extract the numbers that are important - any value larger
than
1.
Is there a way to not display a value below (or above) a specified
threshold
on a report or form?

what I need to produce is this:

FldA FldB FldC FldD FldE FldF
cust1 6
cust2 2
cust3
cust4 4
cust5
cust6 7 3

this is much easier to pick out the critical values

HELP

 

[Steve]

How about using the expression I suggested for your subform so the subform
shows the exceptions you want; understanding that the form is not
updateable. Then one of two options:
1. Add code to the Customer field Click event that brings up a single form
to edit that customer's record only
2. Add a button to your subform with code in the Click event that opens a
continuous form like the one you show with all the 1s that is based on a
table or updateable query

Another option would be to have a button on your subform with code in the
click event that changes the recordsource of the subform to a table or
updateable query and display like you show with all the 1s.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Guess I forgot a real important part of the problem. I need the ability
to
change the value in any field. Using the expression you noted makes the
field not updateable. Same thing happens when I use IIF(FldA>1, FldA, "")
as
the Control Source. Sorry I forgot this. this may be the show stopper.
--
Jeannie

Base your report on a query. Use this expression for FldA:
RptFldA:IIF([FldA]>1,[FldA],Null)
Do the same for B to F.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

What I have is:

FldA FldB FldC FldD FldE FldF
cust1 1 1 1 1 1 6
cust2 1 2 1 1 1 1
cust3 1 1 1 1 1 1
cust4 1 1 1 4 1 1
cust5 1 1 1 1 1 1
cust6 1 7 1 1 1 3

What I need is more of an exception report so the user does not have to
look
to hard to extract the numbers that are important - any value larger
than
1.
Is there a way to not display a value below (or above) a specified
threshold
on a report or form?

what I need to produce is this:

FldA FldB FldC FldD FldE FldF
cust1 6
cust2 2
cust3
cust4 4
cust5
cust6 7 3

this is much easier to pick out the critical values

HELP

 

[Jeannie M]

That worked great. I just made the forground color white if the value in the
field was < 2. that way the field was still updatable but 0 and 1 do not
show. Great suggestion - thanks much
--
Jeannie

If you have Access 2000 or later, you could use conditional formatting and
--bold the font on the controls when they have a value you want to emphasize
and
--set the font color to a medium gray and not bolded when your values should
not be emphasized.

Or you could change the background color or ...
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Also this is a subform on a form not a report.
--
Jeannie

Base your report on a query. Use this expression for FldA:
RptFldA:IIF([FldA]>1,[FldA],Null)
Do the same for B to F.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




What I have is:

FldA FldB FldC FldD FldE FldF
cust1 1 1 1 1 1 6
cust2 1 2 1 1 1 1
cust3 1 1 1 1 1 1
cust4 1 1 1 4 1 1
cust5 1 1 1 1 1 1
cust6 1 7 1 1 1 3

What I need is more of an exception report so the user does not have to
look
to hard to extract the numbers that are important - any value larger
than
1.
Is there a way to not display a value below (or above) a specified
threshold
on a report or form?

what I need to produce is this:

FldA FldB FldC FldD FldE FldF
cust1 6
cust2 2
cust3
cust4 4
cust5
cust6 7 3

this is much easier to pick out the critical values

HELP

 

[John Spencer]

I thought of doing that, but I would hate to use the interface if it
didn't show me what value was really in the control. I would use a
light grey to make the value visible but less noticeable. After all, I
might want to know if I was changing 1 to 3, or 0 to 3, or Null to 3, etc.

However, it is your choice (obviously) and you know how you (and your
users) need to work.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================