How to have query display in one cell the true values of many check boxes

[hockey8837]

Hi,
I have DB based on flower information; i.e. flower name, growth patterns and needs, bloom time, and bloom color.

On my Flower Details form, I have a subform featuring bloom colors in check boxes. Since a flower can have multiple bloom colors, I decided to use check boxes rather than have some kind of combo box to select only one bloom color.

Now, I'm trying to create a query that will combine all of the 'true' check boxes for a flower into one box. I was thinking of using some kind of IIf(Null) statement, like I did with flower name:

Flower Name: IIf(IsNull([Latin Name]),[Common Name],[Latin Name] & ", " & [Common Name])
​

Is this the right track for true/false boxes? What type of statement do I write in the query if I want a box that will list only the true colors for that flower, like "blue, purple, pink," (etc.), of all my possible 11 color check boxes.

thanks!

 

[apr pillai]

You can do it with a Public Function. The sample Code is given below. Copy and Paste it in the Standard Module:

Public Function GetColors(ByVal firstArg As Variant, ParamArray Args()) As String
Dim i, j, coloridx(0 To 4) As String, colorout As String

coloridx(0) = "Red"
coloridx(1) = "Green"
coloridx(2) = "Blue"
coloridx(3) = "Purple"
coloridx(4) = "Pink"

i = UBound(Args)
colorout = ""
For j = 0 To i
    If Args(j) Then
       If Len(colorout) = 0 Then
          colorout = coloridx(j)
      Else
          colorout = colorout & "," & coloridx(j)
      End If
    End If
Next
GetColors = colorout
End Function

Call the Function from the Query Column like the following:

myColors: GetColors([flowerName], [color1],[color2]...)

First parameter can be any field, second parameter onwards give all the color checkbox field names.

You may update all the 11 color names in the code above by increasing the dimension statement.

 

[dcb]

flower name, growth patterns and needs, bloom time, and bloom color.

I think you need to address your table structure here
You have one to many relationships ie.
one flower -> many colors --- New Table
one flower = one growth pattern (I hope) ---Same Table
one flower -> many needs ----New Table
one flower -> many bloom times ---New Table
one flower name = one latin name --- Same Table

Sorry I dont do flowers so maybe I am wrong about the detail.... However if you dont address the table structure you are going to have some interesting moments when you start trying to do queries. Consider trying to query all flowers that have blue and red to get a list of possible flowers.... you are going to snooker yourself.

After you fix your tables the rest should be easy
Dont lose the checkbox idea - its great

 

[hockey8837]

Call the Function from the Query Column like the following:

myColors: GetColors([flowerName], [color1],[color2]...)

First parameter can be any field, second parameter onwards give all the color checkbox field names.

Hi,
Thanks for the suggestions. Not sure if I'm doing this right, I'm having some problems getting this to execute properly. I put the code into a standard module, called 'GetColors', edited it with my colors and saved it.

Now, I've tried to create a query called 'BloomColorExtended' that pulls from the 'BloomColor' table, which tracks the FlowerID, ColorID, and has all 11 colors and the check boxes displayed in the table.

I entered the following code into one of this query's columns:

myColors: GetColors([FlowerName],[Red],[White],[Pink],[Orange],[Yellow],[Green],[Blue],[Purple],[Violet],[Brown],[Black])

When I try to run this, I get 'undefined function 'GetColors' in expression'.

I also tried to create a combo box in my BloomColor subform, which is on my FlowerDetails main data entry form with this same code, to see if this would call the module from there-but it didn't.

Did I miss a step?
Thanks!

 

[JANR]

I put the code into a standard module, called 'GetColors', edited it with my colors and saved it.

Big no,no NEVER name your modules the same as functions...

Rename your module to basGetColors or something.

JR

 

[hockey8837]

Ahhhh!!! Eureka!
That was it, Thanks a million!