Text Spacing Problem in Access 2003

[Guest]

I'm having a problem with a make table query. What I am doing is creating a
table with a single text field from a combination of fields from another
table. I am then exporting this Table as a .TXT with a Fixed Width option and
using this file to process multiple transactions through a seperate automated
system as a data file. The problem that I have is that some of the fields
that I am combining into the one field are not consistent in length.

For example: A voucher number could be 6 to 8 characters in length

What happens is that this inconsistency throws the entire spacing off for
any following data in the field. Any Help? Thanks in advance.

 

[Guest]

Before you combine them do this --
Right(" " & [YourVoucherNumberField],8)
This adds spaces to the left -- OR --
Leftt([YourVoucherNumberField] & " ",8)
This adds spaces to the right.
Then concatenate them all together.

 

[John Nurick]

Hi Mike,

You can use an expression to pad the number to the required length:


'pad with leading zeros
Format([VoucherNumber],"000000") 'e.g 123456 -> "00123456"

'pad with spaces
Right(" " & Cstr([VoucherNumber]), 8) 'spaces on left
Left(CStr([VoucherNumber]) & " ", 8) 'spaces on right

 

[Guest]

Thanks John!

Thanks Karl!

Hi Mike,

You can use an expression to pad the number to the required length:


'pad with leading zeros
Format([VoucherNumber],"000000") 'e.g 123456 -> "00123456"

'pad with spaces
Right(" " & Cstr([VoucherNumber]), 8) 'spaces on left
Left(CStr([VoucherNumber]) & " ", 8) 'spaces on right

I'm having a problem with a make table query. What I am doing is creating a
table with a single text field from a combination of fields from another
table. I am then exporting this Table as a .TXT with a Fixed Width option and
using this file to process multiple transactions through a seperate automated
system as a data file. The problem that I have is that some of the fields
that I am combining into the one field are not consistent in length.

For example: A voucher number could be 6 to 8 characters in length

What happens is that this inconsistency throws the entire spacing off for
any following data in the field. Any Help? Thanks in advance.