Access Report displaying pieces from a String

[WindowsAndDoors]

I have been racking my brain for a week now and if you don't mind I would like to throw it to the group to see what the folks think.

I have a database that holds100 records, there are multiple fields in each record but there is one in particular that is defined as a string and contains numbers that are deliminated by commas,(ie:"1,2 ,3,6,8,9"). Now the version of Access allows me to do a split with no problem and I have successfully been able to read each number from the array created by the split. Here is where is have been running into a wall...I have to display on a report nine checkboxes but I only want the ones checked that are in the string of each of the 100 records.

So for instance if the first record has a text string of "1,2,3,4" on the report of that record I only want the boxes checked for 1,2,3, and 4.
If the second record is "1,2,5,9" then I only want those corresponding boxes checked.

The road that I have been down now is a function that I put in the detailed area of the report. Here is that code for the function:

Public Function WhichLocationsAreChecked(Location_str As String) As Integer
    Dim ThePosition As Integer
    Dim TheChar As String
    Dim TheCnt, LoopCnt As Integer
    Dim Location_arr() As String
 
    TheChar = ","
 
    Location_arr = Split(Location_str, TheChar)
    TheCnt = UBound(Location_arr) + 1
 
    For LoopCnt = 1 To TheCnt Step 1
        Select Case Location_arr(LoopCnt - 1)
            Case 1
                Reports!businessunitproductmeeting!Gainesville_chkbx.Value = True
            Case 2
                Reports!businessunitproductmeeting!Greenwood_chkbx.Value = True
            Case 3
                Reports!businessunitproductmeeting!Huntington_chkbx.Value = True
            Case 4
                Reports!businessunitproductmeeting!Ladysmith_chkbx.Value = True
            Case 5
                Reports!businessunitproductmeeting!Logan_chkbx.Value = True
            Case 6
                Reports!businessunitproductmeeting!Med_CP_chkbx.Value = True
            Case 7
                Reports!businessunitproductmeeting!Med_Glass_chkbx.Value = True
            Case 8
                Reports!businessunitproductmeeting!Med_MW_chkbx.Value = True
            Case 9
                Reports!businessunitproductmeeting!Med_Vinyl_chkbx.Value = True
            Case 10
                Reports!businessunitproductmeeting!Mosinee_chkbx.Value = True
            Case 11
                Reports!businessunitproductmeeting!Parkfalls_chkbx.Value = True
        End Select
    Next LoopCnt
 
    WhichLocationsAreChecked = TheCnt
 
 
End Function
 

So is there any ideas, any hope or should I quit and become a rodeo clown?

Thanks,
Mike

[PEB]

Hi Mike,

I suppose this function that you've created is invoked from the detail section...

If i've been in your place i would prefer to do the split on query level.. Creating as columns all possible values of checkboxes in your report..

So in your report you shouldn't write any code...

And in fact you need to create only one function in a module that returns the respective number of array obtained by split..

Like this one:

Function Verify_word_in_ array(My_field, wanted_value) as boolean
if i try to express myself in russian...
End function



So in your query your first column for check should be like:

Check1: Verify_word_in_ array("1,2,3,4" ,"1")
Check2: Verify_word_in_ array("1,2,3,4" ,"2")
......
Check9: Verify_word_in_ array("1,2,3,4" ,"9")

[WindowsAndDoors]

Sometimes it just takes posting a question and then a good walk. As I was looking over some documentation for a good rodeo clown college I said just take it back to basics...a function should only return 1 thing so I changed the code and ran the function from each checkbox control on the "on open" event and it works like a charm. Here is the code:

Public Function WhichLocationsAreChecked(Location_str As String, Target As Integer) As Boolean
    Dim TheChar As String
    Dim TheCnt, LoopCnt As Integer
    Dim Location_arr() As String
    Dim Boolean_flg As Boolean
 
    TheChar = ","
    Boolean_flg = False
    Location_arr = Split(Location_str, TheChar)
    TheCnt = UBound(Location_arr) + 1
 
    For LoopCnt = 1 To TheCnt Step 1
        If Location_arr(LoopCnt - 1) = Target Then
            Boolean_flg = True
        End If
    Next LoopCnt
 
    WhichLocationsAreChecked = Boolean_flg
End Function
 

I would like to thank all who wrote back or hell even took a look at this.

be good,
Mike

Hi Mike,

I suppose this function that you've created is invoked from the detail section...

If i've been in your place i would prefer to do the split on query level.. Creating as columns all possible values of checkboxes in your report..

So in your report you shouldn't write any code...

And in fact you need to create only one function in a module that returns the respective number of array obtained by split..

Like this one:

Function Verify_word_in_ array(My_field, wanted_value) as boolean
if i try to express myself in russian...
End function



So in your query your first column for check should be like:

Check1: Verify_word_in_ array("1,2,3,4" ,"1")
Check2: Verify_word_in_ array("1,2,3,4" ,"2")
......
Check9: Verify_word_in_ array("1,2,3,4" ,"9")

[PEB]

Glad for u!