List box alignment

I have a list box showing 3 columns.  The third is a currency field - how do I right align 'just' this column?
Also, along the same lines - is there a way to show the total of all items in this list box (or will I have to simply display a label somewhere)?

LVL 67
sirbountyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArjiCommented:
I don't think there is a way to align an item in a listbox.  I often will create a subform that looks like a listbox just so I can align the fields.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dannywarehamCommented:
You cannot align listbox contects - though you can change other properties like colour etc...
A subform arrangement (with a continuous form) can be made to look like one though...
sirbountyAuthor Commented:
Alright - any tips on how to do so?  I think I can create the subform ok - but I'm not sure about it looking like a listbox?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ArjiCommented:
Just color the background, add textboxes to display your data, Make the textbox borders invisible, align the textboxes in a single row format.  I'm sure you'll want to tweak it a bit to get it to look the way you want.
dannywarehamCommented:
Create a form. Change to continuous (in form properties on teh FORMAT tab)
Link this to your would-be listbox source (SQL or table)
Enter your fields onto teh form as textboxes.

Run teh form (so you can see how it will look.

Now you can play with colors and textbox formats to get them flat and same as the background.

OK so far...?
dannywarehamCommented:
thenelsonCommented:
If you use a fixed width font like courier, using something like this in the third column of the row source query should do it:
Amount: Format([Number],IIf([Number]<10,"   $0.00",IIf([Number]<100,"  $00.00",IIf([Number]<1000," $000.00","$0000.00"))))
thenelsonCommented:
To be more clear:
To right align the column in the list box:

 If you use a fixed width font like courier, using something like this in the third column of the row source query should do it:
Amount: Format([Number],IIf([Number]<10,"   $0.00",IIf([Number]<100,"  $00.00",IIf([Number]<1000," $000.00","$0000.00"))))
thenelsonCommented:
Also Lebans shows how to right justify and center columns in listboxes and comboboxes:
http://www.lebans.com/justicombo.htm
sirbountyAuthor Commented:
"Make the textbox borders invisible" - transparent?
ArjiCommented:
Yeah, transparent.  Then you could use the Line drawing tool to draw vertical field dividers.  It's really a matter of your preference.
sirbountyAuthor Commented:
thenelson - I like that idea better - can you detail how to include this in my form?
ArjiCommented:
no where near as versatile
thenelsonCommented:
sirbounty,

post the row source here and I can make the change.

Now wait a minute, I looked at your profile. You got over ten times the points I have and lots of certs.  I don't need to do it for you!  In the row source for your listbox change the name of the currency field to:
Format([Number],IIf([Number]<10,"   $0.00",IIf([Number]<100,"  $00.00",IIf([Number]<1000," $000.00","$0000.00"))))
where you change "Number" above to the name of your currency field.

"sirbounty"??  Do you own a country or something?
sirbountyAuthor Commented:
No, no country... :^)

I put that in the recordsource, but got an error - presumably because it's for my third column and I'm not specifying that?

Format([Amountr],IIf([Amount]<10,"   $0.00",IIf([Amount]<100,"  $00.00",IIf([Amount]<1000," $000.00","$0000.00"))))

is what I used.
thenelsonCommented:
A listbox has a row source the form has a record source.  Why don't you post the row source and I'll take a look at it.  

A ship then?

The H.M.S. Bounty?

The name's familiar.
sirbountyAuthor Commented:
No - not the HMS Bounty either... :^)

No rowsource - using this:

Private Sub ContactID_AfterUpdate()
    Dim strSQL  As String

    strSQL = "SELECT EntryDate,Category,Amount FROM Financial "
    strSQL = strSQL & "INNER JOIN Purpose ON Financial.PurposeID=Purpose.ID "
    strSQL = strSQL & "WHERE Financial.ContactID=" & Me.ContactID

    Me.lstContributions.RowSource = strSQL
End Sub
thenelsonCommented:
Private Sub ContactID_AfterUpdate()
    Dim strSQL  As String

    strSQL = "SELECT EntryDate,Category, "
     strSQL = strSQL & "AmountFormat([Amount],IIf([Amount]<10,'   $0.00',IIf([Amount]<100,'  $00.00',IIf([Amount]<1000,' $000.00','$0000.00')))) "
    strSQL = strSQL & "FROM Financial INNER JOIN Purpose ON Financial.PurposeID=Purpose.ID "
    strSQL = strSQL & "WHERE Financial.ContactID=" & Me.ContactID

    Me.lstContributions.RowSource = strSQL
End Sub

Note I changed the double quotes in the format phrase to single quotes since you can't have double quotes in a string unless you do some fancier stuff.  Single quotes work fine here.

>>No rowsource??
How about: "Me.lstContributions.ROWSOURCE = strSQL"

thenelsonCommented:
Knighted for inventing the paper towel then?
sirbountyAuthor Commented:
The listbox tis blank sir.
thenelsonCommented:
Here you go.  This one's tested:
Dim strSQL  As String

strSQL = "SELECT Financial.EntryDate, Financial.Category, "
strSQL = strSQL & "Format([Amount], IIf([Amount] < 10, ""   $0.00"","
strSQL = strSQL & "IIf([Amount] < 100, ""  $00.00"","
strSQL = strSQL & "IIf([Amount] < 1000, "" $000.00"", ""$0000.00"")))) As Item "
strSQL = strSQL & "FROM Financial INNER JOIN Purpose ON Financial.PurposeID=Purpose.ID "
strSQL = strSQL & "WHERE Financial.ContactID=" & Me.ContactID & ";"

Me.lstContributions.RowSource = strSQL
sirbountyAuthor Commented:
: \
That still doesn't appear to be right-aligned...?
thenelsonCommented:
Here is the database I created for the listbox:
http://www.nosuffering.com/Nelson/RightAlignListbox.zip
sirbountyAuthor Commented:
I've since left there so I'll close this out - thanx for the assistance.
thenelsonCommented:
> I've since left there
I guess that's one way to solve a programming problem.  Good luck on your next "there."
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.