Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    34

    Unanswered: [SOLVED] replacing NULL value in combobox with specified text

    Hi everyone

    Ive read this last week somewhere and tried everything to find it again:

    It is possible to replace a NULL value in a combobox with some specified text by using (i think) the "Input mask" property of the combobox (Access 2000). This can be handy in certain cases. I thought it was something like @;"Replacement text" but that does not work.

    Does anyone known the correct syntax to use?

    Thanks
    Last edited by irenicuz; 07-31-06 at 09:44.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    For you row source pop use the NZ function e.g.

    Code:
    SELECT NZ(MyCol, "Text if NULL") FROM MyTable
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Posts
    34
    hmm my experience when using Access functions like these in SQL is that the query runs a LOT slower. Using the combobox functionality should be a lot faster though. And i know it can be done since i checked it, i just cant remember how i did it

    But thanks for your solution.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by irenicuz
    hmm my experience when using Access functions like these in SQL is that the query runs a LOT slower. Using the combobox functionality should be a lot faster though. And i know it can be done since i checked it, i just cant remember how i did it
    depends what you do with them but broadly no - you wouldn't expect much slow down unless you have a huge recordset or are comparing the expression against something

    e.g.
    WHERE ISNULL(MyCOl) = True
    is slower than
    WHERE MyCol IS NULL

    Domain aggregate functions (DCOUNT, DMax etc) DO slow things down a lot though.

    Just noticed I might have missed what you are looking for. Do you mean replace the null in the drop down list or replace the entry in the combo box if the user has not selected a value from the list?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Posts
    34
    If the result of a query would be 3 records (for example "Value1", "Value2" and NULL) the combobox would give you 3 items to choose from:

    ------
    Value1
    Value2

    ------

    The last item is just an empty line that could be replaced with any specified text using the function i am looking for, while the combobox would still see it as null value.

    Example:

    ------
    Value1
    Value2
    <No value>
    ------

    Selecting the 3rd item would still return NULL and NOT "<left blank>", but would show "<No value>" to the user.
    It could be done by something like @,"<No value>" in the comboboxes "input mask" property.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    New to me. Sounds odd that the input mask would change the values in the drop down list.

    BTW - is your displayed column also the bound column?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Posts
    34
    No, it is only used to add items to a listbox using a button. This listbox is processed later (using VBA)

    I had never seen this function earlier either, but it was quite nice and easy.

  8. #8
    Join Date
    Feb 2004
    Posts
    34
    hehe solved

    It was the format field:

    using @;"No value" will result in the behaviour i described.

    Thanks for your hint

    btw replace ; with your local setting for list separator
    Last edited by irenicuz; 07-31-06 at 11:38.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks for letting me know. I don't tend to do much with format - a useful tip
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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