Remove Blank Spaces from Text and Concatenate 1

[sacsadmin]

I have two fields Description1 and Description2 pulled from a linked table using a query in Access 2000. This query is used to generate a report.

Description1 and Description2 are 25 character long fields in the linked table. I cannot modify the properties of the linked table.

Some of the data input into Description1 does not take up all the character spaces, so Access shows them as blank spaces in the report.

I can get the two fields to concatenate using:

=([Description1] & "" & [Description2])

But the concatenated line ends up looking like this on some descriptions:

CTN THERMOELECTRIC COOLERS

I would like it to look like:

CTN THERMOELECTRIC COOLERS

Thanks for your help!

 

[MichaelRed]

TRIM, as in look it up in help.


Trim(varA) & Space(1) & trim(varB)


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[randysmid]

Hi,
You can use the Trim function to remove leading and trailing spaces. However, if you just want to remove spaces to the right of the text, you can use RTrim. On the other hand, LTrim will remove leading spaces.

=(Trim[Description1] & " " & Trim[Description2])

Did you notice that we re-introduced the space, so that the text is not concatenated totally? HTH,
Randy Smith
California Teachers Association

 

[sacsadmin]

Thanks for the quick response however I did look it up in help Mr. Red. And I wasn't sure if I had the syntex correctly so I posted the question.

I did try to use Mr. Smith's reply but I get the following:

"The expression you entered contains invalid syntax."
You may have entered an operand without an operator.

I cut and pasted Mr. Smith's code directly into the unbound text box on my form and received the error above.

Again, any help would be greatly appreciated.

 

[randysmid]

HI,
Why did you place this on the form? It belongs in the textbox on the report. HTH,
Randy Smith
California Teachers Association

 

[sacsadmin]

It has been a long day. And I am a moron!

I did put it in the report. I just typed form on the reply because I am brain dead at this point.

 

[randysmid]

And,
Did you place it in the Control Source for the textbox? Of course, the field names also have to be correct (Description1 and Description2). You will still need to have both these fields on the report, but you need to set their Visible property to No.
HTH,
Randy Smith
California Teachers Association

 

[randysmid]

Finally, remove the equal sign. I must be brain dead today too! HTH,
Randy Smith
California Teachers Association

 

[sacsadmin]

OK:

I cut and paste your line of code without the equal sign.
I checked the control source it matches.
I checked visible property; it is no.
I checked the field names; they are correct.

I get the following when I try to run the report:

Syntex error (missing operator)in query expression
'[(Trim[Description1] & " " & Trim[Description2])]'.

I can't do it master yoda!

*** I don't know if this helps but this is using an ODBC link to a pervasive SQL database to get the tables to build all of this ***

Thanks for your help.

 

[randysmid]

Hi,
You should not have the outside set of brackets. Brackets should only be used to surround or encase a field name. HTH,
Randy Smith
California Teachers Association

 

[randysmid]

Hi,
You should only have the following in the control source:
Trim[Description1] & " " & Trim[Description2] HTH,
Randy Smith
California Teachers Association

 

[sacsadmin]

No go.

Did exactly what you said. But Access is adding the brackets on the error dialog box.

I am cutting and pasting your code directly into the control source field.

I save the report, then try to run it and get the same error I mentioned before.

 

[randysmid]

Hi,
Maybe this is from left field, but after you drop in the code, did you click on something else before running the report? If not, can you? HTH,
Randy Smith
California Teachers Association

 

[sacsadmin]

After pasting the code into the control source field. I closed the properties box. Actually went to another field and changed the font. Then saved the report.

Tried to view the report and get the error.

Would this have anything to do with the fact that I am linked to the tables via ODBC. The actual data that I am trying to trim is not in an access table. (reaching)...

 

[randysmid]

Hi,
Trim is a function of VBA, and shouldn't have anything to do with ODBC.
BTW, I am now getting the same errors you are. I will experiment and see why this is happening. HTH,
Randy Smith
California Teachers Association

 

[randysmid]

Ok,
Here it goes:
=Trim([Description1]) & " " & Trim([Description2])

As you can see, it needs parentheses around each Trim statement.

WHEW! HTH,
Randy Smith
California Teachers Association

 

[sacsadmin]

FANTASTIC! Worked like a charm.

I will wait until tommorrow to hit you guys with the other problem I am having.

Time for a round beers.

Thanks again for all your help.

 

[sacsadmin]

It is funny;

Now that I read Mr. Red's original reply it makes sense!



I am not sure yet if I want to become a programmer or not?

 

[randysmid]

Hi,
What is your other problem? I have written a bunch of FAQ's specifically designed for this forum. HTH,
Randy Smith
California Teachers Association

 

[sacsadmin]

I will start a new thread for it. It has to do with an ODBC error I am getting using a between [date1] and [date2] on a ticketdate field in a query.