Trim Function


Hello,

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:
JOHN DOE

1234 Anywhere Ave S

City XX 12345

I want it to look like this:
JOHN DOE
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.


Sponsored Links:



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,

T..J.




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?
Thanks.




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?

Thanks,

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.

Mark




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.

thanks.




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?

Thanks,

-Brandon

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

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




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:

	Code:
	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.AddNew
    rs!CitationAuthorLastName = NewData
    rs.Update
    Response = acDataErrAdded
    rs.Close
    Set db = Nothing
Else
    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,
Mitch....




Hello,

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?

Example...

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?
Regards,

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.