End of Text Box - Replace Comma with Period

[bburton]

I am building a report which will pull data from a form. There are a lot of fields that will be listed and separated by commas.



Basically the box will look like this:
= IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", ")

I need the last comma of that statement to be replaced with a period regardless of what data shows up and doesn't. What code would do this?

Thank you for your help.

[June7]

= IIf(IsNull([1]),"",[1] & IIf(IsNull([2] And IsNull([3],".",", ")) & IIf(IsNull([2]),"",[2] & IIf(IsNull([3],".",", ")) & IIf(IsNull([3]),"",[3] & ".")

[bburton]

That would work, however there is going to be over 20 different fields listed in there which would make that a bit too long and complicated while using that method. I am hoping to find another method that would simply just look at the end of the box and replace the last comma with a period.

[Rhino373]

How about something using the Left(), Len() and Concatenate functions?

Left(IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", "),Len(IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", "))-2) & "."

[bburton]

If the fields were blank, then it'd still leave those punctuations wouldn't they? So that wouldn't work out as well as I'd like it to.

[Rhino373]

No, if the fields were blank they wouldn't leave a comma or a space. According to your example it only enters a comma and space if the field was not blank.

All my example does it take exactly what you have and find everything but the last two characters (which would be a comma and a space) and replace them with a period.

[bburton]

No, if the fields were blank they wouldn't leave a comma or a space. According to your example it only enters a comma and space if the field was not blank.

All my example does it take exactly what you have and find everything but the last two characters (which would be a comma and a space) and replace them with a period.

Hmm, well I just tried it out (Field 1 blank, 2 inputted, 3 blank) and it basically spat out " , 1 7/8" Pintle, . " Both commas should not be there, only the period should be there.

[Rhino373]

It would appear the fields you are testing for aren't blank. Based on your output they appear to be filled with spaces. You need to use a Trim() function against them to clear the spaces

[Rhino373]

Rather the fields you are testing for aren't NULL

[bburton]

Ah I see where those spaces were coming from. My IIf statements were making them spaces rather than null when I was doing:

IIf(IsNull([1]),"",[1])

I replaced the "" with Null and it seemed to work now. Just that when they are all blank, it has a period. Just one period isn't a big issue really. If this was fixable, great. Thanks for the help.

[Rhino373]

You could surround the entire statement with another IIF that basically says if the LEN(statement) = 1 then NULL else (statement). This effectively replaces the single period with NULL. In no other instance would the length be 1 if at least one field had data. The minimum length would then be at least 2.

No problem!

[bburton]

Hmm, I must be doing it wrong because I keep getting number of arguments is incorrect... Last pitch here if you don't mind please? Thanks!

=IIf(Len(IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", ") & IIf(IsNull([4]),"",[4] & ", ") & IIf(IsNull([5]),"",[5] & ", "))-2) & ".")=1,Null,Left(IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", ") & IIf(IsNull([4]),"",[4] & ", ") & IIf(IsNull([5]),"",[5] & ", "),Len(IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", ") & IIf(IsNull([4]),"",[4] & ", ") & IIf(IsNull([5]),"",[5] & ", "))-2) & "."

EDIT: I got it now...

[June7]

Did you say there would be over 20 fields involved? Possible expression in ControlSource will hit a string length limit whichever way you go.

Options:
Calculate the string in a hidden box then in a visible box fix the last comma using Rhino's idea referencing the hidden control.
Still too long? Break up into 20 boxes (hidden), each with expression to determine one term and another textbox that concatenates the other 20 and fixes the comma.
Write a function that will return the desired string. Would probably involve a recordset in VBA.

[bburton]

Yep, about 24 options. Currently I have it set up where on another report, it takes the inputted fields from the form then converts them into the correct string/term that they need to be. They will then transfer over to the exporting report where it'll all be combined into the correct format with commas separating them and a period to end it.

All the fields should be named 1-24 to reduce the amount of space it will take. Hopefully it will not hit the cap.

I will also be making the calculating string hidden then output it into another box whilst fixing that last punctuation. If this all works out, it should work perfectly. I hope... Thank you all for your help.

[Rhino373]

tell me what this does:

IIf(Len(IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", ") & IIf(IsNull([4]),"",[4] & ", ") & IIf(IsNull([5]),"",[5] & ", ") & ".")=1,Null,Left((IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", ") & IIf(IsNull([4]),"",[4] & ", ") & IIf(IsNull([5]),"",[5] & ", ") & "."),Len(IIf(IsNull([1]),"",[1] & ", ") & IIf(IsNull([2]),"",[2] & ", ") & IIf(IsNull([3]),"",[3] & ", ") & IIf(IsNull([4]),"",[4] & ", ") & IIf(IsNull([5]),"",[5] & ", ") & ".")-3) & ".")