CODEWITHSUNDEEP
ms-accessvbams-access-2010
AxxieD
AxxieD

Reputation: 614

DMin with Empty Column

I have three DMins that I use to find the lowest number within three columns. The only problem is when I have an empty column, it returns blank. How can I take into account empty columns?

This is the code I'm using.

Private Sub UpdatePriority_Click()

Dim MinGOPri As Variant
Dim MinSRPri As Variant
Dim MinSOPri As Variant

MinGOPri = DMin("[GOPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo")
MinSRPri = DMin("[StrPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo")
MinSOPri = DMin("[SOPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo")

Overal_Priority.Requery

Overal_Priority = IIf(((IIf([MinGOPri] < [MinSRPri], [MinGOPri], [MinSRPri])))
 < [MinSOPri], ((IIf([MinGOPri] < [MinSRPri], [MinGOPri], [MinSRPri]))), [MinSOPri])


End Sub

Upvotes: 2

Views: 152

Answers (1)

Gustav
Gustav

Reputation: 55906

As you wish to find the minimum of the three, you could insert a "larger than everything else" value when DMin is Null:

' Very large value.
Const Superior As Long = 9999

Dim MinGOPri As Variant
Dim MinSRPri As Variant
Dim MinSOPri As Variant

MinGOPri = Nz(DMin("[GOPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo"), Superior)
MinSRPri = Nz(DMin("[StrPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo"), Superior)
MinSOPri = Nz(DMin("[SOPri]", "[Projects]", "Projects.ProjNo = Activity.ProjNo"), Superior)

Upvotes: 2

Related Questions