Concatenated string?

[gusbrunston]

Hi.

I tried this months ago, and I didn't know enough about access or VBA to post it in the right forum, or to understand the answers I got. Here goes again...

In a table describing rental properties, I have many check boxes, e.g., "Air-conditioned", "Community Pool", "Extra parking", etc. The company has long used on a report used by leasing agents a concatenated string: " AC CP EP ,etc." to identify features of particular rentals.

At present, after clicking on the check boxes, the user has to type in the corresponding string.

How can I concatenate a string from check box fields in the table... in the table? in a query? on a form? in the report?

Thanks for ideas, or for simply pointing to the area I need to study. In days gone by I would have built an array of two character strings, and put them together with:

StringA = String(1) + " " + String(2) + " " String(3)

Gus Brunston An old PICKer, using Access2000
gustrel@aol.com

 

[Maquis]

Well I think you already have the right idea. You just need to concatenate a string based on the check boxes.

Let's say you have a form with a lot of checkboxes on it. When the user clicks a button/closes the form/etc just look at the boxes and build your string.

Public Sub somethingorther()
Dim stringA as string

stringA = ""
if me!checkbox1 = true then stringA = stringA & " AC"
If me!checkbox2 = true then stringA = stringa & " EC"
etc etc etc
Maq B-)
<insert witty signature here>

 

[gusbrunston]

Maq:

Thanks so much for your response. I'm not in a place to try your suggestion but when I am I'll post my result.

Offhand, there are about 50 check boxes. An array of 1 - 50 boxes would be helpful--e.g. &quot;Dim CBoxes(50)...,etc.&quot;

Gus Brunston An old PICKer, using Access2000
gustrel@aol.com

 

[BoxHead]

Gus,

If you're not using the tag property for anything else, you could set the tag property for each check box to the abbreviation you want in your string. Then you could use the control collection for building the string.

You would also have to add to a field to the table for the string. In the Table Design view, add the field and set the length as neccessary. Do allow zero-length strings.

In the example, I built a table called tblProperties with address fields as well as 15 checkbox fields for property features I called Feature1 through Feature15. The field that holds the concatenated string is called FeatureString and the function that builds it is called GBsString.

I built a form with all fields from the table and set the tag properties for the checkboxes to be &quot;FTR1&quot; through &quot;FTR15&quot; as appropriate.

The following function will run through the controls collection and check to make sure the control is a checkbox. If it is a checkbox and the value is -1 (checked), the tag property (your abbreviation) is added to the string in the FeatureString field.

Public Function GBsString() As String

Dim ctl As Control
Dim strList As String


For Each ctl In Me.Controls
    If ctl.ControlType = acCheckBox Then
        If ctl = -1 Then
            strList = strList & ctl.Tag & " "
        End If
    End If
Next ctl

GBsString = strList


End Function

On the Form's Current, BeforeUpdate & Load events, you would call

    FeatureString() = GBsString

You could also call this on each checkbox's AfterUpdate if you want to keep it current as changes are made on the form.

Admittedly, this process assumes that your users only have access to the data through the form. If someone goes into a table or query and changes the value of a checkbox, the string will not be updated until the Form is run and one of the events occurs to call the function.

I hope this makes sense and is helpful.




John

Use what you have,
Learn what you can,
Create what you need.

 

[MichaelRed]

Gus,

I rember this thread. My Suggestion stands. NAME the check boxes in a standard format e.g. chkAC for Air-Conditioned, and make the LABEL for the check box show &quot;Air-Conditioned&quot;. To create the concatenated string, just enumerate the controls on the form. For all of the checkboxes, then check the value, and for those where it is non-zero, add the Right 2 (in my example) to the string.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over