Get next higher value OR if result empty: return max

[peng21]

Hallo,

I'm trying to create a query which returns me the next higher number:

My table: tblPersons

ID, Name, balance,
10  John   1000
11  Alice   2000
12  Bob    3000

My query:

SELECT TOP 1 name FROM tblPersons WHERE( (balance)>([InputBalance])) ORDER BY balance ASC

Input:
[InputBalance] = 500
Result: John

[InputBalance] = 1234
Result: Alice

[InputBalance] = 9999
Result: EMPTY
Result should be Bob

Can someone tell me please if it's possible to combine this logic into one query?

Thanks

 

[namliam]

How about

SELECT TOP 1 name 
FROM tblPersons 
WHERE( (balance)>([InputBalance])) 
[COLOR="Red"]or balance = Dmax([Balance], "tblPersons") [/COLOR]
ORDER BY balance ASC

 

[namliam]

that wouldnt return the highest value if the inputbalance is higher than the max balance in the table CJ?

 

[CJ_London]

you are right - I had realised and tried to delete the post but for some reason it wouldn't delete

 

[namliam]

LOL and now you did delete it and my post seems very random :/