adding empty spaces to fields?

[Jerome]

Let's say I need to make a query that results in fixed length fields.
How would I tell the query to add the necessary empty spaces (if the
value in the field is less then the fixed number of characters)?

I guess it's kind of an anti-Trim: TRIM([field])

Any help is greatly appreciated!

 

[Stefan Hoffmann]

hi Jerome,

Let's say I need to make a query that results in fixed length fields.
How would I tell the query to add the necessary empty spaces (if the
value in the field is less then the fixed number of characters)?
I guess it's kind of an anti-Trim: TRIM([field])

Place it in a standard module:

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left( _
Trim(Left([Field], ALength)) & _
Space(ALength), _
ALength _
)

End Function

You can use it in any query, e.g.

SELECT FixFieldLength(Nz([yourField], "")) As FixedField
FROM [yourTable]


mfG
--> stefan <--

 

[Jerome]

Thanks Stefan!

hi Jerome,

Let's say I need to make a query that results in fixed length fields.
How would I tell the query to add the necessary empty spaces (if the
value in the field is less then the fixed number of characters)?
I guess it's kind of an anti-Trim: TRIM([field])

Place it in a standard module:

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left( _
Trim(Left([Field], ALength)) & _
Space(ALength), _
ALength _
)

End Function

You can use it in any query, e.g.

SELECT FixFieldLength(Nz([yourField], "")) As FixedField
FROM [yourTable]


mfG
--> stefan <--

 

[Jerome]

Hm, I'm trying to implement this ... What is this [Field] in the
equation? Does that correspond to the field that I want to apply this
too? But it would be better if that was a variable!

I've got 10 fields in one query that need to have a fixed number of
characters (and empty spaces if necessary to get to that fixed number).

Ideally I could use a query like this

SELECT FixFieldLength(Nz([field1], 10)) As FixedField,
FixFieldLength(Nz([field2], 5)) As FixedField2,
FixFieldLength(Nz([field3], 30)) As FixedField3 FROM myTable

Is that possible? And what's this 'Nz'?

Thanks!

Jerome

hi Jerome,

Let's say I need to make a query that results in fixed length fields.
How would I tell the query to add the necessary empty spaces (if the
value in the field is less then the fixed number of characters)?
I guess it's kind of an anti-Trim: TRIM([field])

Place it in a standard module:

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left( _
Trim(Left([Field], ALength)) & _
Space(ALength), _
ALength _
)

End Function

You can use it in any query, e.g.

SELECT FixFieldLength(Nz([yourField], "")) As FixedField
FROM [yourTable]


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Jerome,

Hm, I'm trying to implement this ... What is this [Field] in the
equation? Does that correspond to the field that I want to apply this
too? But it would be better if that was a variable!

This is sort of a typo. Replace [Field] with AString. It is the parameter.

SELECT FixFieldLength(Nz([field1], 10)) As FixedField,
FixFieldLength(Nz([field2], 5)) As FixedField2,
FixFieldLength(Nz([field3], 30)) As FixedField3 FROM myTable
Is that possible? And what's this 'Nz'?

Yes, you can do that. The Nz() for handling Null values as the function
needs a String to work.

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left( _
Trim(Left([Field], ALength)) & _
Space(ALength), _
ALength _
)

End Function

mfG
--> stefan <--

 

[John Spencer]

SELECT Left(MyTable.Field1 & Space(10),10) as Field1
, Left(MyTable.Field2 & Space(5),5) as Field2
, Left(MyTable.Field3 & Space(30), 30) as Field3
FROM MyTable

If the query grid
Field: YourFieldName: Left([YourTableName].[YourFieldName] & Space(10),10)


You can use a function placed in a VBA module to build the filled values.

'======= Code ========
Public Function LeftFill(strIn, iFieldSize as Integer) as String
LeftFill = Left(strIn & Space(iFieldSize),iFieldSize)
End Function

Public Function RightFill(strIn,iFieldSize as Integer) as String
RightFill = Right(Space(iFieldSize) & strIn, iFieldSize)
End Function
'======= End Code =====

Field: NewFieldName: LeftFill([YourTableName].[YourFieldName],10)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hm, I'm trying to implement this ... What is this [Field] in the equation?
Does that correspond to the field that I want to apply this too? But it
would be better if that was a variable!

I've got 10 fields in one query that need to have a fixed number of
characters (and empty spaces if necessary to get to that fixed number).

Ideally I could use a query like this

SELECT FixFieldLength(Nz([field1], 10)) As FixedField,
FixFieldLength(Nz([field2], 5)) As FixedField2,
FixFieldLength(Nz([field3], 30)) As FixedField3 FROM myTable

Is that possible? And what's this 'Nz'?

Thanks!

Jerome

hi Jerome,

Let's say I need to make a query that results in fixed length fields.
How would I tell the query to add the necessary empty spaces (if the
value in the field is less then the fixed number of characters)?
I guess it's kind of an anti-Trim: TRIM([field])

Place it in a standard module:

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left( _
Trim(Left([Field], ALength)) & _
Space(ALength), _
ALength _
)

End Function

You can use it in any query, e.g.

SELECT FixFieldLength(Nz([yourField], "")) As FixedField
FROM [yourTable]


mfG
--> stefan <--

 

[Jerome]

Thanks for answering but it still doesn't totally work out :/

This is my SQL:

SELECT FixFieldLength(nz([Prenom]),15) AS tPrenom FROM dbo_tParticipants;

It returns all the records but the tPrenom field is empty each time!?

Here's your code in the module:

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left(Trim(Left([AString], ALength)) & _
Space(ALength), ALength)

End Function


What could be the problem?

Thanks a lot,

Jerome

 

[Stefan Hoffmann]

hi Jerome,

Here's your code in the module:

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left(Trim(Left([AString], ALength)) & _
Space(ALength), ALength)

End Function

What could be the problem?

No

Option Explicit

at the beginning of your module. it must be

FixFieldLength = Left(...)

in the function. Another type.


mfG
--> stefan <--

 

[John Spencer]

There is a problem in the function.

Function name is FixFieldLength
You are assigning the result to FixedFieldLength.

Either change the Function name or change the assignment statement. Which I
find overly complicated. Why not just use

FixFieldLength = Left(AString & Space(Alength),ALength)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

hi Jerome,

Here's your code in the module:

Public Function FixFieldLength(AString As String, _
ALength As Long) As String

FixedFieldLength = Left(Trim(Left([AString], ALength)) & _
Space(ALength), ALength)

End Function

What could be the problem?

No

Option Explicit

at the beginning of your module. it must be

FixFieldLength = Left(...)

in the function. Another type.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi John,

Either change the Function name or change the assignment statement. Which I
find overly complicated. Why not just use

FixFieldLength = Left(AString & Space(Alength),ALength)

It is just air code. You're right, the inner Trim() and Left() are not
necessary.


mfG
--> stefan <--

 

[Jerome]

This seems to work just fine!

The other solution always left me with empty fields ...

But thanks to both of you!