Add Text Space

[DBFIN]

A query text field called "customer name" has a variable length. I want to convert "customer name" to a fixed length field including exactly 35 characters by adding spaces at the end of the field.

How can I do this ?

 

[Alc]

Something like this

Do While len(Your_String) < 35
    Your_String = Your_String & " "
Loop

 

[DBFIN]

I need a simple solution that uses a standard MS Access function or expression, since I won't be using any hard-coded language.

 

[WayneRyan]

DBFIN,

[customer name] & Space(35 - Len([customer name] & ""))

Wayne

 

[raskew]

Try something like this in your query, using your field and table names.

The Word and Spaces fields are just to show that it's working as advertised.

When you're satisfied with it, just eliminate those two calculated fields.

SELECT Trim([YourField]) & Space(35-Len(Trim([YourField]))) AS Expr1, Len(Trim([YourField])) AS Word, 35-[Word] AS Spaces
FROM YourTable
WHERE ((IsNull([YourField])=False))
ORDER BY Trim([YourField]) & Space(35-Len(Trim([YourField])));

HTH - Bob

 

[DBFIN]

Add Text Spaces

I tried the following expression, however some results include 34 characters and others include 35.

Trim([POLICY INSURED NAME]) & Space(35-Len([POLICY INSURED NAME]))

How can I guarantee 35 characters in all records ?

 

[MSAccessRookie]

I tried the following expression, however some results include 34 characters and others include 35.

Trim([POLICY INSURED NAME]) & Space(35-Len(Trim([POLICY INSURED NAME])))

How can I guarantee 35 characters in all records ?

I noticed that you used [POLICY INSURED NAME] as well as Trim([POLICY INSURED NAME]) in your query. In the event that [POLICY INSURED NAME] is terminated by a space, then an extra character would be added. Try modifying the query by adding the Trim command (marked in RED)

 

[DBFIN]

Add Text Spaces

I would like to thank all the respondents for your excellent recommendations. Thanks WayneRyan, raskew, MSAccessRookie, Alc !!
The problem is solved.