In database, I have stored colors only in MS Access color format (decimal format of BGR): it looks like 8689859 => brown => #c39884 ... But I need to filter by the color range, I mean user use the color picker and pick the eg. #ffffff color. And I would like to return all rows with color alike the #ffffff (not exactly the same color but +- in same range).
Is something like that possible?
I guess the =- range should apply to each of the separate colour elements in the BGR value. In this case you need to extract these elements, like this:
SELECT colour
,Fix([Colour]/(256*256)) as B
,Fix(([Colour]-Fix([Colour]/(256*256))*256*256)/256) as G
,[Colour]-Fix([Colour]/(256*256))*256*256-Fix(([Colour]-Fix([Colour]/(256*256))*256*256)/256)*256 as R
FROM MyColours
I recommend you put these into separate functions like GetR, GetB, GetG
You can even make the computed fields and apply them to your table:
Now, to lookup your table you need to split the selected colour in B G R elements as well:
Dim selectedColour as Integer
Dim rr as Integer
Dim gg as Integer
Dim bb as Integer
selectedColour = 11124168 ' A9BDC8 - selected from the colour picker
rr = getR(selectedColour)
gg = getG(selectedColour)
bb = getB(selectedColour)
If you have the computed fields in the table then the query will be:
SELECT colour
FROM MyColours
WHERE B BETWEEN bb-4 AND bb+4
AND G BETWEEN gg-4 AND gg+4
AND R BETWEEN rr-4 AND rr+4
If you do not want the computed fields use the VBA functions:
SELECT colour
FROM MyColours
WHERE getB(colour) BETWEEN bb-4 AND bb+4
AND getG(Colour) BETWEEN gg-4 AND gg+4
AND getR(Colour) BETWEEN rr-4 AND rr+4