+ Reply to Thread
Results 1 to 9 of 9

Invalid Use of Null - Formating listbox column

  1. #1
    Registered User
    Join Date
    10-22-2009
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2003
    Posts
    32

    Invalid Use of Null - Formating listbox column

    Hi, I am trying to populate a listbox based of ADO recordset from access database. That works fine. My problem is the last column in the listbox is a currency field but there is no formating in the listbox. So I added a piece of code in hopes that it will format to currency. This all seems to a work, but I get a run-time error 94 "Invalid Use of Null" Any suggestion how I can get around this error? The listbox populates and formats the currency column, but can't do anything because of the error. Thanks for your help.

    It debugs on this line: .List(lngIndex, 8) = (Format(Val(.List(lngIndex, 8)), "$#,##0.00"))

    When i hover the mouse over .List it shows .List(lngIndex, 8) = Null

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Invalid Use of Null - Formating listbox column

    Your recordset is returning Null where there is no number input, you can't format Null.

    So you either need to return null as zero (amend your query) or check if the value is null before trying to format it.

    You can also replace:
    Please Login or Register  to view this content.
    With
    Please Login or Register  to view this content.
    Which will speed things up

  3. #3
    Registered User
    Join Date
    10-22-2009
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Invalid Use of Null - Formating listbox column

    Thanks for you respose. I am using the function below because .GetRows doesn't work for my solution. I am new to the VBV stuff, can you show how I would return null as zero?

    Please Login or Register  to view this content.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Invalid Use of Null - Formating listbox column

    Re-read my code, I'm setting getrows to the column property, not the list property, this automatically transposes the data so you don't need the function.

    I don't know access SQL enough to tell you off the top of my head, but google it, I'm pretty sure theres a function for it.

    To change your code rather than SQL, you can just add an iff condition using the isnull() function which returns true if the list item is null.

  5. #5
    Registered User
    Join Date
    10-22-2009
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Invalid Use of Null - Formating listbox column

    Ok, I got that to work without the Function. Thanks for that. I will work on the Iff condition.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Invalid Use of Null - Formating listbox column

    it would be nz in access but I don't think you can use that in ado so it's iif(isnull(field),0,field) as Kyle said
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  7. #7
    Registered User
    Join Date
    10-22-2009
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Invalid Use of Null - Formating listbox column

    I am really lost. I don't know how to get the iff statement to work with my code.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Invalid Use of Null - Formating listbox column

    for your code you can use
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-22-2009
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2003
    Posts
    32

    Re: Invalid Use of Null - Formating listbox column

    That did it. Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1