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!

hi Jerome,

Jerome wrote:
> 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 <--

Thanks Stefan!

Stefan Hoffmann wrote:
> hi Jerome,
> 
> Jerome wrote:
>> 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 <--

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

Stefan Hoffmann wrote:
> hi Jerome,
> 
> Jerome wrote:
>> 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 <--

hi Jerome,

Jerome wrote:
> 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 <--

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
..

"Jerome" <subliminal.void@gmail.com> wrote in message 
news:OzTq9RzCIHA.3712@TK2MSFTNGP02.phx.gbl...
> 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
>
> Stefan Hoffmann wrote:
>> hi Jerome,
>>
>> Jerome wrote:
>>> 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 <-- 

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

hi Jerome,

Jerome wrote:
> 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 <--

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
..

"Stefan Hoffmann" <stefan.hoffmann@explido.de> wrote in message 
news:%234Uf2u0CIHA.5208@TK2MSFTNGP04.phx.gbl...
> hi Jerome,
>
> Jerome wrote:
>> 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 <-- 

hi John,

John Spencer wrote:
> 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 <--

This seems to work just fine!

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

But thanks to both of you!

John Spencer wrote:
> 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)
>