opposite of trim?

[Guest]

I am familiar with the trim command to drop extra spaces in a data field when
joining 2 fields. But I am trying to create a table that acutually inserts
blanks or spaces so that ifwe normally see

Jacob(end)

I want to see
Jacob (end) with 5 extra spaces

Thanks.

 

[Guest]

assuming jacob is in one field and (end) is in another just text You could
use [field]&" "&"[field2]

 

[Guest]

sorry added in an extra "

should be: [field]&" "&[field2]

assuming jacob is in one field and (end) is in another just text You could
use [field]&" "&"[field2]

I am familiar with the trim command to drop extra spaces in a data field when
joining 2 fields. But I am trying to create a table that acutually inserts
blanks or spaces so that ifwe normally see

Jacob(end)

I want to see
Jacob (end) with 5 extra spaces

Thanks.

 

[Guest]

I'm not really being clear and I apologize for that.

The fields are

LastName
FirstName

and more

Not all last names are going to be 5 characters. But the field needs to be
a static 15 characters so that when I transfer it out to a text file I get a
15 character field, not a 5 character field with the next one right after.

So like this

Jacob Julie

assuming jacob is in one field and (end) is in another just text You could
use [field]&" "&"[field2]

I am familiar with the trim command to drop extra spaces in a data field when
joining 2 fields. But I am trying to create a table that acutually inserts
blanks or spaces so that ifwe normally see

Jacob(end)

I want to see
Jacob (end) with 5 extra spaces

Thanks.

 

[Guest]

The opposite of trim is mirt, which on my home planet of Tralfamadore,
translates to Space, so you can use the Space function:

= LastName & Space(15 - Len(LastName))
= FirstName & Space(15 - Len(FirstName))

I'm not really being clear and I apologize for that.

The fields are

LastName
FirstName

and more

Not all last names are going to be 5 characters. But the field needs to be
a static 15 characters so that when I transfer it out to a text file I get a
15 character field, not a 5 character field with the next one right after.

So like this

Jacob Julie

assuming jacob is in one field and (end) is in another just text You could
use [field]&" "&"[field2]

I am familiar with the trim command to drop extra spaces in a data field when
joining 2 fields. But I am trying to create a table that acutually inserts
blanks or spaces so that ifwe normally see

Jacob(end)

I want to see
Jacob (end) with 5 extra spaces

Thanks.

 

[John Vinson]

I am familiar with the trim command to drop extra spaces in a data field when
joining 2 fields. But I am trying to create a table that acutually inserts
blanks or spaces so that ifwe normally see

Jacob(end)

I want to see
Jacob (end) with 5 extra spaces

Thanks.

Access automatically removes trailing blanks when you store any data
in a Text datatype field. There are (rather ugly) getarounds but this
is the default behavior.

Why do you want this? If you're trying to export to an external text
file, you can specify the length of the field in the export
specifications; if you're putting it on a report you can set the
format; if you're concatenating two fields, you can explicitly include
spaces - what is the difference between storing

Jacob

and

Jacob

in the table, other than wasting five bytes of disk per record?

John W. Vinson[MVP]

 

[Guest]

Our inhouse written payroll system has been linked into access for me to
extrapilate data. My purpose is to create a query that will export a file
that I can submit to SSA for verification of employee name/number match.
Right now it's a one time thing, but in the future for new employees to
upload every payroll.

Since the original tables are linked I've had sucess with running thru a
query to get the info I need and sending it to a table. This is where I have
trouble. I need to then get that data into the format specified by SSA. If
I set those field sizes in the table, the data over the field size drops off,
which is great. But for names that are not as large as the field I need
additional spacing put in. SSA says that text files cannot have any
delimters comma or any other character.

So for names like my own I need to waste those 5 bytes/spaces. How can I
manage this?

 

[Guest]

my forumla looks like this

LastName: [dbo_Pr_EmpDemo_T]![chrLastName] &
Space(13-Len([dbo_Pr_EmpDemo_T]![chrLastName]))

and I am getting an error that it's too complex to be evaluated.

The opposite of trim is mirt, which on my home planet of Tralfamadore,
translates to Space, so you can use the Space function:

= LastName & Space(15 - Len(LastName))
= FirstName & Space(15 - Len(FirstName))

I'm not really being clear and I apologize for that.

The fields are

LastName
FirstName

and more

Not all last names are going to be 5 characters. But the field needs to be
a static 15 characters so that when I transfer it out to a text file I get a
15 character field, not a 5 character field with the next one right after.

So like this

Jacob Julie

assuming jacob is in one field and (end) is in another just text You could
use [field]&" "&"[field2]

:

I am familiar with the trim command to drop extra spaces in a data field when
joining 2 fields. But I am trying to create a table that acutually inserts
blanks or spaces so that ifwe normally see

Jacob(end)

I want to see
Jacob (end) with 5 extra spaces

Thanks.

 

[John Vinson]

So for names like my own I need to waste those 5 bytes/spaces. How can I
manage this?

As I suggested: add the necessary spaces in the File Export
specifications, *not* in your Table.

John W. Vinson[MVP]

 

[John Spencer]

That will error if chrLastName is more than 13 characters long or if
chrLastName is Null.

You might try the following which should work to always return 13 characters


LEFT( [dbo_Pr_EmpDemo_T]![chrLastName] & Space(13),13)

You could also write yourself a simple vba function that would take two
parameters.

Public Function PadString(StrIn as variant, HowLong as Integer) as String
'Add Spaces to end
PadString = Left(StrIn & Space(HowLong),HowLong)
End Function

You could also add spaces to the start with the following
Public Function PadStringStart(StrIn as variant, HowLong as Integer) as
String
'Add Spaces to Beginning
PadString = Right(Space(HowLong) & StrIn,HowLong)
End Function

my forumla looks like this

LastName: [dbo_Pr_EmpDemo_T]![chrLastName] &
Space(13-Len([dbo_Pr_EmpDemo_T]![chrLastName]))

and I am getting an error that it's too complex to be evaluated.

The opposite of trim is mirt, which on my home planet of Tralfamadore,
translates to Space, so you can use the Space function:

= LastName & Space(15 - Len(LastName))
= FirstName & Space(15 - Len(FirstName))

I'm not really being clear and I apologize for that.

The fields are

LastName
FirstName

and more

Not all last names are going to be 5 characters. But the field needs
to be
a static 15 characters so that when I transfer it out to a text file I
get a
15 character field, not a 5 character field with the next one right
after.

So like this

Jacob Julie


:

assuming jacob is in one field and (end) is in another just text
You could
use [field]&" "&"[field2]

:

I am familiar with the trim command to drop extra spaces in a data
field when
joining 2 fields. But I am trying to create a table that acutually
inserts
blanks or spaces so that ifwe normally see

Jacob(end)

I want to see
Jacob (end) with 5 extra spaces

Thanks.

 

[Brendan Reynolds]

I'm inclined to agree with the suggestion to add the spaces in the export
specification and not in the table. It is possible to create a fixed-width
text field in a table, though, if you do it programmatically ...

Public Sub AddFixedWidthField()

CurrentProject.Connection.Execute _
"ALTER TABLE tblTest ADD COLUMN TestText CHAR(15)"

End Sub

The key here is the use of CHAR rather than VARCHAR to specify the field
type.