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

 

[Pat Hartman]

Yes, I am retaining the layout of my Excel file...for now, just to keep everything simple during the transition.

I'm glad you are stopping the conversion and stepping back to think about the data. You will be veeeeeeeeeeerrrrrrry unhappy with Access is you simply port your flat file. As you have discovered with this simple Min() function, Access doesn't work at all like Excel even though a table in DS view might look like a spreadsheet because it appears to have rows and columns. The problem is that excel sheets frequently have what are called repeating groups - columns with similar data. 12 columns, one for each month. 7 columns, one for each day, etc. This works fine in Excel because formulas work "across" as well as "down" but in a relational database, repeating groups violate first normal form and so you will never find any built in functions that operate on "columns" because each column should be different and therefore it would make no sense to have built in functions. The repeating groups actually become multiple rows in a second table and they are related to the parent record. Column names that include numbers, prefixes, or suffixes are easily identified as repeating groups but others are more subtle. Electric, Gas, Lawn, Snow, Trash are all expense types. Each belongs in a separate row. That way, if you add an elevator, and now have an Elevator maintenance bill, you just add a row to a table and you will not have to modify any formula or report. THAT is the power of a relational database.

You, of course, can write your own functions. But it will be quite tedious and they will not scale. You will have to modify them any time you need to add a column just as you do in the spreadsheet. But with a properly normalized schema, functions such as Min() and many others are trivial.

The conversion is a one time event. Some of it will be automatic. Some might require coding.

 

[pbaldy]

9 year old post Pat.

 

[Pat Hartman]

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.

 

[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...