Fixing a string length to a Specific Number of Characters 1

[ErikQ]

I'm trying to fill in a field in a query where I need specific lengths for two fields, last_name and first_name. I need the length to be 20 characters in each field, and to fill the "voids" with spaces so that the formatted output is consistent. In other words, I need

cStr([last_name](plus extra spaces to = 20 characters...)

how is this line of code written to produce this output? i tried the help function, but it didn't.
any guidance is most appreciated.

 

[BoxHead]

In code:
Dim SpaceVal As String
SpaceVal = " " note 20 sapces.
LastName = LastName & Left(SpaceVal, (20 - Len(LastName)))


In a query (QBE):
[LastName] = [LastName] & Left(" ", 20 - Len([LastName]))


HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[tlbroadbent]

You can also use the SPACE function in a module or a query.

VBA module example:

LastName = Left(LastName & Space(20),20)
FirstName = Left(FirstName & Space(20),20)

Query example:

SELECT
Left([LastName] & Space(20),20) AS Last_Name,
Left([FirstName] & Space(20),20) AS First_Name
FROM Table; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[fullkeel]

Dim nametmp As String * 20

'your code here

'now each time you want to put a lastname in the field
'clear the field first
Mid(nametmp, 1) = String(20, 32)
'then put in the [LastName]
Mid(nametmp, 1) = [LastName]

'now nametmp contains [LastName] and is padded to 20 spaces