Expression Builder: Get MIN if Next Column Value > 0

[luzmen]

Hello,


I have this query to get the information base on multiple Table (dB).

I have one Table where the data has multiple entry, example:


ID Date Amount
17-01 01/02/2017 0.00
17-01 02/02/2017 1,000.00
17-01 03/02/2017 500.00

I used "Min" in Total of my update Query, but the result is 01/02/2017. I want the result to be 02/02/2017 since amount of 01/02/2017 is zero.

If it will be through the Expression Builder, what should be the expression?

Hope you understand my problem.

 

[isladogs]

ID Date Amount
17-01 01/02/2017 0.00
17-01 02/02/2017 1,000.00
17-01 03/02/2017 500.00

I used "Min" in Total of my update Query, but the result is 01/02/2017. I want the result to be 02/02/2017 since amount of 01/02/2017 is zero.

This is as clear as mud...

Whether you used Min for Date or Amount fields, the result for the above data will pull the record for 01/02/2017

The data for 02/02/2017 is the maximum amount

If you want the day after the Min, add 1 to the date e.g. [Date]+1

BTW Date is a reserved field in Access - call it something meaningful e.g. OrderDate

 

[Minty]

In addition to Ridders comments, if you want to exclude 0 values you would need to add a criteria to your Amount to <> 0. You may need a sub query to get that to work.

 

[isladogs]

In addition to Ridders comments, if you want to exclude 0 values you would need to add a criteria to your Amount to <> 0. You may need a sub query to get that to work.

Good point.
Should've read the post title line...