Finding Min between two different fields

[Spennyd]

I did a couple searches on this before posting, but couldn't find anything that helps. Here is what I have (sorry it's not in table format):

Purchase Price: 50,000
Max Loan on Purchase Price: 40,000 (80% of purchase price)
Value: 70,000
Max Loan on Value: 42,000 (60% of Value)

I need to create a field that will look at the two "Max Loan" fields and return the lower of the two.

Any help?

Much appreciated,

Spennyd

 

[pbaldy]

Try

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

 

[Spennyd]

Try

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

Thanks Pbaldy, but that didn't seem to work. I'm using Access 2003 and it did not recognize the 'Minimum' function. And when I try to use Min is says I have the wrong number of arguments.

 

[georgedwilkinson]

Spennyd,

You really didn't give enough information to answer your question. The article Paul pointed you to would help in certain situations.

First, what I "think" might be the answer to your question is the use of the Iif() function and the greater than (>) or less than (<) operators. You might have to throw in one or two Nz() functions to make it work right.

Second, as a note on design, you typically don't store calculated values in tables. Though there is no direct evidence you are doing that, the description of your structure is suspicious.

Third, also a note on design, you should name your fields without spaces or special characters. Again, there is no direct evidence you are doing that but I've always found it to be very helpful to have single-word, descriptive object names.

 

[pbaldy]

I'm using Access 2003 and it did not recognize the 'Minimum' function.

Did you miss this?

Create a new module and enter the following two functions

 

[Spennyd]

Thanks George for the advice. I must admit that I was originally trying to create calculated fields in a table, but deviated from that earlier in the design. These calculated fields are all being generated in a Query. (I hope that's not essentially the same thing). Also, I am not using spaces in my field names. Let me try to be a bit more specific.

tblProperty has:
PurchPrice - 50,000 - Purchase Price
EstValue - 70,000 - Estimated Value
MaxLTP - 80% - Maximum Loan to Purchase Price
MaxLTV - 60% - Maximum Loan to Value

QryProperty has:
MaxLoanOnPurch: [PurchPrice]*[MaxLTP] ---- (I know that's a terrible Field name, but it does the job). This calculates correctly as 40,000.
MaxLoanOnValue: [EstValue]*[MaxLTP]. This calculates correctly as 42,000.

Now I am trying to create a new field that will return the lower of the two values, MaxLoanOnPurch and MaxLoanOnValue, or 40,000.

I hope that clarifies a bit.

Thanks for the help.

 

[Spennyd]

My apologies, Pbaldy. That worked perfectly. Thank you!

 

[Spennyd]

One more follow-up. The returned value does not appear to be recognized as a number which can be formatted in the Currency form. Is there a line of code that can be added to the end of the module, or elsewhere, that will allow me to display this number in currency format?

 

[pbaldy]

I would have thought it would be format-able, but you could try this, which I haven't tested:

Function Minimum(ParamArray FieldArray() As Variant) As Currency

 

[Spennyd]

Worked like a charm. Thanks!

 

[pbaldy]

No problemo, and welcome to the site by the way!