Text Spacing Problem in Access 2003

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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
 
Thanks John!

Thanks Karl!

John Nurick said:
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.
Click to expand...
Click to expand...
 
Back
Top