Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    126

    Unanswered: Can I Format Columns in a Listbox?

    I'm using a listbox on a form in an Access 2003 ADP. I have some columns of several different data types that I would like to format, some are percentages, some are currency, some are dates.

    Is there any way to format these columns independently? Should I be doing it in Access/VB or should I be doing it at the SQL query level?


    On a related note, when I click on the listbox, it copies the columns to unbound text boxes below that the user can then edit. Is there anyway that I can format the data that gets copied? I just use:

    Me.txtbox0 = lstbox.column(0)

    in the onclick event of the listbox. It only copies over the number, and no matter what I put in the format property it still just displays the number.

    Thanks!
    Last edited by grrr223; 02-01-05 at 12:49.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You can't "format" the columns individually ... You can however put the data into the columns formatted ... Change everthing to text and go to town ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Formatting should be handled by the interface, so you are correct to assign the task to the Access Form.

    Your ListBox has a source property. Change the source to an SQL String that references your original source (table, view, whatever...) and formats the columns in the manner you want.

    You can copy additional columns from your listbox by referencing their ordinal number:

    Me.txtbox0 = lstbox.column(0) 'First column
    Me.txtbox0 = lstbox.column(1) 'Second column
    Me.txtbox0 = lstbox.column(2) 'Third column
    Me.txtbox0 = lstbox.column(3) 'Fourth column
    etc...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Posts
    126
    Thanks for your help. I used CAST(dollarfield AS smallmoney) to get the currency values to display properly in the listbox. That is good enough for now.

    My issue now is just getting the text boxes to be formatted. I copy the data over as blindman describes. But that just enters "50" in the text box. It doesn't format it to look like "$50.00" unless I actually typed in the text box. Is there anyway to force Access to format this field.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Umm..set the format property on the text box?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Posts
    126
    No, I am doing that. What happens is that it copys the "50" into the box but until I actually type information into the field it doesn't get formatted. I do have that property set to currency.

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    It's possible that it's interpreted as text when fetched from the listbox, try the Val function?

    Me!txtbox0.value = val(lstbox.column(0))

    Except, if there's some characters in front ($?) you might need to strip them off

    Me!txtbox0.value = val(mid(lstbox.column(0), 2))
    Roy-Vidar

  8. #8
    Join Date
    Feb 2004
    Posts
    126
    Thank you all for your help, but Roy, YOU ARE MY HERO! I was not aware of that function, it works perfectly! I guess that would explain why it wasn't being properly formatted as a number.

    Oh, I just had to use val(), even for the ones formatted as smallmoney.

    THANK YOU!

  9. #9
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Thank you for your vey kind words, but I'd rather think of it as teamwork.
    Roy-Vidar

  10. #10
    Join Date
    Feb 2004
    Posts
    126
    Actually, I've been thinking about this some more, and I think this same issue is responsible for a few other problems I've been jumping through hoops trying to solve.

    A while back I was having issues choosing a sale from a combo box and putting that selection into a text box, and I have to try it, but I'd be willing to be this is the issue.

    Thanks everyone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •