TRIM() function in query help needed

[SAK]

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

 

[The_Doc_Man]

First comment: Don't worry about the TRIM function until you need to generate a report or form. In a datasheet, maybe you shouldn't care. Trim the fields only when you want to display them in a certain way for FORMAL reports/forms. Query datasheets are a bit too informal.

Second comment: If those are REALLY independent fields, you can write an update query (or series of queries) to trim the fields in-place so you don't keep the spaces anyway. Then your subsequent SELECT queries don't need to bother. Why would you keep untrimmed data anyway?

Just a couple of random thoughts for you...

 

[SAK]

Trim Issues

Doc Man, thanks for responding.

One of the uses for the data is that it's exported out to our commercial newsletter mailer, and I found that the cleaner I send the data, the less they tend to screw things up...

Thus I want to pursue the TRIM() issue.

I set up an update query (good suggestion), and here's what I have and what I ran:

UPDATE tblAllMembers SET tblAllMembers.First_Name = Trim([First_Name]), tblAllMembers.Last_Name = Trim([Last_Name]), tblAllMembers.Postal_Code = Trim([Postal_Code])
WHERE (((tblAllMembers.Group_Name)="US Fish & Wildlife Service") AND ((tblAllMembers.SingleOrGroup)="group"));

The query seemed to run fine, and I got the message box that so many records were being updated.

Problem is, after running that, the spaces are still there.

For example Fred is still " Fred" and Fred's zip code is still "12345 ".

Any ideas where to go from here.

Thanks

 

[Pat Hartman]

Trim only removes blanks. If the leading/trailing characters are anything else, they'll still be there. Use Asc(Left(YourField, 1)) to determine the ASCII code for the first character.

 

[SAK]

Does Blank = [Space]?

Trim only removes blanks. If the leading/trailing characters are anything else, they'll still be there. Use Asc(Left(YourField, 1)) to determine the ASCII code for the first character.

Thanks Pat, I ran that and got back the value of 160, which I looked up and that is [space].

This aspect of coding is new to me, is a space = to a blank in this case?

If not, I need to come up with a way to trim the [space] from the front of some fields and the back of others.

Thanks

 

[Pat Hartman]

Space = ASCII 32. Here's a link to the table of values. ASCII table

If you see 160, then the character is not a space and that is why Trim() is not working for you.

 

[RV]

Space = ASCII 32 and ASCII 160.

However, Access Help states:

The values in the table are the Windows default. However, values in the ANSI character set above 127 are determined by the code page specific to your operating system.

and that could by the reason why TRIM isn't working for you as ASCII represents another character, as Pat pointed out.

Depnding on your Access version, you could use the Replace funktion to replace any first and any last character having an ASCII value of 160 with "".
That should do the trick.

RV2

 

[Pat Hartman]

you could use the Replace funktion to replace any first and any last character having an ASCII value of 160 with "".

"" is not the same as " ". I don't know if you can see the space in the second field but the first one indicates a null string which is different from space which is different from null.

 

[SAK]

Replace Function

Hello, as follows, I need help with the syntax to identify an ASCII character. I am attempting the replace functions using SQL, however my attempts are not working.

How is the ASC part entered so the replace function will find it properly.

Here's my SQL:

SELECT Replace([tblAllMembers.First_Name], AscII=160, "") AS Expr2, tblAllMembers.First_Name, tblAllMembers.Last_Name, tblAllMembers.Postal_Code
FROM tblAllMembers
WHERE (((tblAllMembers.Group_Name)="US Fish & Wildlife Service") AND ((tblAllMembers.SingleOrGroup)="group"));

Thanks...

 

[RV]

As you're searching for a string having ASCII code being 160, you need to use the Chr function:

Replace([tblAllMembers.First_Name], Chr(160), "")

RV

 

[SAK]

Replace Function

Thanks, I modified my SQL and that did the trick.

I'd like to take this a bit further, and if someone has the insight as to why this occurs, I'd like to know.

This particular database I run, I get/maintain updates by exporting specific portions of the data to Excel and emailing the "rosters" to the various groups to update and return to me.

Once in a while I get this type of data back, and I wonder why. Sometimes spaces. Sometimes "hard returns" within a field.

For the spaces I suspect it has something to do with the procedure the person uses to complete the Excel sheet, but I can't see someone deliberately typing in a space before/after the end of whatever string is required. My hunch is it's coming from an import or copy/paste routine.

In this particular case the Excel sheet the FirstName field had the leading "space", then the postal code had a trailing "space" for example.

Any ideas how this could be occurring? If I had a definitive answer, I could watch for this and "counsel" my cooperating list maintainers to avoid such situations.

Thanks

 

[Pat Hartman]

Copy and paste is the most likely source. Tabs and returns are not visible so they are easily copied by mistake. Caution the users to make sure that no extra leading or trailling characters are selected when they copy things.

 

[RV]

I agree with Pat that a proactive approach is the best you can do.
However, in "real life" you end up quite often with having to be reactive.

You could create a function that's called on a form After Update event that removes any "non-desired" ASCII 160 characters.

RV

 

[SAK]

Cleaning up data

Yes, I am considering some routine that would address just what you said, and not delete wanted spaces, such in street address text strings.

Thanks for the discussion, it helped me understand this situation a bit better.