Is it possible to delete the last comma using Trim?

[chapmajm]

Hi,
Does anybody know how I would get rid of the last comma in the following expression? Pardon me if I don't use the correct terminology but, I'm creating a report where I'm combining a bunch of fields separated by commas. But the problem is that if I don't populate the last field in the string of fields, then I get an extra comma at the end.

=Trim([Q1]+", ") & ([Q2]+", ") & ([Q8]+", ") & ([Q9]+", ") & ([Q11]+", ") & ([Q28]+", ") & [Q29]

---See... if I don't put a value in Q29 then there will be an extra comma on my report.---

Is there a simple way to tell this expression to... delete the last comma OR don't put a comma after the field if the field is blank? I can't use IIf Is Null because I don't thing you can use multiple IIf Is Nulls in one expression. Help.... PS, I tried searching the help files but I don't understand what I should even be asking.

I'VE WORKED MY PROBLEM OUT BY GIVING UP. I'M NOW GOING TO CAPITALIZE THE FIRST CHARACTER OF MY DEFAULT ANSWERS AND USE A PERIOD AT THE END OF EACH FIELD TO LOOK LIKE A SHORT STATEMENT ON MY REPORT... BECAUSE I DON'T HAVE TIME TO RESEARCH THIS ANY LONGER... BUT I'M STILL CURIOUS OF THE ANSWER FOR LEARNING PURPOSES. IF SOMEONE EVEN HAS A LINK TO A HELP FILE, THAT'D BE FABULOUS.

 

[burrcm]

IIf(IsNull([Q29]),[Q28],([Q28] & ", "))

Chris B

 

[burrcm]

You can use up to 7 IIfs nested and as many as you like consecutively.

 

[chapmajm]

Hi! Thanks for replying. I tried copying and pasting your expression and it didn't work. Did I miss something?
=IIf(IsNull([Q1]),[Q2],([Q3] & ", "))

-Jenny

 

[CraigDolphin]

=Trim([Q1] & (", " +[Q2]) & (", " + [Q8]) & (", " + [Q9]) & (", " + [Q11]) & (", " + [Q28]) & (", " + [Q29]))

 

[Banana]

Ahem, if you look at the help file, all Trim will do is remove leading or trailing spaces.

If you want to remove a character, there's two ways:

Replace(MyStr, ",", "")

Or if you may have other commas that you don't want to remove (the Replace function will remove all occurances in a string), and just need to remove the last one

Left(MyStr, (Len(MyStr)-1))

 

[chapmajm]

Thank you. I used trim to also remove fields that were blank. So I will try this code tomorrow at work.... My appreciation.
-Jenny

 

[WayneRyan]

Jenny,

If you have "intermediate" fields that can be Null, then the
task gets a little tougher.

If Null fields are producing "lists" like:

Value1,Value2,,,Value5
,Value2,Value3,,,

From a query, you need to use VBA to produce nicer lists
on forms/reports etc.:

NiceList: fnFixList([Q1] & "," & [Q2] & "," & [Q8] & "," & [Q9] & "," & [Q11] & "," & [Q28] & "," & [Q29])

Calls this Public function:

'
' Passed:  Comma-separated list with potential for
'         leading, trailing and double commas.
'
' Returns: Same list, but without "unneeded" commas
'
Public Function fnFixList(strList As String) As String
Dim strTemp As String
strTemp = strList
'
' Remove all double
'
While (InStr(strTemp, ",,")) > 0
   strTemp = Replace(strTemp, ",,", ",")
   Wend
'
' trailing ...
'
If Right(strTemp, 1) = "," Then
   strTemp = Left(strTemp, Len(strTemp) - 1)
End If
'
' leading ...
'
If Left(strTemp, 1) = "," Then
   strTemp = Mid(strTemp, 2)
End If

fnFixList = strTemp
End Function

hth,
Wayne

 

[chapmajm]

I'm still trying to fix my problem. Where do I put your code? Is it possible to use it in conditional formatting on the report level? Or do I still need to create strings in my query first?

 

[WayneRyan]

Jenny,

The code goes in a Public module. In the Modules tab of your database window. You call it from your query.

hth,
Wayne

 

[chapmajm]

Oh, I understand. I haven't used Modules yet. I always think VB first. woops. I will try this tomorrow. If I start tonight I'll keep myself up all night getting all into it. Time flies when you're stumped and using trial and error on everything you're doing in your database, right? But I'm learning loads and that's great!

Thanks!

 

[chapmajm]

BTW- I'm not getting report outcomes like Q1,,,Q7,Q8,,Q11. Can I use just the end of your code that simply removes the last comma and that's it?

 

[WayneRyan]

chapmanjm,

Yes, you could remove most of the function, but for something this easy.

You can put this in your query as a new field:

NewField: IIf(Nz(Right([YourField], 1), "") = ",", Left([YourField], Len([YourField]) - 1), [YourField])
              ===================================  =======================================  ===========
              Check the right-most character.      If right-most is a "," remove it.        Otherwise return
              Use Nz because string might be null.                                          whole string

hth,
Wayne

 

[chapmajm]

Hi Wayne,
Thank you so much for all of your help. It worked. Actually though, it wasn't working at first and I couldn't figure out why, but it was because my trailing characters were a "," and a "space", so when I include the ", " and -2 it works great! Thanks again.