If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with the TRIM command
I have four fields (First_Name, MI, Last_Name, Suffix) that I am trying to
trim so that they will appear without spaces on a report. When I do a combined string (=RTrim([First_Name])+" "+[MI]+" "+Trim([Last_Name])+" "+[Suffix]) I run into problems when any one of the fields is blank. Nothing will print. As long as all four fields contain data it will print. Problem is that only a few records have data in all four fields. When I separate out the fields into four separate controls on the report, I cant get the TRIM function to work and there are spaces between the conrols. Any idea? I think I am missing somehting simple here. Thanks Rob |
#2
|
|||
|
|||
Problem with the TRIM command
Typically with Access you don't need to use Trim(). Also, the & and + have
different results. + will propogate nulls. Try: =[First_Name] & " " + [MI] & " " + [Last_Name] & " " + [Suffix] -- Duane Hookom Microsoft Access MVP "FMO902" wrote: I have four fields (First_Name, MI, Last_Name, Suffix) that I am trying to trim so that they will appear without spaces on a report. When I do a combined string (=RTrim([First_Name])+" "+[MI]+" "+Trim([Last_Name])+" "+[Suffix]) I run into problems when any one of the fields is blank. Nothing will print. As long as all four fields contain data it will print. Problem is that only a few records have data in all four fields. When I separate out the fields into four separate controls on the report, I cant get the TRIM function to work and there are spaces between the conrols. Any idea? I think I am missing somehting simple here. Thanks Rob |
#3
|
|||
|
|||
Problem with the TRIM command
Duane:
That did the trick!! Thanks. Now for my education, can you explain the difference between the '&' and the '+' operators and also can you explain why the TRIM command is not needed here? Thanks Rob "Duane Hookom" wrote: Typically with Access you don't need to use Trim(). Also, the & and + have different results. + will propogate nulls. Try: =[First_Name] & " " + [MI] & " " + [Last_Name] & " " + [Suffix] -- Duane Hookom Microsoft Access MVP "FMO902" wrote: I have four fields (First_Name, MI, Last_Name, Suffix) that I am trying to trim so that they will appear without spaces on a report. When I do a combined string (=RTrim([First_Name])+" "+[MI]+" "+Trim([Last_Name])+" "+[Suffix]) I run into problems when any one of the fields is blank. Nothing will print. As long as all four fields contain data it will print. Problem is that only a few records have data in all four fields. When I separate out the fields into four separate controls on the report, I cant get the TRIM function to work and there are spaces between the conrols. Any idea? I think I am missing somehting simple here. Thanks Rob |
#4
|
|||
|
|||
Problem with the TRIM command
Access doesn't store trailing spaces so Trim() generally is a waste of time.
The difference between + and & is that they can both be used to concatenate text but the + will propogate a Null Null + "Rob" = Null Null & "Rob" = "Rob" Null + " " & "Rob" = "Rob" Null & " " & "Rob" = " Rob" -- Duane Hookom Microsoft Access MVP "FMO902" wrote: Duane: That did the trick!! Thanks. Now for my education, can you explain the difference between the '&' and the '+' operators and also can you explain why the TRIM command is not needed here? Thanks Rob "Duane Hookom" wrote: Typically with Access you don't need to use Trim(). Also, the & and + have different results. + will propogate nulls. Try: =[First_Name] & " " + [MI] & " " + [Last_Name] & " " + [Suffix] -- Duane Hookom Microsoft Access MVP "FMO902" wrote: I have four fields (First_Name, MI, Last_Name, Suffix) that I am trying to trim so that they will appear without spaces on a report. When I do a combined string (=RTrim([First_Name])+" "+[MI]+" "+Trim([Last_Name])+" "+[Suffix]) I run into problems when any one of the fields is blank. Nothing will print. As long as all four fields contain data it will print. Problem is that only a few records have data in all four fields. When I separate out the fields into four separate controls on the report, I cant get the TRIM function to work and there are spaces between the conrols. Any idea? I think I am missing somehting simple here. Thanks Rob |
Thread Tools | |
Display Modes | |
|
|