string manipulation

  • Thread starter Thread starter robc
  • Start date Start date
R

robc

How do you pad spaces onto a value ? I have 3 columns that need to be
combined into 1 column, however I want the 3 columns to be essentially fixed
width within the column.

The values in the 3 columns may vary in length between 1 and 8 characters.
I want column 1 to start at position 1, column 2 to start at position 11,
and column 3 to start at position 21.

Can this be done in a query ?
 
Yes, this can be done in a query. Try:

MyNewColumn: Format([Column1], "0000000000") & Format([Column2],
"0000000000") & Format([Column3], "0000000000")
 
If your Fields are numeric, you can use to Cheryl's solution.

If you Fields are String use:

MyNewColumn: Left([Column1] & Space$(10), 10) &
Left([Column2] & Space$(10), 10) &
Left([Column3] & Space$(10), 10)
 
Check Access *VB(A)* Help which you can activate from the VB_IDE (Visual
Basic Integrated Development environment) window. Note that the VB_IDE
window is a separate window from the Access window in A2K and later
versions. You can open the VB_IDE by pressing Ctrl + G in the normal Access
window.
 
Thanks,

I have now been introduced to a Space and a String function that I did not
know existed... where may I get more info on these ? A search in Access
help yields too much info. Is there a good comprehensive list of Access
functions somewhere ?

Thanks again,

Rob



Van T. Dinh said:
If your Fields are numeric, you can use to Cheryl's solution.

If you Fields are String use:

MyNewColumn: Left([Column1] & Space$(10), 10) &
Left([Column2] & Space$(10), 10) &
Left([Column3] & Space$(10), 10)
--
HTH
Van T. Dinh
MVP (Access)



robc said:
How do you pad spaces onto a value ? I have 3 columns that need to be
combined into 1 column, however I want the 3 columns to be essentially fixed
width within the column.

The values in the 3 columns may vary in length between 1 and 8 characters.
I want column 1 to start at position 1, column 2 to start at position 11,
and column 3 to start at position 21.

Can this be done in a query ?
Click to expand...
Click to expand...
 
Just to let you know, you're not quite through with Space and String
functions yet.
Here's yet another method you could use.
NewColumn:[YourField1] & Space(10-len([YourField])) & [YourField2] &
Space(10-len([YourField2])) & etc.

If you wanted the space before the field, just reverse the [YourField]
and the Space(10 etc.)

Have fun.
--
Fred

Thanks,

I have now been introduced to a Space and a String function that I did not
know existed... where may I get more info on these ? A search in Access
help yields too much info. Is there a good comprehensive list of Access
functions somewhere ?

Thanks again,

Rob



Van T. Dinh said:
If your Fields are numeric, you can use to Cheryl's solution.

If you Fields are String use:

MyNewColumn: Left([Column1] & Space$(10), 10) &
Left([Column2] & Space$(10), 10) &
Left([Column3] & Space$(10), 10)
--
HTH
Van T. Dinh
MVP (Access)



robc said:
How do you pad spaces onto a value ? I have 3 columns that need to be
combined into 1 column, however I want the 3 columns to be essentially fixed
width within the column.

The values in the 3 columns may vary in length between 1 and 8 characters.
I want column 1 to start at position 1, column 2 to start at position 11,
and column 3 to start at position 21.

Can this be done in a query ?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top