MIN and MAX

[trek]

Hei!

First - sorry for bad English

I try to make my second database in Access. I need to summery 5 numbers, and then minus the lowest and the highest of them. For example (1+2+3+4+5)-1-5=9


In excel it looks like this: =SUM(C2:G2)-MAX(C2:G2)-MIN(C2:G2)

In access (Expression builder) i made this code: [J1]+[J2]+[J3]+[J4]+[J5] - it's the summary of five marks, but how can i erase the highest and the lowest one?

Best,
Kert

[June7]

Write a custom function in VBA.

[trek]

Write a custom function in VBA.

Hei!

Thanks, but can you help me with it? It sounds easy, but it's quite difficult for me.

I uploaded my file: http://www.upload.ee/files/2111458/T...ion.accdb.html

Thanks,
Kert

[June7]

Could be something like:

Function SumData(D1 As Double, D2 As Double, D3 As Double, D4 As Double, D5 As Double)
Dim DMax As Double
Dim DMin As Double
DMax = D1
If D2 > DMax Then DMax = D2
If D3 > DMax Then DMax = D3
If D4 > DMax Then DMax = D4
If D5 > DMax Then DMax = D5
DMin = D1
If D2 < DMin Then DMin = D2
If D3 < DMin Then DMin = D3
If D4 < DMin Then DMin = D4
If D5 < DMin Then DMin = D5
SumData = IIf(D1 <> DMax And D1 <> DMin, D1, 0) _
    + IIf(D2 <> DMax And D2 <> DMin, D2, 0) _
    + IIf(D3 <> DMax And D3 <> DMin, D3, 0) _
    + IIf(D4 <> DMax And D4 <> DMin, D4, 0) _
    + IIf(D5 <> DMax And D5 <> DMin, D5, 0) _
End Function

Call the function from query, textbox, or code like: SumData([J1], [J2], [J3], [J4], [J5])