MAX Function

[CBJ]

Hi Guys - Help please

I have two fields in different tables. One field called StandardQty the
other field called UsedQty, both relating to products. I need to run a query
that will create an extra (ChargedQty) column and comparing the two fields,
returning the highest value only for each product.

The scenario is that if the UsedQty is more than the StandardQty then it
returns the UsedQty value. if The used Qty value is less than the
StandardQty, then StandardQty value will be returned. In either event it is
the MAX value each time.

Any help would be great!

Thanks

 

[Douglas J. Steele]

IIf(Nz([StandardQty], 0) > Nz([UsedQty], 0), [StandardQty], [UsedQty])

 

[CBJ]

Hi Douglas

Just run the query - works like a dream!
I've been scratching my head for hours on this one.

Thank you so much.

IIf(Nz([StandardQty], 0) > Nz([UsedQty], 0), [StandardQty], [UsedQty])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Guys - Help please

I have two fields in different tables. One field called StandardQty the
other field called UsedQty, both relating to products. I need to run a
query
that will create an extra (ChargedQty) column and comparing the two
fields,
returning the highest value only for each product.

The scenario is that if the UsedQty is more than the StandardQty then it
returns the UsedQty value. if The used Qty value is less than the
StandardQty, then StandardQty value will be returned. In either event it
is
the MAX value each time.

Any help would be great!

Thanks