Text report with set number of spaces and positioning

[a2ari]

ok, finally working like a charm, thanks so much!
ready for a new kink?

the Amount field has to be right justified, so that there are no spaces between Amount and Circuit, regardless of how long Amount is. Amount can be anywhere from 2 to 8 bytes long, depending on the situation.
Examples

Currently:
Field.....Position.....Bytes
Amount....26............9
Circuit.....35.............20

Which result in: (A = Amount, C = Circuit)

... AAA CCCCCCCCCCCCCCCCCCCC...

I need to right align the Amount field so that the outcome is:

... AAACCCCCCCCCCCCCCCCCCCC...

or

... AAAACCCCCCCCCCCCCCCCCCCC...

etc.

Any thoughts?
Thanks!

ps. this forum doesn't seem to accept multiple spaces as valid and translates them all into just one space, so the point I'm trying to make might not come across... let me know if this doesn't make sense!

 

[shudini]

The easy way to pad it in the field would be to put in the query the following:
SELECT... Space(9-len([Amount]) & [Amount]

Basically saying first give me spaces - 9 minus the length of the Amount field, then concatenate this with the data from the amount, and you will end up with a field that is left padded with spaces.

what are the amount and circuit data types?

 

[a2ari]

amount is a number and circuit is text

btw, I got rid of the whole date table ref. Turns out I don't need to present the date in the report, so the sql is now:

strSQL = "SELECT tblFoo.Prefix, tblFoo.BU, tblFoo.Category," & _
"tblFoo.Credit, tblFoo.Amount, tblFoo.Circuit " & _
"FROM tblFoo WHERE " & _
"(((tblFoo.FooID)=[Forms].[frmFoo].[ID]))"

so where do I put the padding, in this select statement?

strSQL = "SELECT tblFoo.Prefix, tblFoo.BU, tblFoo.Category," & _
"tblFoo.Credit, Spaces(9-len(tblFoo.Amount) & tblFoo.Amount, tblFoo.Circuit " & _
"FROM tblFoo WHERE " & _
"(((tblFoo.FooID)=[Forms].[frmFoo].[ID]))"

That doesn't look right...

Is it something that will make more sense when I look at MSysIMEXSpecs and MSysIMEXColumns, which I haven't done yet...?

 

[shudini]

so where do I put the padding, in this select statement?

strSQL = "SELECT tblFoo.Prefix, tblFoo.BU, tblFoo.Category," & _
"tblFoo.Credit, Spaces(9-len(tblFoo.Amount) & tblFoo.Amount, tblFoo.Circuit " & _
"FROM tblFoo WHERE " & _
"(((tblFoo.FooID)=[Forms].[frmFoo].[ID]))"

That doesn't look right...

You almost got it right, it should say
Space(9-len(tblFoo.amount)) & tblFoo.Amount as PaddedAmount
you don't have to call it paddedAmount, you can call it whatever you want.
The function to use is Space() not Spaces().

 

[a2ari]

Like a charm!
I had to work through another issue, which is why I didn't respond yet... to make sure that I didn't have any other problems to report (and with 2 other projects to complete)... but I finally got it working and thank you very much Shuie!

 

[shudini]

It was a pleasure - thanks for letting me know it worked.