Query to take lowest of 2 fields and put in another field

[ragnarok75]

I have two fields in the same table that I need to calculate a result on a report, but I need to use the field that is the lowest out of the two fields.

Example:
FieldA FieldB
100 100 does not matter which field is used
200 100 need to use fieldB
450 300 need to use fieldB
300 450 need to use fieldA

I know on a form I can use an If Then statement, or a Case statement, but I do not know how to either get a query to put the results in one field or have a report do the same. Once I have the result, I then need to sum it for different groupings on a report. That is why I thought I could use a query to take the results and put them in one field. I am running into issues doing this. Any help is appreciated.

 

[jordanking]

you could run two different gueries.

SELECT FieldA FROM myTable WHERE FieldA > FieldB
SELECT FieldB FROM myTable WHERE FieldB > FieldA

Thene you can change these to update/append queries once you confirm they are giving you the desired results.

Someone else might be able to make this more compact.

.....
I'd rather be surfing

 

[PHV]

IIf(FieldB<FieldA,FieldB,FieldA)

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