CODEWITHSUNDEEP
ms-accessvbams-access-2010
AxxieD
AxxieD

Reputation: 614

DMin Won't Read First Column

I have a Sub in Access 2010 that's supposed to pull the lowest value between three columns. The problem is that it doesn't seem to be reading the first column. I've checked the spelling a thousand times, and I have no idea what else could possibly wrong. Any suggestions would be beyond appreciated.

Private Sub UpdatePriority_Click()

Const Superior As Long = 9999

Dim MinGeoPri As Variant
Dim MinStrPri As Variant
Dim MinSOPri As Variant

MinGeoPri = Nz(DMin("[GeoPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo"), Superior)
MinStrPri = Nz(DMin("[StrPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo"), Superior)
MinSOPri = Nz(DMin("[SOPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo"), Superior)

Overall_Priority.Requery

Overall_Priority = IIf(((IIf([MinStrPri] < [MinGeoPri], [MinStrPri], [MinGeoPri]))) < [MinSOPri], ((IIf([MinStrPri] < [MinGeoPri], [MinStrPri], [MinGeoPri]))), [MinSOPri])

End Sub

Upvotes: 1

Views: 50

Answers (1)

Andre
Andre

Reputation: 27644

There are several issues with your code.

1) I don't know where Activity.ProjNo comes from, but the DMin calls should probably read:

DMin("[GeoPri]", "[Projects]", "Projects.ProjNo = " & Activity.ProjNo)

with the parameter outside of the WHERE string.

2) What is Overall_Priority.Requery supposed to do, if you set the same (field? variable?) right afterwards?

3) Your IIf statement is a nightmare, I'm pretty sure there is something wrong with the parentheses. :)
Much better: use a VBA Min() function as found here: http://www.mrexcel.com/forum/excel-questions/132404-max-min-visual-basic-applications.html

Public Function Min(ParamArray values() As Variant) As Variant

   Dim minValue As Variant, Value As Variant
   minValue = values(0)
   For Each Value In values
       If Value < minValue Then minValue = Value
   Next
   Min = minValue

End Function

ParamArray is great for this sort of thing (unknown number of parameters).

And then:

Overall_Priority = Min(MinGeoPri, MinStrPri, MinSOPri)

Edit: test function

Public Sub MyMinTest()

    Debug.Print Min(47, 11, 88)

End Sub

Upvotes: 1

Related Questions