Highlight record with the highest value 1

[qwerty70]

Dear All,

My inquiry is relevant to thread702-1381356.

In my report, is there any way to highlight a record that contains the highest revision number? As you can see per my sample data below, the revision number field have 2 different value formats; alpha-numeric and number. Therefore, the highest revision should be "3E". Please help.

txtDrwgNum txtRevNum
20-SD-2001 1A
20-SD-2001 2F
20-SD-2001 3
20-SD-2001 3E

Thanks in advance,

qwerty70

 

[missinglinq]

What code/method (from the previous posting) did you decide on to find the highest value?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SeeThru]

You will need to convert the field into a sortable numberic value.

I would suggest using the number, and adding hte letter part as a decimal:

ie A=.01
B= .02

so 3E would be 3.05

You can then sort and group on these

To high lisght the highest, consider using a DMAX to find the highest value, then step thuogh every record, and if the value is equal to the DMAX result, change the colour or whatever.

 

[qwerty70]

missingling,

Below is the code provided to me by PHV. This code works fine for my query. It only shows the record with the latest revision.

SELECT A.txtDrwgNum, Max(A.txtRevNum) AS MaxOftxtRevNum
FROM tblDrawings AS A INNER JOIN (
SELECT txtDrwgNum, Max(Val(txtRevNum)) AS MaxOfVal
FROM tblDrawings GROUP BY txtDrwgNum
) AS B ON A.txtDrwgNum = B.txtDrwgNum AND Val(A.txtRevNum) = B.MaxOfVal
GROUP BY A.txtDrwgNum

Now, I have a different criteria for my report. Example, I want to see drawing number "20-SD-2001" with its all revisions but the latest revision must be highlighted.

SeeThru, sorry I'm not allowed to change the revision format.

Please help.

qwerty70

 

[SeeThru]

Hi Qwerty - don't know if my suggestion in general was helpful, but it would not require changing the revision format - what I was suggesting was writing a function that converted the format "3E" to "3.05".

This could be done on the fly in the background, and would not have to be visible to users.

Public Function fnConvertRelease(inputCode) as variant
dim strReleaseVersion as string
dim lngReleaseVersion as long 
strReleaseVersion=mid(inputcode,len(inputcode))
select case strReleaseVersion
case "A"
 lngreleaseversion=0.01
case "B"
 lngreleaseversion=0.02
...
...
end case
fnConvertRelease=left(inputcode,len(inputcode)-1))+lngreleaseversion

There may be a better way of convering letters to numbers, though. This code should be fast enough to run on the fly for any reports or queries - just call it and sort or gruop on it, but remember to display the version with the letters.

 

[maxhugen]

G'day qwerty

I'd create and save a separate aggregate query for the Max(txtRevNum):

SELECT txtDrwgNum, Max(txtRevNum) AS MaxRevNum
FROM tblDrawings 
GROUP BY txtDrwgNum

Then link this query to the txtDrwgNum of your report's query. Now every row returned will include MaxRevNum.

In the report's OnFormat event, do something like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    if txtRevNum = MaxRevNum then
        txtRevNumber.BackColor = 255
        ...
    end if
End Sub

HTH


Max Hugen

http://www.hugen.com.au

Australia

 

[TheAceMan1]

How are yaqwerty70 . . .

I started code for this but couldn't get back to it until now. So try this:
[ol][li]In a module in the modules window copy/paste the following function:

[blue]Public Function CipherRev(Rev As String) As Long
 
   Dim Cipher As Long, Xfactor As Long, idx As Long
 
   Do
      Xfactor = Choose(idx + 1, 1, 100, 10000)
      Cipher = Cipher + (Asc(Mid(Rev, Len(Rev) - idx, 1)) And 223) * Xfactor
      idx = idx + 1
   Loop Until idx > Len(Rev) - 1
 
   If Len(Rev) = 1 Then
      CipherRev = Cipher * 100
   Else
      CipherRev = Cipher
   End If[/blue]

[/li]
[li]In query design view goto SQL view and copy/paste the following:

[blue]SELECT tblDrawings.txtDrwgNum, tblDrawings.txtRevNum, CipherRev([txtRevNum]) AS Cipher
FROM tblDrawings;[/blue]

Save & name the query [blue]qryMaxRev[/blue].[/li]
[li]Finally in the [blue]On Print[/blue] event of the [blue]Detail Section[/blue] of the report, copy/paste the following:

[blue]   Dim Criteria As String
   
   Criteria = "[txtdrwgNum]='" & Me!txtDrwgNum & "'"
   
   If CipherRev(Me!txtRevNum) = DMax("Cipher", "qryMaxRev", Criteria) Then
      Me!txtRevNum.BackColor = 8454143 [green]'Yellow[/green]
   Else
      Me!txtRevNum.BackColor = 16777215 [green]'White[/green]
   End If[/blue]

[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[qwerty70]

TheAceman1, the for the reply. I did follow your instruction thoroughly, however, none of the revision numbers in my report are highlighted. As per my sample data below, revision # 7 should be highlighted, but it is not.

Drawing Number Revision Number Cipher
000-10P-0086 4 2000
000-10P-0086 5 2100
000-10P-0086 7 2300

I have another issue here, when I reviewed my data in the qryMaxRev, I found out that revision "OH" got the highest cipher. It should be revision "1".

Drawing Number Revision Number Cipher
000-50P-0001 1 1700
000-50P-0001 OG 7971
000-50P-0001 OH 7972

Your help is highly appreciated.

Thanks & regards,

qwerty70

 

[qwerty70]

maxhugen, thanks for the insight...but your code just don't work with my data. It selects "OH" as the highest revision, it should be "1".

Drawing Number Revision Number
000-50P-0001 1
000-50P-0001 OG
000-50P-0001 OH

regards,

qwerty70

 

[TheAceMan1]

qwerty70 . . .

I fully test this with no problemo . . . Post the code as you have it in the [blue]On Print[/blue] event of the reports detail section.

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[qwerty70]

TheAceman1, here's my code in the On Print event of the reports detail section.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   Dim Criteria As String
   
   Criteria = "[DrwgNum]='" & Me!DrwgNum & "'"
   
   If CipherRev(Me.RevNum) = DMax("Cipher", "qryMaxRev", Criteria) Then
      Me!RevNum.BackColor = 8454143 'Yellow
   Else
      Me!RevNum.BackColor = 16777215 'White
   End If
   
End Sub

BTW, I am using Access 2002.

Please help.

Thanks,

qwerty70

 

[TheAceMan1]

qwerty70 . . .

I gave the wrong code for the function [blue]CipherRev[/blue] (there's a flaw). Change to the following:

[blue]Public Function CipherRev(Rev As String) As Long
   Dim valLtr As Integer
   
   valLtr = (Asc(Right(Rev, 1)) And 223)
   
   If valLtr > 64 And valLtr < 91 Then
      CipherRev = Val(Left(Rev, Len(Rev) - 1)) * 100 + valLtr
   Else
      CipherRev = Val(Rev) * 100
   End If

End Function[/blue]

Perform your testing. If testing still fails [purple]move[/purple] the code from the [blue]On Print[/blue] event to the [blue]On Format[/blue] event.

BTW: what version access?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[qwerty70]

TheAceman1, thanks for the code. I've checked my data in the qryMaxRev and the cipher code was perfect. It recognize the number as the highest revision.

Drawing Number Revision Number Cipher
000-50P-0001 1 100
000-50P-0001 OG 71
000-50P-0001 OH 72

Concerning my report, I've moved the code from On Print to On Format but still the same old thing. I am using Access 2000...not 2003.

Thanks for the unwavering support TheAceman1. I wish you can help me sort this out.

Regards,

qwerty70

 

[TheAceMan1]

qwerty70 . . .

Check the names of the controls. Is it:

txtRevNum or RevNum
txtDrwgNum or DrwgNum

Be sure to check the [blue]Name[/blue] property of the controls to be sure! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[qwerty70]

TheAceman1, I just changed the BackColor to ForeColor and it works fine. I have no idea why BackColor doesn't work in my report. But anyway, this serves my purpose. A million thanks to you for your great help.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   Dim Criteria As String
   
   Criteria = "[DrwgNum]='" & Me!DrwgNum & "'"
   
   If CipherRev(Me!RevNum) = DMax("Cipher", "qryMaxRev", Criteria) Then
      Me!RevNum.ForeColor = 255 'Red
   Else
      Me!RevNum.ForeColor = 16711680 'Blue
   End If
End Sub

BTW, I had a close look at my qryMaxRev just now and found one more flaw. As per data below, revision number "0" should have the higher cipher...not "A". Please help.

Drawing Number Revision Number Cipher
011-20P-0036 0 0
011-20P-0036 A 65

Thanks,

qwerty70

 

[TheAceMan1]

qwerty70 . . .

I didn't think revision 0 would be used (real world usually starts at one). In any case:

[blue]Public Function CipherRev(Rev As String) As Long
   Dim valLtr As Integer
   
   valLtr = (Asc(Right(Rev, 1)) And 223)
   
   If valLtr > 64 And valLtr < 91 Then
      If Len(Rev) = 1 Then
         CipherRev = valLtr
      Else
         CipherRev = Val(Left(Rev, Len(Rev) - 1)) * 100 + 100 + valLtr
      End If
   Else
      CipherRev = Val(Rev) * 100 + 100
   End If

End Function[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[qwerty70]

TheAceman1...no more flaws! Your code works perfect.

Yes, I understand in real world revision should always start at one. In fact, I'm planning to impose that in our company but sad to say I don't have the badge to do it.

Anyways, thanks for your time and support. I really appreciate it.

Regards,

qwerty70

 

[maxhugen]

I note that your main issue is that you need alphas to sort higher than numerics.

Be aware that using a custom function in a query may have a severe performance impact. With a small recordset you may not really notice, but it could get ugly with many records.

Another performance hit is calling the domain aggregate function DMax(), as this is sending a call to the db for every single record.

If you do strike performance probs, you can avoid both above performance hits by using a query:

Query qryMaxRev

SELECT txtDrwgNum, Max(Asc(Left([txtRevNum],1))+IIf(Asc(Left([txtRevNum],1)) Between 48 And 57,2000,1000) & Right([txtRevNum],Len([txtRevNum])-1)) AS MaxRevNum
FROM tblDrawings
GROUP BY txtDrwgNum;

Then link this query to the txtDrwgNum of your report's query.

In the event, use:

Dim MaxRev$

    MaxRev = CLng(Left(MaxRevNum,4) - _
             iif(Left(MaxRevNum,1)=1,1000,2000) & _
             iif(Len(MaxRevNum)=5,Right(MaxRevNum,1),"")
    RevNum.ForeColor = iif(RevNum=MaxRev,255,16711680)

Technically, this should be called in the OnFormat event, but in this particular case, it won't matter that it's called from OnPrint. However, had you changed the font size, for example, you would strike problems.

HTH

Max Hugen

http://www.hugen.com.au

Australia

 

[TheAceMan1]

[blue]I note that your main issue is that you need alphas to sort higher than numerics.[/blue]

Hmmmmm . . .

[blue]As per data below, revision number "0" should have the higher cipher...not "A". Please help.

[tt]DrawingNumber RevisionNumber Cipher
011-20P-0036 0 0
011-20P-0036 A 65[/tt][/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[maxhugen]

maxhugen, thanks for the insight...but your code just don't work with my data. It selects "OH" as the highest revision, it should be "1".

Perhaps clarification of the required sort is needed... is it (in ascending order):

0 (zero)
a-z
1-9

If so, it only needs minor mods to the query and code, and still avoids both the custom function and DMax overheads, no?





Max Hugen

http://www.hugen.com.au

Australia