Trim Function


Hope I can explain my problem so that it makes sense.

I am having a problem creating an address block in Access 2007 report. I have a 5 field address; Name1, Name2, Address1, Address2, CityStateZip. (The fields Name2 and Address2 do not always have information for each record.)

When I create a report I am getting a blank line when there isn't information for fields Name2 and Address2. I am using the Trim function and have set the properties to each of the fields to "shrink".

This is how the address block is printing:

1234 Anywhere Ave S

City XX 12345

I want it to look like this:
1234 Anywhere Ave S
City XX 12345

This works correctly on reports I have previously created in Access 2003. I have even tried coping & pasting the fields from Access 2003 into Access 2007 but still can't get it to work.

Does my problem have anything to do with "grouping"? I can't seem to find anywhere in the report module to "group" items.

Post your answer or comment

comments powered by Disqus
I am not getting the results I expect in a few of my Trim function fields, yet I am getting the correct results on a few of my other Trim function fields on THE VERY SAME REPORT WITH THE SAME FORMAT.

This makes no sense, but here is what is happening:

I am combining two text fields into one field on a report. Here is the control source =Trim([BegBates] & " " & [EndBates]).

This simple, and two of the fields give me #Error all the way through the report and the other two fields give me the correct results. Nothing different between the feilds, they are both named differently, there is NO code on the report, there are no null values, and the fields are set can grow = true so that they cannot cut off.

What is going on??????

Thank you so much for your help,


I am using a trim function on a report line that will place the address number, address prefix, address street, address type, address suffix, and address extension on one line. The problem I am having is if I do not have an address number or address prefix, the line does not left justify. The line does not go clear to the left hand side. Listed below is a sample of my code.

=Trim([ADD_NO]) & " " & Trim([add_pre]) & " " & Trim([add_st]) & " " & Trim([add_type]) & " " & Trim([add_suf]) & " " & [add_ext]

Any help would be greatly appreciated.

I've got a field that read:

xxxxxxxxxx ooooo
xxxxxxxxxx ooo
xxxxxxxxxx oooooo

and so on for each record where the length of x is constant but the length of o is varying. I need to Trim off the x leaving just the o. I know there's some code to do this but I was wondering if there was a Reverse Trim function or something like that? If I type Trim(Field, 10) then this leaves the x's, Does vba have something that will delete the x's instead of the o's?

I have 2 PC's one that has Access and VB6.0 installed on it and the other which just has Access.

I can use the trim function on the PC with VB6 installed, but on the PC with just Access when using VBA, Trim is not recognised. I have looked at the references added for both versions and everything looks the same.

Anyone help?

I imported a DB from 97 to 2000. Some of the reports were generated by the label wizard and use the Trim function.

When I run the report, Access does not recognize the Trim function, asking me to type it as if it's a parameter function, then returning #Error instead of data.

What gives?


Dan Lee

I've just placed a prototype database on a company's server and got a compile error for a line of code which uses the Trim() function. To be honest I don't want to look a fool deploying 'tested' programs which don't work.

This is such a common built-in function so why didn't Access compile it.

I wrote the code in access 2003 and deployed it on the same version. Is there a library reference missing and if so what.

While we're on the subject are there any more problems like this i should expect.


Hello, need help with the Trim() function. In a query I am using this SQL:

SELECT TRIM(tblAllMembers.First_Name), tblAllMembers.Middle_Name, TRIM(tblAllMembers.Last_Name), tblAllMembers.Address1, tblAllMembers.Address2, tblAllMembers.City, tblAllMembers.StateOrProvince, Trim(tblAllMembers.Postal_Code)
FROM tblAllMembers
WHERE (((tblAllMembers.City) Is Not Null) AND ((tblAllMembers.StateOrProvince) Is Not Null) AND ((tblAllMembers.Country)="USA"));

However when I run the query, the First_Name, Last_Name and Postal_Code doesn't trim leading and following blank spaces.

Help please

I have used the Trim function in a query and am getting an error. At first I was concatanating several fields but have cut it back to the most basic form but still receive the error. The error says that the trim expression failed. Here is the code I used:

fullname: trim([last]) + ", " + trim([first]) + " " + trim([middle])

I then used
fullname: trim([last])

I got the same error. Just using the field last does not generate an error.

Any ideas? I created the DB in Access XP and am getting the error in 2003. I tried the changes in 2003 but still got the error.


i m tranferring data in bulk form excel

after transfering the name field of the table shows spaces and i am using trim function but not getting result,

a have a lot of techniques but not able to get results

can any one highlight and solve my problem

I am trying to get a label maker to work for my contact list, and the wizard sets everything up nicely enough at first, but it uses a function called trim() that is supposed to take out spaces and make things fit better.

When I open the report however, a prompt comes up asking for a value for Trim. No matter what I put in there, it gives me an error and no data is placed in the report.

Shouldn't Trim be an automatic function that doesn't need a value?

Does anyone know how I can fix this?



PS: The contents of the label boxes are as such:

[=Trim([FirstName] & " " & [LastName])]
=" " <- (Blank Space)
=Trim([City] & ", " & [StateOrProvince])

Apparently there is a design issue which I don't understand. I have a table with an Account column (account number). It's of type TEXT and length 50.

For some reason Jet has it as a fixed-length column. How do I undo that? That is to say, even if I insert a 10-digit account number, Jet is storing a string of 50 characters (40 spaces).

I'm accessing the DB from VB.Net (actually C#.Net) but have the same problem even when I try it from Access VBA. Due to the fixed-width, even if I run this query,

UPDATE Posted SET Account = Trim(Account)

Jet ignores it - I still get a 50 char string when I run a select query from the table.

SELECT Account FROM Posted

The only way to get a 10-char string is this:

SELECT Trim(Account) FROM Posted

How did I get a fixed-width column?

I am importing data from Excel which is download from another countries system and the data comes with extra spaces and I have explored using Excels Trim Function before the data is uploaded and it is massively slow. So I am looking for some VBA (SQL statement) to run an update to the table to trim all the relevenat fields in that table, but I am a little unsure how to do this. It has been a long day here with lots of VBA coding and I am struggling.

Lets say the table name is tblTempImport and it will be all the fields with varying names.

Any pointers would be helpfull.

So I did some searches and came up with 'perhaps the leading character is not a space, in which case TRIM would not work.

I placed this in my code
mplan = LTrim(rst.Fields("Service Bureau ID"))
Asc (Left(mplan, 1)) -- this was a 48 (in the lookup table a 0)

The situation is that the mplan has the right number of digits in a field length of 4 which is correct. Then when I try to insert this field into another db in the code, which also has a field length of 4, I get a leading, what appears to be a space, and the last digit is cut off.

I've also tried TRIM in the above code and in the INSERT line.

I've defined the following function as an event procedure in the On Not in List:

	Private Sub cboLastname_NotInList(NewData As String, Response As Integer)
Dim rs As Recordset
Dim db As Database
If MsgBox(UCase(NewData) & " is not in the list of Authors." & vbNewLine & "Enter it as new Author?", vbYesNo + vbQuestion,
"Confirm.") = vbYes Then
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblCitationAuthor")
    rs!CitationAuthorLastName = NewData
    Response = acDataErrAdded
    Set db = Nothing
    Response = acDataErrContinue
End If
End Sub

The problem is that it does not trim the string before comparing the not in list. Is there a way to trim before the even procedure runs? To clarify what I mean it currently evaluates "Bob" and "Bob " as two different strings, I'd like to use the trim function before the check on not in list.

Any advice is greatly appreciated.

I imported a spreadsheet in Excel to an Access table but unfortunately brought those nasty control code characters (i.e., carriage return, line feed) in some of the column fields that look like hieroglyphics. Is there anyway to write an update query to remove those leading and trailing characters from the fields in the new Access table? I tried the Trim() function but it didn't do the trick. Any suggestions?

I am developing two database, one that is linked to anothers table. In the table that has the table that is non-linked I have a simple function in a query that parses out the phone number of an individual so that it will display the way I want it to on a report etc, this works fine, in the other data base with the linked table to the same data I have another query that has the exact same syntax, however when I run this query I get an error stating that this function is not available in expressions. I did some further testing with the Trim function and it creates the same error. I created this function from a different PC and then zipped it and ported it to another PC where I unzipped it, at the other PC it works fine, but here it is a different story. Is there a database setting that needs to be set, or possibly a table property? Any help would be greatly appreciated, wasting too much time on this one.

Hi all,
I have a local table that has data imported from a spreadsheet
and we have discovered that one of the fields brings in trailing spaces. Since the Access DB has some querys to manaipulate the data I thought I would just create a qry that would trim the value of that field but I am getting error messages when I try to run the query.
Basically it is a update query with the field value written as:-

Code: Trim(

I am leaving the update field in the qry blank and it complains about it not having a destination field which is understandable so how do I go about trimming the value of a field in a query. Is it even possible ?

Thanks in advance,


Does anyone know of a way that I can get rid of characters off a product code so all I am left with is just the characters greater than zero?


AA0000000652618... I only want to be left with 652618.

Would it be best to use a left trim or to use a right trim function? Unfortunately there is no standard for the product numbers... meaning that some numbers are 15 characters in length (as is above) but others maybe shorter or longer...

Also where I would add in the Right or Left Trim piece of code?

Peter Vav

Hi there.

I have a little problem.

I have a file that I split into little code files each with their own little Code and description. Now whoever the smart one was that is making these codes and descriptions has put double spaces between some descriptions and before and after some others.

Now in excel I don't have a problem removing them. I just use the good old Trim() function. But in access like most other programs the trim function just removes the leading and trailing spaces but leaves the double spaces inside the actual strings there.

Now my question is this. Is it possible to write a query or macro that would inspect each description and search for double spaces and either remove them if their on the end/beginning of a string or replace them with a single space if they are in the inside of a string.

Basically a search and replace function i guess is what im looking for here.

a maybe simple question:

With the mid function one can make sure that a part of a text is token to display on a report, output or whatever.

But how to do the following:
"John Isaac"
"William Bill"
"Michael Robert"

In the report the following has to be visible:

The use of mid is i think possible, and starting with the first letter as well, but how to determine when the space is coming?

Please advise!


I have a field "DisplayURL" that contains values of various lengths. Some are only 12 or 13 characters long, but others are 50 characters long. I need to run an update query to "cut-off" all the characters past the 35 limit - so that all records in the field will be 35 characters or less.

I've searched this forum and online and there are similar posts but nothing quite like this, I thought it would be a trim function but I keep reading that it only removes spaces. Any ideas?

Thank you

Hi all, this is my first post, I'll try and make it a good one :-)

Windows 2000
Office 2000

I have a small network of PC's here which have access to our central database. I have a simple form created using the label wizard which is used to print address labels for mailing lists.
On most of the PC's here it works fine but on one or two (which have no obvious difference in thier setup) I get an "ODBC--Call Failed" error and it won't run the report????
I have puzzled over this for a while when I decided that I would remove the Trim function from the lines of the address fields on the label, these are put in automatically by the label wizard. This stopped the error message and the reports now run.
I don't know why this is happening but I am hoping someone will.



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.


Okay, I have a textbox on a form. It holds a first name, and a last name... separated by a space..

What I am trying to do, And have zero idea how.. except I can use trim...

The code below is putting in first and last name in both of the fields.. I have searched the trim function.. and I guess a Ltrim and Rtrim will be used... but not sure how to accomplish this... Any assistance is appreciated.

Code: Sub Adduser() Dim oIE As InternetExplorer Dim First, Last As String Set oIE = FindIE("https://sensitivewebsitedata!!!/") On Error GoTo NotOpen: oIE.Document.all.tags("a").Item(24).Click PauseApp 1.5 oIE.Document.all.Item("inputFirstName").Value = Forms!frmmainnew!.txtaddUser1 '''This should only be putting in the first name oIE.Document.all.Item("inputLastName").Value = Forms!frmmainnew!.txtaddUser1 '''This should only be putting in the Last name NotOpen: If Err.Number = 91 Then MsgBox "L&M Admin is NOT open!" MsgBox Err.Number & Err.Source & Err.Description End If Set oIE = Nothing End Sub

Not finding an answer? Try a Google search.