Stuck on Padding

  • Thread starter Thread starter Tom McNally
  • Start date Start date
T

Tom McNally

I have a report which uses a crosstab query as the recordsource. The
crosstab query's value key is a compound concantenated "First" value that
contains 5 items that I need to split up into several grouped columns as
part of the crosstab. Because the grouped "values" that are part of the
concantenation string are not always the same length, and some are numeric
and some are strings, I figure the simplest method is CStr the values and
then pad them to the right, preferably with spaces.

I placed symbol characters as part of the concantenation of the value field
so that I can use Mid and InStr to un-concantenate the values and place
these in unbound textboxes on the report, but no matter what I try with the
crosstab query, including the suggested RPad and LPad functions on the MSDN
site the values in question always end up trimmed for some reason... It's
a messy-enough process to pluck out a value in a concantenated string with
nested Mid and InStr. Padding would be the easiest fix. My next alternative
would be to run multiple crosstabs and then union them. But that's tricky
with the way this report needs to be laid out and grouped.

Any ideas?

Am using AccessXP.

As always, thanks in advance

Tom
 
Tom:

Well there may be many ways to approach the query, but I won't get into
that. If you need to pad values then you've got to wrap them in the Format
function as in:

Format(CStr(MyField),"@@@@@@@"), which will give you a seven character
result as a string regardless of whether the value is alpha or numeric.
 
Did that... Doesn't "stick" in the query results -- trims the field. In
addition, have tried the following function (called within the query):

Dim x As Integer
Dim PadLength As Integer

Function Lpad(MyValue As String, MyPadCharacter As String, _
MyPaddedLength As Integer)
On Error Resume Next

Lpad = String(MyPaddedLength - Len(MyValue), MyPadCharacter) _
& MyValue
End Function

Function Rpad(MyValue As String, MyPadCharacter As String, _
MyPaddedLength As Integer)
On Error Resume Next

Rpad = MyValue & String(MyPaddedLength - Len(MyValue), _
MyPadCharacter)
End Function

This should work, provided MyValue is a string and not a number (which I
convert anyway, and also check for null values).

Tom
 
Well, this worked once I got rid of any null values with
a prior query. I discovered that format(CStr
([Field]),"@@@@@") pads to the left by default,
regardless if string or numeric. For right padding, the
function RPad below works well.

TM
 
Back
Top