For some reason, I can't figure this out...

I have 3 currency fields in a table with varying amounts. I have a query in
which I need to compare the amounts in these three fields, then select the
lowest value for use in a subsequent calculated field. I'm hoping and
looking for a simple function or module to accomplish this. Any suggestions?

MaxPrice: SWITCH( price1>=price2 AND price1>=price3, price1,
price2>=price3, price2, true, price3)


as computed expression, then, use MaxPrice as required (except in the WHERE
clause, or in the ORDER BY clause). If you need to use that value in the
orderby clause, or in the where clause, save that query and use another
query that will use that saved query.

I assumed your prices are NOT null.


Vanderghast, Access MVP



"Burnett71" <(E-Mail Removed)> wrote in message
news:956799B6-22FB-464B-B434-(E-Mail Removed)...
> For some reason, I can't figure this out...
>
> I have 3 currency fields in a table with varying amounts. I have a query
> in
> which I need to compare the amounts in these three fields, then select the
> lowest value for use in a subsequent calculated field. I'm hoping and
> looking for a simple function or module to accomplish this. Any
> suggestions?

ooops, you wanted the min, not the max....

MinPrice: SWITCH( price1<=price2 AND price1<=price3, price1,
price2<=price3, price2, true, price3)



Vanderghast, Access MVP



"vanderghast" <vanderghast@com> wrote in message
news:0BBB24CC-BDE4-440B-AE09-(E-Mail Removed)...
> MaxPrice: SWITCH( price1>=price2 AND price1>=price3, price1,
> price2>=price3, price2, true, price3)
>
>
> as computed expression, then, use MaxPrice as required (except in the
> WHERE clause, or in the ORDER BY clause). If you need to use that value in
> the orderby clause, or in the where clause, save that query and use
> another query that will use that saved query.
>
> I assumed your prices are NOT null.
>
>
> Vanderghast, Access MVP
>
>
>
> "Burnett71" <(E-Mail Removed)> wrote in message
> news:956799B6-22FB-464B-B434-(E-Mail Removed)...
>> For some reason, I can't figure this out...
>>
>> I have 3 currency fields in a table with varying amounts. I have a query
>> in
>> which I need to compare the amounts in these three fields, then select
>> the
>> lowest value for use in a subsequent calculated field. I'm hoping and
>> looking for a simple function or module to accomplish this. Any
>> suggestions?
>

Thank you for your response. Definitely gave me what I needed to finish this
job out.

"JimBurke via AccessMonster.com" wrote:

> I don't know of a Min function in VBA - if there is one you would just use
> that, e.g. create a new field using something like:
>
> MinValue: Min(Cur1,Cur2,Cur3)
>
> Assuming there is no Min function, the only way I can think of to do this in
> that same query is to create a new field based on an IIf statement. I'll call
> the field with the minimum value of the three MinValue, and I'll call the
> three currency fields Cur1, Cur2, Cur3. Create a new field in the query like
> this:
>
> MinValue: IIf(Cur1 < Cur2 and Cur1 < Cur3, Cur1, IIf(Cur2 < Cur1 and Cur2 <
> Cur3, Cur2,Cur3))
>
> Pretty sure that should work, assuming all 3 currency fields will always have
> values. If it's possbile some or all 3 currency fields may have null values
> sometimes, then you'd need to decide what you want to do in case of a null
> value. If you wanted to treat a null value as 0 you'd have to replace the
> field name in the comparison using the nz function, e.g. nz(Cur1,0) < nz(Cur2,
> 0), etc. If you don't want to use a null value as a minimum you could use Nz
> and assign a very high number that you know will always be < the other
> numbers, e.g. Nz(Cur1,999999) or something like that.
>
> Burnett71 wrote:
> >For some reason, I can't figure this out...
> >
> >I have 3 currency fields in a table with varying amounts. I have a query in
> >which I need to compare the amounts in these three fields, then select the
> >lowest value for use in a subsequent calculated field. I'm hoping and
> >looking for a simple function or module to accomplish this. Any suggestions?
>
> --
> Jim Burke
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200906/1
>
>