Trim Function - problem

[jonosborne]

Hi,
Im sure the TRIM function works fine, otherwise there would be thousands of people worldwide kicking up a right fuss. But for some reason it is not returning the desired result.

I have a table with names, the majority are 6 characters long the rest are 5 characters. This data has been copied from a seperate program into excel and the excel file is linked to the access database. This database is then, on a daily basis, appended into an access table.

Now, i am having problems with querying this data as the table has added an extra space onto all fields with 5 characters and will therefore not find the fields with 5 characters.

I have tried using TRIM([name field]) with no avail. After this query has run the spaces are still there.! (i have checked using LEN([name field]) but the figure is still six.)

Does anyone have any idea why this might not work, i have tried using the TRIM command in excel prior to appending the data into the access table but still no luck.

Help is appreciated

[MMcCarthy]

Hi,
Im sure the TRIM function works fine, otherwise there would be thousands of people worldwide kicking up a right fuss. But for some reason it is not returning the desired result.

I have a table with names, the majority are 6 characters long the rest are 5 characters. This data has been copied from a seperate program into excel and the excel file is linked to the access database. This database is then, on a daily basis, appended into an access table.

Now, i am having problems with querying this data as the table has added an extra space onto all fields with 5 characters and will therefore not find the fields with 5 characters.

I have tried using TRIM([name field]) with no avail. After this query has run the spaces are still there.! (i have checked using LEN([name field]) but the figure is still six.)

Does anyone have any idea why this might not work, i have tried using the TRIM command in excel prior to appending the data into the access table but still no luck.

Help is appreciated

Trim should be working fine. Can you post the sql of the actual query you are using.

[JConsulting]

Hi,
Im sure the TRIM function works fine, otherwise there would be thousands of people worldwide kicking up a right fuss. But for some reason it is not returning the desired result.

I have a table with names, the majority are 6 characters long the rest are 5 characters. This data has been copied from a seperate program into excel and the excel file is linked to the access database. This database is then, on a daily basis, appended into an access table.

Now, i am having problems with querying this data as the table has added an extra space onto all fields with 5 characters and will therefore not find the fields with 5 characters.

I have tried using TRIM([name field]) with no avail. After this query has run the spaces are still there.! (i have checked using LEN([name field]) but the figure is still six.)

Does anyone have any idea why this might not work, i have tried using the TRIM command in excel prior to appending the data into the access table but still no luck.

Help is appreciated

Trim should work, however you can muscle it by using the Replace() function.

myfield:replace([name]," ","")
 

luck!
J

[jonosborne]

Hi,

Unfortunately my company uses Access '97 so the 'Replace' function is unavailable.

The SQL query is:

SELECT [Site Summary].Queue, Trim([Queue]) AS ShortQueue, Len([ShortQueue]) AS Length
FROM [Site Summary];

Which should trim each record within the field 'Queue' and then provide the length of the Trimmed record. This is not happenning for some reason.

Its very confusing why this does not work, im assuming that the problem lies with the application the records originate from.

[MMcCarthy]

Hi,

Unfortunately my company uses Access '97 so the 'Replace' function is unavailable.

The SQL query is:

SELECT [Site Summary].Queue, Trim([Queue]) AS ShortQueue, Len([ShortQueue]) AS Length
FROM [Site Summary];

Which should trim each record within the field 'Queue' and then provide the length of the Trimmed record. This is not happenning for some reason.

Its very confusing why this does not work, im assuming that the problem lies with the application the records originate from.

Are you sure it's not just the Len() function thats not giving the correct number. Have you actually tested the data itself to see if the whitespace is still present.

[missinglinq]

Anytime a common function such as Trim() fails you have to consider that there's a missing reference! Go into the code module for your form, then goto Tools - References. Check all of the selected references. If any of the selected references are marked "Missing" unselect them, and back out of the dialog box. Now go back into the dialog box and reselect the missing reference(s).

[puppydogbuddy]

Hi,

Unfortunately my company uses Access '97 so the 'Replace' function is unavailable.

The SQL query is:

SELECT [Site Summary].Queue, Trim([Queue]) AS ShortQueue, Len([ShortQueue]) AS Length
FROM [Site Summary];

Which should trim each record within the field 'Queue' and then provide the length of the Trimmed record. This is not happenning for some reason.

Its very confusing why this does not work, im assuming that the problem lies with the application the records originate from.

Here's a Replace String Function for Access 97 posted on the tip page of
www.aadconsulting.com

A neat function from Vincent Billard that you can use in Access97, which does not have the native Replace function available in Access2000. Visit Vincent's web site at http://www.rezo.net/skrol29 to download his great V97/V2000 Access add-in.

Public Function ReplaceString(Str As String, WhatStr As String, WithStr As String) As String
'Vincent BILLARD
'[e-mail]
 
Dim s As String
Dim i As Integer
 
s = Str
i = 1
 
If Len(WhatStr) > 0 Then
Do Until i > Len(s)
If Mid$(s, i, Len(WhatStr)) = WhatStr Then
s = Left$(s, i - 1) & WithStr & Mid$(s, i + Len(WhatStr))
i = i + Len(WithStr)
Else
i = i + 1
End If
Loop
End If
 
aad_ReplaceString = s
 
End Function
 

[ADezii]

Hi,

Unfortunately my company uses Access '97 so the 'Replace' function is unavailable.

The SQL query is:

SELECT [Site Summary].Queue, Trim([Queue]) AS ShortQueue, Len([ShortQueue]) AS Length
FROM [Site Summary];

Which should trim each record within the field 'Queue' and then provide the length of the Trimmed record. This is not happenning for some reason.

Its very confusing why this does not work, im assuming that the problem lies with the application the records originate from.

Are you absolutely positive that the terminating character is a Space and not something similiar to a Null-terminated String where the last character has the ANSI value of 0? To be absolutely sure that the last character in these Strings are spaces, run this simple line of code on these Strings and if the return value is 32, then it is a Space, if not, then we'll have to find out exactly what it is:

Debug.Print Asc(Right(<your string>, 1)) ==> should equal 32 if last character is a Space

[jbrumbau]

If basic string functions aren't working, the file comdlg32.ocx is missing from your C\WINDOWS\SYSTE M32\ folder. Simply copy it from a working computer and paste it into the faulty one. It has worked with pretty much everyone around here who has had VBA missing library problems.