Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Filtering subform causes null value issues

Discussion in 'Information Technology' started by vavroom@gmail.com, Jul 28, 2006.

  1. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    I am using Split() to display part of the value of a field in a
    subform. The subform has a filter to view only those records that have
    a field checkbox=true.

    This works well as long as I am viewing only the subform

    When I view the form with the subform, if a record on the form happens
    not to have records with that true checkbox, then I get an invalid use
    of null error.

    It points to this bit of code:
    ===
    strFileName = Me.PaperConcat
    varValues = Split(strFileName, " ")
    If UBound(varValues) > 0 Then
    lngNumber = CLng(varValues(0))
    strPaper = varValues(0)
    End If
    Me.txtPaper = strPaper
    ===

    I tried including that in its own if/else statement, checking first to
    see if me.paperconcat is null, if it is, do one thing, if it's not,
    then run that split routine. But that didn't work (possibly because I
    wouldn't have written it properly?

    I'm not sure how to modify this code to handle the null value? I guess
    what I'd like it to do is that if me.paperconcat returns a null value,
    then it should be ignored and txtPaper return display nothing.
     
  2. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    How is strFileName declared? If it's a String (as the name implies), its
    value cannot be Null. If it's a variant (which it would be if you haven't
    explicitly declared it, which you should), it can be Null.

    Assuming it can be Null:

    strFileName = Me.PaperConcat
    If Not IsNull(strFileName) Then
    varValues = Split(strFileName, " ")
    If UBound(varValues) > 0 Then
    lngNumber = CLng(varValues(0))
    strPaper = varValues(0)
    End If
    End If
    Me.txtPaper = strPaper


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    <vavroom@gmail.com> wrote in message
    news:1153266033.689852.243930@s13g2000cwa.googlegroups.com...
    >I am using Split() to display part of the value of a field in a
    > subform. The subform has a filter to view only those records that have
    > a field checkbox=true.
    >
    > This works well as long as I am viewing only the subform
    >
    > When I view the form with the subform, if a record on the form happens
    > not to have records with that true checkbox, then I get an invalid use
    > of null error.
    >
    > It points to this bit of code:
    > ===
    > strFileName = Me.PaperConcat
    > varValues = Split(strFileName, " ")
    > If UBound(varValues) > 0 Then
    > lngNumber = CLng(varValues(0))
    > strPaper = varValues(0)
    > End If
    > Me.txtPaper = strPaper
    > ===
    >
    > I tried including that in its own if/else statement, checking first to
    > see if me.paperconcat is null, if it is, do one thing, if it's not,
    > then run that split routine. But that didn't work (possibly because I
    > wouldn't have written it properly?
    >
    > I'm not sure how to modify this code to handle the null value? I guess
    > what I'd like it to do is that if me.paperconcat returns a null value,
    > then it should be ignored and txtPaper return display nothing.
    >
     
  3. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    Thanks Doug,

    That did indeed do the trick. I was declaring it as a string, as the
    value in PaperConcat field is "stringey"... I didn't realise that you
    couldn't have a Null value for such. Changing to variant made the
    entire difference! Huzzah, thanks a lot :)
     
  4. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    Revisiting this... Re: Filtering subform causes null value issues

    Ok, so, it's working on a form, but I can't get it to work on a report
    :(

    I'm still having the same field that I need to split into its 4
    individual components. I obviously don't want to have them in actual
    fields (why carry extra data if you can access it differently).

    so a typical record for field ConcatPaper looks like this: "125225
    0601 E 33"

    I want to display only the first bit on the report.

    If I put code on the report's On Page, it returns one value only, even
    with several different ConcatPaper. If I put the code in the report's
    On Open, I get a "you entered an expression that has no value" and
    highlights the Concatpaper.

    I'm feeling lost on this one. Any idea?
     
  5. vavroom@gmail.com

    vavroom@gmail.com
    Expand Collapse
    Guest

    Re: Revisiting this... Re: Filtering subform causes null value issues

    > I'm feeling lost on this one. Any idea?

    Well, I've answered my own question... Doing searches on different,
    not necessarily obvious, words sometimes bring results.

    I looked up "on activate" report and went through some posts, someone
    suggested putting code in the "on format" of the report's section
    rather than "on activate" of the report itself. Tried it, works.

    <phew>

    Cheers and thanksa gain
     

Share This Page