Convert Excel to Access MIN() function help

[ericwfrost]

I think that link is broken, unless you intended the NPV function?

http://support.microsoft.com/kb/268159

How about this? I realize this is really basic but it serves my purposes...

'Simple "Excel" min and max functions
'these only work with integers and only compares two numbers
[EMAIL="'eric.frost@mp2kmag.com"]'eric.frost@mp2kmag.com[/EMAIL]
Public Function EMin(nVal1 As Integer, nVal2 As Integer) As Integer
  If nVal1 <= nVal2 Then EMin = nVal1
    Else: EMin = nVal2
  End If
End Function
Public Function EMax(nVal1 As Integer, nVal2 As Integer) As Integer
  If nVal1 >= nVal2 Then EMin = nVal1
    Else: EMin = nVal2
  End If
End Function

 

[pbaldy]

Works for me. Should go to KB 209857, which is "How to Find Minimum or Maximum Value Across Fields of Record", which could be used for your needs as well. Yours looks like it will work; the one in the link is more flexible.

 

[ericwfrost]

Yes, the link works now.. by the way, the code I posted above had some problems. Also, here's the full translation of the Excel function I posted earlier now converted to an Access VBA function.

Public Function myhtml(nIndex As Integer) As String
  myhtml = "<font color=white>" & REPT("n", EMin(nIndex, 10)) & _
   "</font><font color=red>" & REPT("n", EMax(10 - nIndex, 0)) & _
   "</font><font color=black>n</font><font color=" & _
   Chr$(34) & "#0097d8" & Chr$(34) & ">" & REPT("n", EMax(nIndex - 9, 0)) & _
   "</font><font color=white>" & REPT("n", IIf(EMin(19 - nIndex, 10) < 0, 0, EMin(19 - nIndex, 10))) & "</font>"
End Function
'The parameters of the REPT function from Excel are reversed compared to the Access String function
'eric.frost@mp2kmag.com
Public Function REPT(szChar As String, nReps As Integer) As String
   REPT = String(nReps, szChar)
End Function
'Simple "Excel" min and max functions, these only work with integers and only compares two numbers
'eric.frost@mp2kmag.com
Public Function EMin(nVal1 As Integer, nVal2 As Integer) As Integer
  If nVal1 <= nVal2 Then
    EMin = nVal1
  Else
    EMin = nVal2
  End If
End Function
Public Function EMax(nVal1 As Integer, nVal2 As Integer) As Integer
  If nVal1 >= nVal2 Then
    EMax = nVal1
  Else
    EMax = nVal2
  End If
End Function

Eric

 

[DavePaulChicago]

For simple Excel MIN function statement conversions, the easiest conversion to Access is to use Build function in new/blank column field to create field of first value, then Build #2 field for field of second value, then final Build #3 field compares the two to see which is minimum. As you probably already know, if you are referencing Build values, as in the final Build #3, you have to save and close out of the query and then open again. Then when you Build #3 you will see the Build 1 & 2 fields. This is the code for my final Build #3: SYS MIN SOLE /2: IIf([SYS ABS Sole]<[SYS ABS Joint],[SYS ABS Sole]/2,[SYS ABS Joint]/2). In my case, I had to compare ABS values and divide by two.

Hope this helps!

 

[CJ_London]

@Dave - post is 8 years old but welcome to the forum

 

[pbaldy]

9 year old post Pat.

 

[Mark_]

Let's do the time warp again

Who reads dates? If the question pops to the top, it is current. That is the good and the bad of this interface. I'm going to guess that the OP abandoned his conversion effort.

I do read the dates, and I'm sure the ANTICIPATION was exquisite!

 

[isladogs]

I'm going to guess that the OP abandoned his conversion effort.

We could wait another nine years for them to let us know....
However as both larrynew and ericwfrost haven't been on the site since 2009, I doubt that's going to happen.

Mind you Bob Larson did reappear recently after a six year gap, so who knows...