Getting rid of excess blank spaces. 2

[haygordon]

I have a textbox that gathers information from many fields using the code:

=[forename] & " " & [outcomment1] & " " & [outjoin1] & " " & [outcomment2] & " " & [outjoin2] & " " & [outcomment3] & " " & [outjoin3] & " " & [outcomment4] & " " & [outjoin4] & " " & [outcomment5] & " " & [outjoin5] & " " & [outcomment6] & " " & [outjoin6] & " " & [outcomment7] & " " & [outjoin7] & " " & [outcomment8] & " " & [outjoin8] & " " & [outcomment9] & " " & [outjoin9] & " " & [outcomment10] & " " & [outjoin10] & " " & [outcomment11] & " " & [outjoin11] & " " & [outcomment12]

The outcomment fields are the selections from drop down boxes and the outjoins are also drop down selections.

Some users do not use all fields and the result is an excess of blank spaces, getting a result like:

Charlotte has worked very well and with developing confidence this year, she is making very good progress in performing, listening and composing. Compositions show an understanding of the musical devices used and an ability to take initial ideas and work them into successful pieces. structured tune in particular shows promise. Performing work is showing good progress with developing her practical skills. She is demonstrating an increasing understanding of the music to which we listen. Her home-made instrument worked and made an appropriate sound. An able musician who should do well. A target is to continue to work on developing and extending her musical skills. Well done!

I would like to be able to remove the unwanted spaces to just one space after each statement. Teachers are fussy and parents very quick to spot typos!

If I can't find a way to remove these spaces I have thought about the original comments that form a drop down box having a leading blank space but I can't work out a way to force this.

Any suggestions?

 

[PHV]

Perhaps this ?
=Replace([forename] & " " & [outcomment1] & " " & [outjoin1] & " " & [outcomment2] & " " & [outjoin2] & " " & [outcomment3] & " " & [outjoin3] & " " & [outcomment4] & " " & [outjoin4] & " " & [outcomment5] & " " & [outjoin5] & " " & [outcomment6] & " " & [outjoin6] & " " & [outcomment7] & " " & [outjoin7] & " " & [outcomment8] & " " & [outjoin8] & " " & [outcomment9] & " " & [outjoin9] & " " & [outcomment10] & " " & [outjoin10] & " " & [outcomment11] & " " & [outjoin11] & " " & [outcomment12], " ", " ")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haygordon]

Thanks PHV - I tried the amended code but got a message box saying it wasn't valid.

My code is in the textbox, is yours intended for the same location or am I missing something?

My problem is that the blank spaces might be 2, 3, 4 or even 5 in number.

 

[PHV]

So, the hard way:
=[forename] & IIf(Trim([outcomment1] & "")="", "", " " & [outcomment1]) & IIf(Trim([outjoin1] & "")="", "", " " & [outjoin1]) & ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haygordon]

Wow PHV, nearly there but a minor bug

I have tested it with:
=[forename] & IIf(Trim([outcomment1] & "")="",""," " & [outjoin1]) & IIf(Trim([outcomment2] & "")="",""," " & [outjoin2]) & IIf(Trim([outcomment3] & "")="",""," " & [outjoin3]) & IIf(Trim([outcomment4] & "")="",""," " & [outjoin4])

There was data in [forename][outcomment1][outcomment3][outjoin3][outcomment4] and [oujoin4]
None of the [outcomment] fields picked up and the result is:

Rebecca Her Her Rebecca

which is: [forename] [outjoin1][outjoin3]

I really wish I knew coding well enough to spot the bug.

Thanks for all your help.

Gordon

 

[PHV]

Please reread my previous suggestion !
Why outputting [outjoin1] when [outcomment1] is not null ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya haygordon . . . . .

I was surprised [blue]PHV's[/blue] first suggestion didn't work (it was perfect for the job!). Apparently [blue]Replace[/blue] has something against all those concatenations. So we'll make it easy for [blue]Replace[/blue] by providing a [blue]pre-concatenated string[/blue] so it doesn't have too.

In the code module of the form, copy/paste the following function:

[blue]Public Function FixSpc() As String
   Dim Pack As String
   
   Pack = [forename] & " " & _
          [outcomment1] & " " & [outjoin1] & " " & _
          [outcomment2] & " " & [outjoin2] & " " & _
          [outcomment3] & " " & [outjoin3] & " " & _
          [outcomment4] & " " & [outjoin4] & " " & _
          [outcomment5] & " " & [outjoin5] & " " & _
          [outcomment6] & " " & [outjoin6] & " " & _
          [outcomment7] & " " & [outjoin7] & " " & _
          [outcomment8] & " " & [outjoin8] & " " & _
          [outcomment9] & " " & [outjoin9] & " " & _
          [outcomment10] & " " & [outjoin10] & " " & _
          [outcomment11] & " " & [outjoin11] & " " & _
          [outcomment12]
   FixSpc = Replace(Pack, "  ", " ")

End Function[/blue]

Then for your textbox:

[blue]=FixSpc()[/blue]

[purple]Give it a whirl & let us know . . .[/purple]

See Ya! . . . . . .

 

[haygordon]

Aceman1 - your function has very nearly got there. I still got multiple spaces but I added an extra space to the fixspc line to give 3 spaces to check for and it worked fine where there are just one outcomment and one outjoin fields that are blank before the next selected outcomment or outjoin. Where there are more than one then extra blanks get put in.

The following text used [forename],[outcomment1],[outjoin1] [outcommnt3],[outjoin4], [outjoin5] and [outjoin12]

Rebecca has shown excellent progress. Her compositions show an understanding of the musical language used and an ability to take initial ideas and work them into simple pieces. Rebecca is learning to be able to perform individually. Why are we bothering.

Where 2 fields had been skipped it worked absolutely brilliantly, but where 12 fields were missed, this left 6 spaces.

Sorry to keep on....

 

[TheAceMan1]

haygordon . . . . . .

In that case, we need to kick the [blue]Replace[/blue] function in the rear a few more times. Replace the code with the following:

[blue]Public Function FixSpc() As String
   Dim Pack As String, x As Integer
   
   Pack = [forename] & " " & _
          [outcomment1] & " " & [outjoin1] & " " & _
          [outcomment2] & " " & [outjoin2] & " " & _
          [outcomment3] & " " & [outjoin3] & " " & _
          [outcomment4] & " " & [outjoin4] & " " & _
          [outcomment5] & " " & [outjoin5] & " " & _
          [outcomment6] & " " & [outjoin6] & " " & _
          [outcomment7] & " " & [outjoin7] & " " & _
          [outcomment8] & " " & [outjoin8] & " " & _
          [outcomment9] & " " & [outjoin9] & " " & _
          [outcomment10] & " " & [outjoin10] & " " & _
          [outcomment11] & " " & [outjoin11] & " " & _
          [outcomment12]
   
   For x = 1 To 7
      Pack = Replace(Pack, "  ", " ")
   Next
   
   FixSpc = Pack

End Function[/blue]

If you still get spaces, increase the frequency of the [blue]For Next[/blue] loop . . .

See Ya! . . . . . .

 

[PHV]

As you seem to not follow the IIf way I suggested you may amend TheAceMan1 code.
Replace this:
For x = 1 To 7
Pack = Replace(Pack, " ", " ")
Next
FixSpc = Pack
By this:
While Instr(Pack, " ")
Pack = Replace(Pack, " ", " ")
WEnd
FixSpc = Trim(Pack)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[haygordon]

Thank you aceman1 and PVH Both code versions worked perfectly and I will now have a happy bunch of teachers with less work to do correcting their annual reports. A few seconds saved makes up a lot of time for some who are writing 500+ reports.

Thanks once again.

Sheer brilliance that I aspire to but see it a long way off.