Trailing Space in a text field...

[Kelanen]

Hi there,

Does anyone know of a way to force Access to allow a trailing space in a text field (or a memo field if that made the difference).

Thanks,

Jonathan Challis
IT Manager

 

[batteam]

If your field name is txtbox1 then you might try something like the following code in the appropriate place:

txtbox1_LostFocus() (or txtbox1_AfterUpdate())
txtbox1 = txtbox1 & " "

This way, no matter what your users enter into the field, after updating, it will contain the data plus a space. Doing this to a memo field would work the same.

Hope this helps.

 

[Kelanen]

It's not a text field in a form, etc, but rather a column in a table (datatype: text) - is there any way to do something similar there?

Jonathan Challis
IT Manager

 

[jksmi]

Hi,

It sounds like you want a column of the CHAR type - by default Access uses the VARCHAR type. the difference is that a CHAR field of size 5 will always use the whole 5, i.e. if you input "one" it will store "one "; where as VARCHAR of size 5 will use up to 5, i.einput "one" it will store "one". trouble is I dont think that Access will let you change this in the table design view - good news is you can use SQL to do it, i.e.

ALTER TABLE YOUR_TABLE
ALTER COLUMN YOUR_COLUMN CHAR(5)

switch YOUR_TABLE for the name of your table, switch YOUR_COLUMN for the name of the column you want to change and instead of 5 put whatever length you want the string to be. you can alter as many columns in the table as you want at once, just seperate them with columns. To write SQL directly into a query click on new, cancel the add table and change the view to SQL (icon under file in the top left hand tool bar)

HTH, Jamie
FAQ219-2884

 

[Craig0201]

Jamie,

Access doesn't support ALTER COLUMN.

Syntax

ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

Craig

 

[Kelanen]

This field is usually either empty, or contains more than 5 characters. If it has any data in it, I want to have a single trailing space at the end (a full stop and then space actually, but the "." is the easy bit).

The reason is that I am using this to power a mail merge in word, and two fields of the table are printed next to one another, effectively forming two sentences in a single paragraph.

From the mail merge side I can supress any blank records (most of them), but where there is data, I want a space before moving to the new sentence.

...and just to pre-empt - a leading space in the second field doesn't work, since this puts a space in, even if the first field is empty and thus not printed.

Jonathan Challis
IT Manager

 

[Craig0201]

Jonathon,

Do it in a query.

SELECT IIF([YourField] IS NULL, "", (YOURFIELD & " "), etc
FROM
YourTable

Craig

 

[Kelanen]

Thanks Craig - incorrect brackets, but yes, that was the obvious solution - especially since I was (as always) running of queries rather than tables direct.

Jonathan Challis
IT Manager

 

[jksmi]

Hi,

glad you got this sorted, but just so you know Craig - Access 2000 does support ALTER COLUMN...

HTH, Jamie
FAQ219-2884