max value 1

[timhans]

Hello, I am making a parts order form and I need to get the max value from three fields

MSRP MarkUp HcpcPricing

Naively thought there would be a built in function. Thanks

 

[ProgramError]

Are you asking for help to get
1) the max value for each of these fields?
2) the max value of one of these fields?
3) the max value of the highest field?



Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[TheAceMan1]

How are ya timhans . . .

An example of what your after ... including some assemblence of [blue]table structure[/blue], would be a boon to resolution!

Can you accommodate?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[timhans]

ProgramError and Aceman , sorry about the lack of clarity I always think I am being clear when I post, then find out I'm not!. I'm looking for

3) the max value of the highest field?
MSRP MarkUp HcpcPricing
Like
Max(MSRP, MarkUp, HcpcPricing) = ?

Aceman not sure what to pass on about table structure, I have tried to normalize (not sure I have succeeded, my first DB)
Key field is autonumber other field are
Quantity, MSRP, Cost, MarkUp, BestPrice, SubTotal, EstimatedLabor, TotalLabor, WorkOrderDetail_WorkOrder_ID, WorkOrderDetail_DemoChairs_ID

MarkUp = Cost * Constant
HcPcPricing are values in a separate table down loaded from a government web site.

MSRP and cost gotten from a manufacturers quote, if this is not what you were looking for please let me know. Thanks for responding

 

[MajP]

There are probably a lot of versions. In access there are a lot of instance of a blank field (null value). This handles those cases.

Public Function getMax(ParamArray vals() As Variant) As Variant
  Dim myVal As Variant
  Dim maxVal As Variant
  
  For Each myVal In vals
    If Not IsNull(myVal) Then
      maxVal = myVal
      Exit For
    End If
 Next myVal
 
 If IsNull(maxVal) Then Exit Function
 
 For Each myVal In vals
    If Not IsNull(myVal) Then
       If myVal > maxVal Then
          maxVal = myVal
       End If
    End If
 Next myVal
 getMax = maxVal
End Function

 

[timhans]

MajP, Thank you that worked perfectly.

 

[PHV]

I've already posted this:

'A generic function to get the max value of an arbirtrary numbers of same type values:
Public Function myMax(ParamArray Args())
Dim i As Long, rv
For i = 0 To UBound(Args)
  If IsNull(rv) Or rv < Args(i) Then rv = Args(i)
Next
myMax = rv
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

That's nice.