Concatenation for an English rewording

[tfaiers]

I've been trying to think this one through and thought I'd throw it out there for some pointers as I think someone must have done this before.

I've 5 yes/no fields, call them Alpha, Beta, Charlie, Delta and Echo.

I'd like to place on a report, an English wording to the selected fields including correct grammer, but the formula eludes me.

Essentially, if you were to select Beta, Delta and Echo, the English description would read on the report as "You've chosen Beta, Delta and Echo"

The same for Alpha and Delta which would read as "You've chosen Alpha and Delta"

Or just Charlie which would say "You've chosen Charlie" (no commas or and)

The problem is trying to add commas where relevant plus the word 'and' before the last word, or not at all if only one option is chosen.

I hope you can see the logic to my question, I've approached both from trying to use Iif statements with & and + references, but it's adding the 'and' or not and the commas that I'm struggling with, they don't appear where they should and I feel like I've tried multiple configurations to no avail.

Any suggestions would be most appreciated.

 

[plog]

I'd make a custom function for this. You pass it your five inputs, it returns your sentence.

Here's the psuedo code I'd use:

TotalUsed=0 
    ' how many inputs were used 

LastUsed=-1
    ' last value that was used--will put 'and' before it

ReturnValue="You've chosen "
    ' return value--will construct sentence to be spit out

InputArray = (put all inputs here)
    ' array to hold input values

InputWords={"Alpha", "Beta"...}
    ' array to hold words associated with each input    


Put all submitted values into InputArray so can loop through them 

Loop 1:
    Test all inputs to see if they are used:
    if used, add 1 to TotalUsed
    if used, set LastUsed to index


If TotalUsed=1 set LastUsed to -1 Else Subtract 1 from LastUsed

If TotalUsed=0 set up message to show no values chosen
Else Loop 2:
    Check InputArray to see if used, if so add InputWord to ReturnValue
    If counter variable = LastUsed spit out 'and' into Return Value


return ReturnValue & "."

I didn't account for commas between words, but that can be done in Loop 2 and might require another variable to account for how many you have already added to the sentence.

 

[tfaiers]

As often happens, throwing the question to the forum seems to makes the brain think differently, so I've got the following ugly code, but it works and someone might be able to sweeten it up or re-use it for themselves. It forms the correct sentence based on the choices returning a 'nothing chosen' if there's a zero count.

eng_desc = "You've chosen "

Dim stringcount As Integer

stringcount = 0
If [Alpha] = -1 Then stringcount = stringcount + 1
If [Bravo] = -1 Then stringcount = stringcount + 1
If [Charlie] = -1 Then stringcount = stringcount + 1
If [Delta] = -1 Then stringcount = stringcount + 1
If [Echo] = -1 Then stringcount = stringcount + 1

If stringcount = 0 then [eng_desc] = [eng_desc] & "nothing!"

If [Alpha] = -1 Then
[eng_desc] = [eng_desc] & "Alpha"
stringcount = stringcount - 1
If stringcount = 1 Then [eng_desc] = [eng_desc] & " and "
If stringcount > 1 Then [eng_desc] = [eng_desc] & ", "
If stringcount = 0 Then [eng_desc] = [eng_desc] & "."
End If

If [Bravo] = -1 Then
[eng_desc] = [eng_desc] & "Bravo"
stringcount = stringcount - 1
If stringcount = 1 Then [eng_desc] = [eng_desc] & " and "
If stringcount > 1 Then [eng_desc] = [eng_desc] & ", "
If stringcount = 0 Then [eng_desc] = [eng_desc] & "."
End If

If [Charlie] = -1 Then
[eng_desc] = [eng_desc] & "Charlie"
stringcount = stringcount - 1
If stringcount = 1 Then [eng_desc] = [eng_desc] & " and "
If stringcount > 1 Then [eng_desc] = [eng_desc] & ", "
If stringcount = 0 Then [eng_desc] = [eng_desc] & "."
End If

If [Delta] = -1 Then
[eng_desc] = [eng_desc] & "Delta"
stringcount = stringcount - 1
If stringcount = 1 Then [eng_desc] = [eng_desc] & " and "
If stringcount > 1 Then [eng_desc] = [eng_desc] & ", "
If stringcount = 0 Then [eng_desc] = [eng_desc] & "."
End If

If [Echo] = -1 Then
[eng_desc] = [eng_desc] & "Echo"
stringcount = stringcount - 1
If stringcount = 1 Then [eng_desc] = [eng_desc] & " and "
If stringcount > 1 Then [eng_desc] = [eng_desc] & ", "
If stringcount = 0 Then [eng_desc] = [eng_desc] & "."
End If

 

[vbaInet]

You could also use this:

Public Function ConcatVals(A As Boolean, _
                           B As Boolean, _
                           C As Boolean, _
                           D As Boolean, _
                           E As Boolean) As String
    Dim varConcat       As Variant
    
    Const STR_FIRST     As String = "You've chosen "
    Const STR_ALPHA     As String = "Alpha, "
    Const STR_BETA      As String = "Beta, "
    Const STR_CHARLIE   As String = "Charlie, "
    Const STR_DELTA     As String = "Delta, "
    Const STR_ECHO      As String = "Echo, "
    
    If A Then varConcat = STR_ALPHA
    If B Then varConcat = varConcat & STR_BETA
    If C Then varConcat = varConcat & STR_CHARLIE
    If D Then varConcat = varConcat & STR_DELTA
    If E Then varConcat = varConcat & STR_ECHO
    
    If varConcat <> vbNullString Then
        varConcat = Left(varConcat, Len(varConcat) - 2)
        varConcat = StrReverse(varConcat)
        varConcat = Replace(varConcat, ",", "dna ", 1, 1)
        varConcat = StrReverse(varConcat)
        ConcatVals = STR_FIRST & varConcat
    End If
End Function