Using format to right justify a string in vba

[hardrock]

Hi Guys,

In the bit of code below how do I set QTY to be right aligned using the Format keyword? Example of the output I am trying to get with my output strings is

1600.00
***1.00
**15.00
*100.00

I've messed about with the format function but no quite there yet. Any help appreciated Thanks

code:
Set rs2 = CurrentDb.OpenRecordset("qry_test")
With rs2
Do Until .EOF

StrBody = StrBody & vbCrLf & _
"" & !Item & "" & !Desc & " " & Format(!QTY, "##0.00") & " " & !PRICE & ""

 

[Mihail]

There is a function that automated add a number of characters to a string.
But, for the life of me, I can't remember it's name.

So, until you will find out that function, you can use this one:

Public/Private Function AddChr(Character As String, FinalLenTarget As Long, strTarget As String)
     AddChr = strTarget
Dim i As Long
     For i = Len(strTarget) +1 To FinalLenTarget
         AddChr = Character & AddChr 
     Next i
End Function

Apply this for your needs:

Dim strQty As String
   strQty = AddChr("*", 6, QTY)

WARNING !
This is "in air" code.

 

[Galaxiom]

There is a function that automated add a number of characters to a string.
But, for the life of me, I can't remember it's name.

The function is String(). Its first parameter is the number of characters and the second is the character.

 

[spikepl]

http://support.microsoft.com/kb/217012

 

[missinglinq]

What am I missing, here? One would assume that QTY stands for quantity, and quantity would suggest that the field is a Number; the example data appears to confirm this. So why does anything need to be done? Numeric data is automatically right-aligned.

Linq ;0)>

 

[CJ_London]

Numeric data is automatically right-aligned

Not in the context of building a string

I would use the Space function. Decide how many characters you need for the maximum size e.g. 9999.99 is seven characters

then use

"" & !Desc & " " [COLOR=red]& space(9-len(Format(!QTY, "##0.00") ))[/COLOR] & Format(!QTY, "##0.00") & " " & !PRICE &

""

Mind you, you will also need to apply something to the end of !Desc as well so you might want

"" & !Desc & " " [COLOR=red]& space(40-len(Format(!QTY, "##0.00") -Len(!Desc))[/COLOR] & Format(!QTY, "##0.00") & " " & !PRICE & ""

 

[namliam]

Have you tried using tabs (vbTab) to allign words and stuff? I find that works best in text allignments

 

[Mihail]

@namliam
An example please? Thank you !

 

[hardrock]

CJ London your solution worked perfectly. Many thanks to you and everyone else who posted.

 

[namliam]

Something like:
!DEsc & vbTab & Quantity & vbtab & !Price

Only issue with tab is that it by default will only allign left, but if you export the data to like word or something its easy to adjust the tab allignments or even have it as a default in your document already.

 

[hardrock]

@ CJ London

I have to create this string. Can you help me create the string for this format. The string needs to contain
Part number: 10 digits length
Part description: 20 characters
QTY: 6 digits length
Price: 10 digits length

No spaces between the 4 items. The QTY and Price need to be right justified and the others left justified thanks!

 

[spikepl]

The willingness of humans so reinvent the wheel never ceases to amaze me.

 

[CJ_London]

Surprised you can't do this from the earlier post.

Not sure what you mean by

No spaces between the 4 items

but this should do it - note that if the partnumber is 10 chars in length then it will run into the description, but that is how I have interpreted no spaces between the 4 items - change the values in red to a higher number to increase the spacing change names to suit:

[Part number] & space([COLOR=red]10[/COLOR]-len([Part Number])) & [Part description] & space([COLOR=red]20[/COLOR]-len([PartDescription])) & space([COLOR=red]6[/COLOR]-len([QTY])) & [QTY] & space([COLOR=red]10[/COLOR]-len([Price]))

You haven't mentioned about formatting numbers - if required then use the format function as referred to in an earlier post.

I haven't tried this but if you are putting this in an html formatted email, you may lose the spaces, if so, change the formatting to plain text.

 

[hardrock]

@CJ London, Of course I could do it I was just wanted a helping hand. All sorted now and thank you once again.