Space Suffixes (Access 2003)

Topic

New Reply

When entering text into a field, I would like to intensionally leave a space at the end of some text strings, but Access removes it after leaving the cell/field. Is their a way to keep the space character?

Thanks,
Drew

Reply | Quote

Replies

Perhaps using the “After Update” would be your solution.

You can update a field with VBE code and include a space !!

‘ After_Update

Me.txtPhrase = "Ten Spaces After This          "

Reply | Quote

Please see the reply to Jezza.

Thanks,
Drew

Reply | Quote

Hi Drew

How will you determine which text strings will have a space after them, or is is all of them.

A way you could do this is have the form run from a query. We shall call this field for argument sake [PartNo]. In the same query, but not shown in the form a created field which states NewPartNo: [PartNo]&” “.

An idea but I am unclear why it is for “some” text strings

Reply | Quote

Jerry,

What I have is 3 tables (T_Alias which contains fields ‘Alias’ and ‘Mfr’ along with a few others; T_AgileData which contains a Description along with a few others; and T_Instances which contain a instance field and a few others). I currently have a script that when ran will look into the Description field and pull any alias (or aliases) into the T_Instance table (along with other information). What my problem is, is sometimes an alias is only 2 characters long (ex: TI), which when the script is run will pull a large amount of unwanted data (TILL, TIMER, TINNED, TINTED, etc when what I really want is all manufactures that are TI . If I could include a space at the string’s suffix, this would dramatically decrease the amount of unwanted records that I return.

I know that I could include another field in T_Alias that would indicate when a SPACE is to be included at the end of the string, but I hoping that I wouldn’t have to add a field just for this.

Thanks!!!
Drew

Reply | Quote

What if the alias is 3 characters long, e.g. TIN? Do you want to pick up TINNED and TINTED then?

Reply | Quote

Hans,

It would all depend how I wanted to specify the string in T_Alias.

Regards,
Drew

Reply | Quote

But there has to be some kind of system to it…
I’m afraid I do not understand what you are doing.

Reply | Quote

Hans,

My end goal is to identify all manufactures that are embedded in the description field by using a script that loops through the entire table. The script uses T_Alias to find the manufacture that resides in the description field then moves that alias to both another field in T_AgileData and another field in the table T_Instances. Since some aliases are short, I’m getting data that is unwanted and I could decrease the amount of records if I could indicate a space in the Alias field (ex: “TI “). I can do this if I would paste “TI ” into the field, but when I try to type this in, Access removes the space suffix.

Thanks!
Drew

Reply | Quote

How does your code work? Perhaps it could be modified to do what you want.

Reply | Quote

Hans,

I agree. I was thinking that I could use quotations at the beginning and the end of the text field. When the script ran, it would ignore the quotes. This is what my original thought was, but I would prefer to be able to add the space suffix to the text field in the table and be done with it. From what I get from the other responces, Access won’t do this???

Thanks,
Drew

Reply | Quote

Access will always remove trailing spaces entered manually. I repeat, it may be possible to modify the code to work the way you want without addding spaces or quotes in the table itself, but we’d need to know more about the code to be able to help with that.

Reply | Quote

Hans,

I’ve attached the code. Their is a lot going on here, so if you would like me to add the database file, I would be happy to.

Regards,
Drew

Reply | Quote

I haven’t tried to understand all the code, but as far as I can see, the line that tries to find an alias is

AliasMatch = InStr(1, rstAgileParsed!LDesc, rstAlias!MFR_Alias)

You could add a space to rstAlias!MFR_Alias, perhaps to rstAgileParsed!LDesc too:

AliasMatch = InStr(1, rstAgileParsed!LDesc & ” “, rstAlias!MFR_Alias & ” “)

That way, the alias will only be found if it is followed by a space. There is no need to include the space in the table itself.

Reply | Quote

Hans,

Thank you…again.
What about the records that I don’t want to include a space after?
For instance…For the record “TI” in the Alias table, I would like to find any string that has “TI ” (with a space, so I don’t pull out TILL and TINNED), however for the record “ON SEMI”, I want to all records that have the string “ON SEMI” (without a space suffix, this way I could pickup both “ON SEMI ” and “ON SEMICONDUCTOR” and “ON SEMICONDUCTORS”, etc.
The data we have here is very dirty, and coming up with a way to logically clean it is nearly impossible.

Regards,
Drew

Reply | Quote

You will have to find some way to determine to which values you want to add a space, and to which values you don’t…

Reply | Quote

Because Access strips spaces from the end of text fields prior to storing it what you may be able to do is to put a special character ( say an @ ) in the place of the space. Then when you run your script simply replace the @ with a space.

This is slightly simpler than an extra field depending upon your point of view.

Reply | Quote

Pat,

I thought about this and thought about using quotes, but before I did I wanted to make sure that Access didn’t have some option where it would leave the space. Doesn’t sound like it does.

Thanks,
Drew

Reply | Quote