Format first 25% of records one colour, next 25% another, etc.

[CanadianAccessUser]

How do I format:
First 25% of records - Gold
Second 25% of records - Green
Third 25% of records - Yellow
Fourth 25% of records - Red

Please!!
:banghead:

 

[CJ_London]

how are you defining 'first 25%'? Based on what - there are 100 records and records 1-25 are the first 25%? Or perhaps it is something based on value, such as total sales are $1000 and one customer has spent $250 so they are the top 25%?

If you want an answer, provide enough information - give examples of your data in table layout and what you want to do with it - i.e. the final result

Regret I will not be able to continue with this thread as I am now away for a few days. Suggest you provide more information for someone else to follow up

 

[CanadianAccessUser]

I literally meant the first 25% of the records. If I had 100 records it would be 1-25.
I've already put the records in order according to value. Now I need to add colour.
This is my code so far:

Private Sub Report_Current()
    Dim Top As Long, MidTop As Long, MidBottom As Long, Bottom As Long
    Dim lngGold As Long, lngGreen As Long, lngYellow As Long, lngWhite As Long, lngRed As Long
    Dim KPIMiss As Field
    
    lngGold = RGB(186, 20, 25)
    lngGreen = RGB(167, 218, 78)
    lngRed = RGB(186, 20, 25)
    lngYellow = RGB(255, 242, 0)
    lngWhite = RGB(255, 255, 255)
    KPIMiss = [qryQuartile-Final].KPIMiss
    
    Top = DCount(KPIMiss, "qryQuartile-Final") / 4
    MidTop = DCount(KPIMiss, "qryQuartile-Final") / 2
    MidBottom = DCount(KPIMiss, "qryQuartile-Final") / 2 + DCount(KPIMiss, "qryQuartile-Final") / 4
    Bottom = DCount(KPIMiss, "qryQuartile-Final")
    
    If KPIMiss = Top Then
        Me!KPIMissed.BackColor = lngGold
    End If
    If KPIMiss = MidTop Then
        Me!KPIMissed.BackColor = lngGreen
    End If
    
    If KPIMiss = MidBottom Then
        Me!KPIMissed.BackColor = lngYellow
    End If
    If KPIMiss = Bottom Then
        Me!KPIMissed.ForeColor = lngWhite
        Me!KPIMissed.BackColor = lngRed
    End If
End Sub

I know I've missed something very important when it comes to selecting the required records... New to VBA and trying... please help!

 

[CJ_London]

Unfortunately, your code means nothing to me, I cannot relate to it so I repeat

If you want an answer, provide enough information - give examples of your data in table layout and what you want to do with it - i.e. the final result

 

[pbaldy]

I was curious enough to play around. This worked:

Option Compare Database
Option Explicit

Dim lngCount As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Dim lngCurrent              As Long
  lngCurrent = Me.CurrentRecord

  Select Case lngCurrent / lngCount
    Case Is < 0.25
      Me.Enter_date.BackColor = vbGreen
    Case Is < 0.5
      Me.Enter_date.BackColor = vbYellow
    Case Is < 0.75
      Me.Enter_date.BackColor = vbBlue
    Case Else
      Me.Enter_date.BackColor = vbRed
  End Select
 
End Sub

Private Sub Report_Open(Cancel As Integer)
  lngCount = DCount("*", "qryBankRec")
End Sub