CODEWITHSUNDEEP
ms-access
frons79
frons79

Reputation: 25

MS Access: trouble with concatenating operators

my issue here is with concatenating operators, & and +.

I have to concatenate text boxes with names such as txt1, tx11, txt2, txt21, txt3, txt31 and so on with the following final structure:

**txt1 (txt11), txt2 (txt21), txt3 (txt31)**

where txt1 is always present and it's the only one that will be present and never Null. All the other text boxes may be Null, but if there is a txt with double digit (i.e txt11, txt21, txt31) then it needs a comma before and parentheses around it (besides the first one, txt11, that only needs parentheses) in order to recreate the above structure.

Problem is that I cannot find a proper combination of & and + to achieve my goal, sometimes there are text boxes missing, sometimes parentheses without text boxes inside, things like that.

Any suggestion on how to proceed?

Upvotes: 0

Views: 30

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49059

This should work:

 Public Function GetControls

  Dim strResult     As String
  Dim i             As Integer
  Dim strF          As String
  
  strResult = ""
  
  For i = 1 To 33
     
     strF = "txt" & i    ' setup text box name
     
     If Nz(Me(strF), "") <> "" Then
        If strResult <> "" Then strResult = strResult & ","
        
        If i >= 11 Then
           strResult = strResult & "(" & Me(strF) & ")"
        Else
           strResult = strResult & Me(strF)
        End If
  
  Next i
 GetControls = strResult

End Function

So the above can be put in a form as public function.

Then, to display in a text box in that form, set the control source to

=GetControls()

So this simply loops the 1 to 33.

Say, if first 5 have none, then we have nothing.

If we have one or several, then of course we add the "," with this check

  If strResult <> "" Then strResult = strResult & ","

(yes, that is a VBA one-line bit of code!)

Then we check if 11 or greater and thus put "() around the value. But the concept of adding "," and "()" are kind of separate tasks.

Also, having 1 to 33 controls? Well, I will keep this lecture short about lack of normalized data here.

You will be forever be relegated to writing all kinds of routines to deal with such issues. And near 99% of them would not be required - especially when it comes to you trying to report on that data - you going to suffer here. Thus such data this way is bad idea, and a bad design. With normalized data, you not be writing so many loops. Now in this case I will admit that you would need a similar loop as per above against a child table, but you would not be limited to 33 values, and you could have 2 or 40. But come query time, with normalized data, you can use sum(), counts(), and even group data - and you don't have to change code, reports or whatever if you decide to have 2 or 40 values.

Upvotes: 1

Related Questions