1

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?

Shanoy
  • 55
  • 1
  • 2
  • 7

1 Answers1

1

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:

Colours Table with computed fields

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
cha
  • 10,301
  • 1
  • 18
  • 26