Find exact match or first bigger number in Access database

Dejan Petrovic 0
Viewed 36

I have next problem. There is a table with four columns:

|ID |  X |  Y | VAL |
|:--|:--:|:--:|----:|
|1  | 1  | 1  | 1110|
|2  | 1  | 2  | 1720| 
|3  | 1  | 3  | 2330|
|4  | 1  | 4  | 2940|
|5  | 1  | 5  | 3550|
...

When user enter some value in text field e.g. 2370 i need function to find is there exact match in VAL field and if not to find very first bigger than 2370 (2940) and return ID value. In some other language I can do it trough dictionaries and so one but in VBA I simply don't have idea. Any idea or help will be appreciated.

ms-access vba
1 Answers

You can use a query to get this answer, using TOP 1 to just return 1 record:

SELECT TOP 1 tblData.ID, tblData.VAL
FROM tblData
WHERE (((tblData.VAL)>=2370))
ORDER BY tblData.VAL ASC;
Applecore0
Related