Conditional Formatting in Access

[Triland]

I am working on an Access report and want to have the system add conditional formatting (shading) to the details area if the value of the field "Revisions" is the Max value for all revisions in the report.

[June7]

Have a field in report that has this Max value. Refer to that textbox in the ConditionalFormatting expression for the Revisions textbox.

[Triland]

The only problem with that is for each instance in the report the latest 'Revision' number will be different. Any idea of how I might work a round that?

[June7]

Don't understand. If the Revision number does not equal the 'Max' value then highlight it or only highlight the number that is equal to the 'Max'.

What do you mean by 'instance' - a document item? What is this report showing? If it has only the 'lastest' revision for each item then why the need to highlight?

[Triland]

Revision

Cable No.

Quantity

Text130

Text131

Text132

Field96

Field101

Field97

Field102

Comments

Text110

Circuit Voltage

Field95

Field98

Field99

Text119

Text120

Text121

Text122

Text123

Text124

Text125

0

C0050

1

600

5/C

14

1GES-470

1SCS-0970

125.00

C05014

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

0

E0050-1

1

600

1/C

4/0

1GES-470

0GES-471A

480.00

E014/0

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3031

K3055

K3075

0

E0050-2

1

600

1/C

4/0

1GES-470

0GES-471A

480.00

E014/0

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3032

K3068

K3076

0

E0050-3

1

600

1/C

4/0

1GES-470

0GES-471A

480.00

E014/0

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3033

K3071

K3077

0

E0050-4

1

600

1/C

4/0

1GES-470

0GES-471A

480.00

E014/0

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3034

K3072

K3078

0

H0050

1

600

4PR-SH

16

1GES-470

1SCS-0970

24.00

G04P16SR

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

0

P0050-1

3

600

1/C

500

1GES-470

0GES-471A

480.00

B01500

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3031

K3055

K3075

0

P0050-2

3

600

1/C

500

1GES-470

0GES-471A

480.00

B01500

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3032

K3068

K3076

0

P0050-3

3

600

1/C

500

1GES-470

0GES-471A

480.00

B01500

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3033

K3071

K3077

0

P0050-4

3

600

1/C

500

1GES-470

0GES-471A

480.00

B01500

480V LOAD CENTER SWGR

480V LOAD CENTER SWGR

T4102

T4107

T4108

K3034

K3072

K3078

0 (this is the revision number

C0136

1

600

12/C

14

1SCS-0970

0EEG-610CP

120.00

C12014

SUPERVISORY CNTL SYS (SCS)

SUPERVISORY CNTL SYS (SCS)

T1103

T1104

K1041

K1034

K1037

K1048

CONDUIT

1

C0136A

1

600

12/C

14

1SCS-0970

0EEG-610CP

120.00

C12014

SUPERVISORY CNTL SYS (SCS)

SUPERVISORY CNTL SYS (SCS)

T1103

T1104

K1041

K1034

K1037

K1048

CONDUIT

So what happens is the report runs and will show all of the different Revisions. My boss wants to see just the Greatest Revision number (highes value) highlighted in the report. Hopefully that's a little clearer. And, thanks for working with me on this.

[June7]

The highest revision number I see is 1. That is the value that should be highlighted? What if more than one record has revision 1?

What is being 'versioned' - the CableNo?

[Triland]

Any Ideas???

[June7]

My idea is to calculate the Max value displayed on the report then use it in ConditionalFormatting expression. Nothing you have shown gives me any different understanding to recommend otherwise.

You did not answer questions in last post.

[Triland]

OK. HMMM. I have a report that lists all of the revisions for a project. So for example: revision 1, revision 2, revision 3 and so on. In this instance on only want revision 3 to be shaded because it is the latest revision and has the greatest value.

[Triland]

I posted an example in the response at 1:54.

[June7]

Don't know where that post went but I found another thread on this issue posted at 12:45 and deleted it.

So you need to calculate the Max revision for each project? Possible options:

1. A DLookup in query to construct a field that will then be available as any other field in the report. The value will show for every project record.

2. An aggregate (GROUP BY) query that returns the Max revision for each project then join that query to table/or query with the detail records. Again, the Max value will show on every record because of the join and field is available in the report.

3. Set up a Group section in report on the ProjectID. Textbox in that Group section with expression: Max(Revision)

[Triland]

Thank you so much. I'm going to try this and reply and soon as I get it fixed :-)!!!!

[Triland]

I have added the group and the field. Can you tell what the coding would look like to get it to return that MAX revision number in that report?

[Triland]

Can someone please help. I have a simple report that pulls in Revision numbers. I simply want the conditional formatting in Access to only highligt the greatest (or highest) revision number. So the report would show Rev = 1, Rev = 2 and so on. In that instance only revision 2 should be highlighted b/c it has the highest value. I would like the report to continue to use that logic moving forward and higher and higher revision numbers are added. Any ideas? I tried and few things and nothing seems to work.

[Triland]

Hi, I added the textbox and the coding: MAX(revisions). It is accepted ok but does not pull anything into the field. Any idea of what I might be doing wrong.