How add calculated blank to string value to fill a fixed lenght field

[giovanni]

Dear All,

I'm trying to build a query that has to add blanks to a field value content to fill all the field fixed lenght.
I.e. Fixed field lenght = 8

Field content "ABNN" result "ABNN "
Field content "AB" result "AB "
and so on.

I did the following but it doesn't work :

Espr1:
IIf(Len([STRUEROG]="1");[STRUEROG] & " ";
IIf(Len([STRUEROG]="2");[STRUEROG] & " ";
IIf(Len([STRUEROG]="3");[STRUEROG] & " ";
IIf(Len([STRUEROG]="4");[STRUEROG] & " ";
IIf(Len([STRUEROG]="5");[STRUEROG] & " ";
IIf(Len([STRUEROG]="6");[STRUEROG] & " ";
IIf(Len([STRUEROG]="7");[STRUEROG] & " ";
IIf(Len([STRUEROG]="8");[STRUEROG];" "))))))))

What's the mistake ?
Is there a more intelligent but still "easy" way to do this calculation ?

I did also the following function that is faster to insert
STRUEROG_F1: IIf([STRUEROG] Is Null;" ";Replace(Format([STRUEROG];"@@@@@@@@");" ";" "))

It works but I need to perform a left alignment of the string.

Many thanks
Giovanni

 

[namliam]

yourfield & String ( " ", 8-len(yourfield) )

Why update your table though? This should not happen...
Just store the non-fixed-length in the database and for exporting use a fixed length format, that should auto add the spaces.

If its a requirement for display on a form or something do it in a query...
Only vague reason to do it would be a requirement to edit it, in which case you have to enforce the 8 length. Which is a nightmare....

My Advice: Just use a non-fixed-length field to store your data.

 

[giovanni]

Thanks so much for suggestion !
I did what you said but MsAccess 2003 give me #Error when I run it.

Espr1: [STRUEROG] & String(" ";8-Len([STRUEROG]))

Have you got idea what it could be ?
Thanks
Giovanni

 

[Brianwarnock]

The String parameters are the wrong way

Espr1: [STRUEROG] & String(8-Len([STRUEROG]);" ")

Brian

 

[giovanni]

Ok now it works !!
Thanks again

Espr1: Str([STRUEROG]) & String(8-Len([STRUEROG]);" ")

Bye
Giovanni

 

[gemma-the-husky]

out of interest, why do you want to add the blanks. I cant see any reason to do this

this is almost certainly going to give you issues somewhere.

 

[Brianwarnock]

Namliam has asked that question.

Brian

 

[Atomic Shrimp]

You're doing this in a query, not trying to update it back into the table? Reason I ask is that I don't think Access tables can store trailing spaces in text fields - they just get discarded.

I posted a function way back in this thread that will pad or trim a string to a specified length.

Edited to add:

I see its a bit mangled in that old thread, so here it is again, reworked a bit:

Function padtrim(strInput As String, length As Integer, rightJ As Boolean, padchar As String)
strInput= Trim(strInput)
padtrim = strInput
If Len(strInput) > length Then
If rightJ = True Then
padtrim = right(strInput, length)
Else
padtrim = Left(strInput, length)
End If
End If

If Len(strInput) < length Then
If rightJ = True Then
padtrim = String(length - Len(strInput), padchar) & strInput
Else
padtrim = strInput& String(length - Len(strInput), padchar)
End If
End If
End Function

 

[namliam]

The String parameters are the wrong way

OOPS

why do you want to add the blanks. I cant see any reason to do this

Very valid question

 

[giovanni]

Why I need to do so ?

The end user would like to check data and relative formatting before to send the query result to regional office so that he/she can edit the original table or to re-do all the uploading process.
The easiest way to simulate the result is something like I'm doing.
Bye
Giovanni

 

[Atomic Shrimp]

If this is for data output, there are lots of potentially valid reasons to pad the values in the output - it might be some requirement for import into a different system, for example - I know EDI systems tend to be pretty finnicky about field lengths and so on.

If it's not just being done in the output, then yes, it sounds like a workaround and probably indicates some other underlying problem needing fixing. And as mentioned above, if it's an attempt to pad trailing spaces onto text values in an Access table, I don't think it will work at all.

 

[namliam]

The easiest way to simulate the result is something like I'm doing.

Have you checked out the fixed width option of the export ?? This does it automagicaly for you...