Can I keep a records leading and trailing 'SPACES'?? Access 2003 and 2007

[cynet2k]

In an Access 2003 database, when you enter a string into a field with that string leading or ending with a space, Access automatically trims the spaces off when you leave the cell that the string you just entered is in.



For example, string:
' XYZ ' or 'XYZ ' or ' XYZ'

will get trimmed to 'XYZ' when you leave the field.

Can I stop this from happening and how?

Also, Can I stop this from happening in Access 2007 as well, if so how?

Thanks

[RuralGuy]

Maybe if you explained why you need the spaces we could help.

[cynet2k]

We have a program that manipulates strings. Say we want to find a company names:
1) AIR FORCE
2) AIRPRODUCTS

I want to take the company names that begins with 'AIR ' and manipulate them and we dont want to maniputlate strings that being with 'AIR'. Such that 1) gets manipulated and 2) doesnt.

Does that help?

I was looking for a work around so I dont have to do it using SQL statements. The client doesnt know SQL and I dont feel like creating an application to do this is all. But its looking like I might have to.

[RuralGuy]

That may not be a good example. You should have no difficulty finding "AIR " in the case you just stated.

[cynet2k]

Your right let me elaborate.

THe problem is that the table CAN ONLY contain the strings that Im using to search for and not the company names. In this example the search word in the table needs to be 'AIR ' and not 'AIR'.

Does that help?

[RuralGuy]

I have to go to a meeting for a few hours but I'll check in when I get back. Frankly, I don't see how a human could make the choice you suggest let alone a computer.

[cynet2k]

So in this example we have two tables
table1 = company names
table2 = searchwords

The program uses table2 to search for company names in table1 to manipulate. I want the user to be able to enter data in table2 and not have to worry about access trimming the tailing spaces off search word.

[RuralGuy]

As I was driving to my meeting I think I understood your issue. The code search function ^F will accept trailing spaces. You need to overide the trimming "feature" of Access when it comes to fields. I will have to give that some thought.

[RuralGuy]

Here's a response from MVP Peter Doering:

If you do nothing, trailing blanks will be trimmed. There are following
workarounds:
If a field must have a fix length, delare it as Char. This is not possible
through UI, but DDL:
CREATE TABLE Tab1 ( Field1 CHAR(10) )
Otherwise, if you want to enter trailing blanks, you can overwrite the
value within the field's AfterUpdate procedure:
Me!Field1.Value = Me!Field1.Text

I haven't tried it but if you use the AfterUpdate event of the control, somehow you need to pass the users value to that event before the control has a chance to trim it unless the .Text property still has the trailing spaces; wouldn't that be nice. Let me know if you need some more help here. It could get interesting.

[RuralGuy]

Did you try the suggested solution and did it work?