selecting the lesser of two amounts

[Mike Hughes]

Query returns “amount paid” and “amount due” for the month.

From these results I want to select the lesser of the two amounts and if they are
The same amount than just return that amount.

EXAMPLE 1: amount paid $100 amount due $200 query would return $100.

EXAMPLE 2: amount paid $200 amount due $200 query would return $200

EXAMPLE 3: amount paid $200 amount due $100 query would return $100

Can someone help with the formula needed in my query to return these results?

 

[R6Flyer]

A calculated field in your query of:

ReturnAmount:IIf([amount paid]<[amount due],[amount paid],[amount due])

Place the above statement in the field name section of a blank column in your query and run it - it should return what you are after.

Cheers

Flyer

 

[Mike Hughes]

Well I think I did what you suggested and this is what the query now looks like.

SELECT [CURRENT PAID --- CASE LEVEL].ID_CASE, [CURRENT PAID --- CASE LEVEL].IV_D_DO_CODE, [CURRENT PAID --- CASE LEVEL].WORKER_ID, [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].CURRENT_OWED, [CURRENT PAID --- CASE LEVEL].[CURRENT PAID], [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].CD_TYPE_WELFARE, IIf([current_paid]<[current_owed],[current_owed],[current_paid]) AS ReturnAmount
FROM [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE] INNER JOIN [CURRENT PAID --- CASE LEVEL] ON [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].ID_CASE = [CURRENT PAID --- CASE LEVEL].ID_CASE;

I'm now being asked to enter the parameter value for current_paid

What did I do wrong ?

 

[pbaldy]

It looks like that field in the IIf() has an underscore, the field in the query does not.

 

[Mike Hughes]

I tried running it both ways and didn't make a difference.....

 

[WayneRyan]

Mike,

Can you post the db with the query and table?

Tools --> Database Utilities --> Compact/Repair
then ZIP
then attach

Wayne

 

[Mike Hughes]

Sorry no, the tables are linked to the data in an Oracle database at my office. I'm not storing any data in this database.

 

[WayneRyan]

Mike,

All of those spaces, make reading tougher.

You are not using a table name qualifier for the fields in the
Iif statement. It doesn't know how to resolve the name
so it asks you for a value.

Put in the [tablename].[fieldname]

Wayne

 

[Mike Hughes]

That worked....thank you very much !

This is how it ended up

SELECT [CURRENT PAID --- CASE LEVEL].ID_CASE, [CURRENT PAID --- CASE LEVEL].IV_D_DO_CODE, [CURRENT PAID --- CASE LEVEL].WORKER_ID, [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].CURRENT_OWED, [CURRENT PAID --- CASE LEVEL].[CURRENT PAID], [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].CD_TYPE_WELFARE, IIf([CURRENT PAID --- CASE LEVEL].[CURRENT PAID]>[NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].[CURRENT_OWED],[NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].[CURRENT_OWED],[CURRENT PAID --- CASE LEVEL].[CURRENT PAID]) AS ReturnAmount
FROM [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE] INNER JOIN [CURRENT PAID --- CASE LEVEL] ON [NEW CURRENT SUPPORT OWED USING CSO PART 1 BY CASE].ID_CASE = [CURRENT PAID --- CASE LEVEL].ID_CASE
WHERE ((([CURRENT PAID --- CASE LEVEL].ID_CASE)=[xxxxxxxxP]));

 

[WayneRyan]

Mike,

Good to hear it ... Now about those names.

Wayne

 

[vengsiva]

I tried running it both ways and didn't make a difference.....

I think the following query will solve the problem
SELECT Table1.Amountpaid, Table1.Amountdue, IIf([Amountpaid]=[amountdue] Or [Amountpaid]<[amountdue],[amountpaid],[amountdue]) AS returnamount
FROM Table1;