Trailing White Spaces

[rbs]

Hi Everyone,

I have a query in which I have various fields that I need to specify are a certain number of characters each.

I need to append Trailing White Spaces to the end of each field to make the field the correct number of characters.

Further to this, I need to append Leading zeros to all of the numeric characters to bring them to the correct number of characters.

Can anyone help.

Thanks in advance.

 

[dk]

Hi,

You can do this using an update query. Create a new query in design view and change it to an Update Query (by clicking Query, Update Query). The expression you will need to enter in the 'Update To' will be something like:-

[Table1]![Field1] & Space$(50-Len([Table1]![Field1]))

Obviously you'll need to use your actual table and field name. The 50 refers to the length you want the field to be. The SQL of the query I wrote is:-

UPDATE Table1 SET Table1.Field1 = [Table1]![Field1] & Space$(50-Len([Table1]![Field1]));

<hr>

You can use an update query to append a zero as well. Use something like:-

UPDATE Table1 SET Table1.Field1 = "0" & [Table1]![Field1];

 

[CT Witter]

Or you could try something like this.

Format(12345,"0000000")
Format([Field1],"0000000")

HTH,
CT