Stringing Multiple YES/NO Text Boxes on report

[Christopher770]

I may have gone about this the wrong way. However...this is the deal.

• I have a Table of Special instructions. Each type of a Yes or No Text Box. There are 13 items in this table along with the ID key.
• Each Field has a Special Description. I used the Y/N format for ease of use for user input to simply select the applicable options.
• However, I need the text description to display on the printed report, which is not the problem.
• So i created a separate text box for each item that simply says; If True, "Description", else blank. And named each one sp1...sp14.

So now, I want to take these text boxes with the proper descriptions and string them together.

My formula is: =Trim([sp1])&" "&([sp2]) etc.

This does produce the proper text results, however, and oddly enough, each item displays on its own line rather than in a string.

I get:
SP1
SP1

Instead of the desired result of SP1 SP2

This seems to simple, and probably has to do with the yes/no format. I've tried with and without (), and using + instead of &, and to troubleshoot, I eliminated the " ". No luck. Everything is coming back as a single column.

Any suggestions?

 

[Galaxiom]

I may have gone about this the wrong way.

I strongly suspect you have done it all wrong but it is hard to be sure from your description. Clearly describe the structure you do have.

You would be well advised to forget about the concatenation for now and get the data structure correct. Otherwise you will spend a lot of time on frustrating workarounds.

 

[Christopher770]

Without uploading the database, which I could do, I’ll try to be as detailed, yet brief as possible.

Table 1 Structure
• Id Auto-ID
• More fields (Details irrelevant)

Table 2 Structure
• Id Number (LongInt)
• BHSPLGDELV = DataType: Yes/No – Display Control = Check Box
• BHSPAPPT = DataType: Yes/No – Display Control = Check Box
• BHSPFREEZ = DataType: Yes/No – Display Control = Check Box

Relationships: Table1 [ID] = Primary Key, integrity enforced. All other tables joined to Table1 based on the like key of [ID]. One-To-Many Relationship

Table 2 Comments: I need the Yes/No check box for ease of use on the Data Entry Form used by Table 2. The result of the check box = Zero or Not Zero. There are thirteen of these data check box’s data fields.

All of these items in the Table2 structure can be selected, but they cannot be replicated per ID. Example: The user could select either [BHSPLGDELV] or [BHSPAPPT], or both, but not [BHSPAPPT] more than once. For the sake of shortening this, I’ve not listed every field. They’re all the same just different descriptions.

Report Structure:

• Based on the reports Select Statement Control Source where Table1:ID =Table2:ID(One-to-many), the select statement is the Reports Control Source which generates the reports:querybuilder where all the supporting fields are selected. There is no filtering, expressions or calculations done within this query, for no other reason than it isn’t required.
• Report is Grouped on Table1:ID
• When the user selects any one or all of the check box items in table 2, I need to display the actual description of the item the user selected.
o I do this through a simple If statement in a text box
o Statement: =iif([BHSPLGDELV]<>0,”Liftgate Delivery”,Null)
• I’ve created 13 text boxes on the report for each of these. I cannot use the field ID or the YES/NO.
• Each text box created on the report is named [SP1], [SP2],...[Sp13].
• I concatenated these as described previously, which produces correct verbiage, but all the resulting TEXT is in a single column, instead of separated either by a space or comma string.
o New Text Box: [myDeliveryInstructions]
o Syntax: =Trim([sp1])&" "&([sp2])
o Returns:
 Liftgate Delivery
 Appointment Required
o Expected Results:
 Liftgate Delivery, Appointment Required
 **Note: Doesn’t matter the delimiter I use, “,” or “ “ or “-“, I get the same results

Thank you very kindly for your taking the time to read this. This is driving me bonkers. I do not have room on the report to leave as is. As I led off originally, I may have gone about this all wrong as these Yes/No could have just as easily been in Table 1, but I really don’t see how that matters at all. I have several other concatenations on the same report which all work fine.

 

[Christopher770]

SOLVED: Solved by post from Kafrin #204582, but I cannot post the link here. 'His code
--------------------------------------------------------------------

Public Function RepString(ParamArray Tests() As Variant) As String
'Tests() requires pairs of values - the True/False from the check box and the string (name) that corresponds to it.
Dim S As String, Ctr As Byte

S = ""

For Ctr = LBound(Tests) + 1 To UBound(Tests) Step 2
If Tests(Ctr-1) = True Then
If S <> "" Then S = S & ", "
S = S & Tests(Ctr)
End If
Next Ctr

RepString = S
End Function